At 02:44 AM 25/10/2006, you wrote:

>FB 1.5.3
>
>I must be making some obvious mistake here, but I can't see what.
>
>I have the trigger below to add a username and timestamp to a blob
>field.
>
>
>SET TERM ^^ ;
>CREATE TRIGGER CONTACTS_TIMESTAMP_NOTE FOR CONTACTS ACTIVE BEFORE
>INSERT OR UPDATE POSITION 9 AS
>/*
>time stamp a Note field that is new or edited
>*/
>declare Str VarChar(8192);
>
>begin
> if ((INSERTING and NEW.NOTE is not null) or (UPDATING and OLD.NOTE
><> NEW.NOTE)) then
> begin
> Str = SubString(New.Note from 1 for 8190);
> Str = Str ||' (***' || CURRENT_USER || ' ' || CURRENT_TIMESTAMP
>|| ' ***)';
> //exception E_Exception Str;
> NEW.NOTE = Str;
>
> end
>end
>^^
>SET TERM ; ^^
>
>When I post new text to the BLOB the user and timestamp info is not
>there. When I uncomment the exception the Str variable holds the
>correct BLOB string + Username and timestamp so I know the trigger
>has been triggered properly.
>
>What am I missing?

A blob - even a text blob - isn't a string. You can't edit a blob at
all: "editing" a blob always means "replacing an existing blob with
a new blob".

Your null logic for the UPDATING test is going to come to grief if
you have OLD.NOTE null and NEW.NOTE not null or vice versa.

When dealing with your string variable, the whole thing is going to
fall apart if your substring returns the whole 8190 bytes to the
string variable, since appending the username and timestamp will add
~ 60 bytes and overflow the variable. Doing what you want to do will
also add other problems like truncating the blob and/or reaching a
point where an ever-increasing number of user/timestamp appendages
will eventually cause shorter strings to overflow.

If you still want to persevere with this idea, you could try a UDF
such as Str2Blob in fbudf (making sure that you have defined the
function to your database to take an input string of adequate size)
and declare a local TEXT blob variable into which to feed the amended
string, finally assigning this blob to NEW.NOTE. There are serious
gotchas with this if you allow the application to update the record
more than once in the same transaction (or allow both insert and
update of the record without enforcing a commit between the ops).

OTOH, plan a happy life and consider adding a timestamp and a varchar
column to the table for last update date and updater, respectively,
and don't try to tinker with NOTE at all. Users will be a lot
happier to know that their creation is not going to be blown away by
some invisible malevolent hand....

./heLen