Sam,
Wd you please trim your posts? As your threads progress they get become
more and more like my son's bedroom and I get the same sense of utter despair.

At 07:05 PM 29/06/2005 +0000, you wrote:

>XP Pro, FB1.5.2 4731
>Using the examples in Helen's book, I successfully created a generator
>and trigger to create a unique "ID" on the BEFORE INSERT event for a
>table. Now I need one a bit more complex.
>This trigger should update the DISPLAY_NAME field in the CONSTITUENTS
>table after update IF (for constituent type 1) the LAST_NAME,
>FIRST_NAME, or MI FIELD has been changed by the user, or (for
>constituent type 3) the BUSINESS_NAME has been chgd by the user. This
>is what I have so far. Could some one fix this for me?
>
>The following script returns this error msg when I try to run it.
>===
>ISC ERROR CODE:335544569
>
>ISC ERROR MESSAGE:
>Dynamic SQL Error
>expression evaluation not supported
>===

1) As others pointed out, you need to use the '||' double-pipe SQL
concatenation operator, not the VB '+' symbol.

>SET TERM !! ;
>CREATE TRIGGER UPDATE_DISPLAYNAME FOR CONSTITUENTS
> AFTER UPDATE AS
> BEGIN
> IF (OLD.CONSTITUENT_TYPE_CODE=1) THEN
> BEGIN
> IF ((OLD.LAST_NAME <> NEW.LAST_NAME)
> OR (OLD.FIRST_NAME <> NEW.FIRST_NAME)
> OR (OLD.MI <> NEW.MI)) THEN
> NEW.DISPLAY_NAME=UPPER(NEW.LAST_NAME)+', '+NEW.FIRST_NAME+'
>'+NEW.MI;
> END
> ELSE
> IF (OLD.CONSTITUENT_TYPE_CODE=3) THEN
> BEGIN
> IF (OLD.BUSINESS_NAME <> NEW.BUSINESS_NAME) THEN
> NEW.DISPLAY_NAME=UPPER(NEW.BUSINESS_NAME);
> END
> END !!
>SET TERM ; !!

2) The other important thing you have to re-read in The Book is that After
triggers don't update anything in the table that owns them. If you want to
set or change the NEW values, it has to happen in a Before trigger.

3) Also make sure that your tests include testing for NULL in both OLD and
NEW values.

That should solve most of your current problems.

./heLen