OK thank you.

I changed my trigger in the following way:

AS
begin
if ((new.rbcard <> old.rbcard)
or (new.anrede <> old.anrede)
or (new.v_name <> old.v_name)
or (new.n_name <> old.n_name)
or (new.telefon <> old.telefon)
or (new.handy <> old.handy)
or (new.email <> old.email)
or (new.comment <> old.comment)
or (new.bonus <> old.bonus)) then
new.last_changed = current_timestamp;
end

but the trigger workes only on some datasets. Sometimes the trigger does not change the last_changed field

Whats wrong now ?

----- Original Message -----
From: Martijn Tonies
To: firebird-support@yahoogroups.com
Sent: Tuesday, December 12, 2006 8:49 PM
Subject: Re: [firebird-support] Problem with my Trigger


Hi,

> I've created the follwoing Trigger:
>
> CREATE TRIGGER USER_BU0 FOR "USER" ACTIVE BEFORE UPDATE POSITION 0 AS
> begin
> if ((new.rbcard is not NULL)
> or (new.anrede is not NULL)
> or (new.v_name is not NULL)
> or (new.n_name is not NULL)
> or (new.telefon is not NULL)
> or (new.handy is not NULL)
> or (new.email is not NULL)
> or (new.comment is not NULL)
> or (new.bonus is not NULL)) then
> new.last_changed = current_timestamp;
> end
>
> What I want is:
> The fiield last_changed has to be set to the timnestamp of the last
editing
> of the dataset, but only if important fields are manipulated. The table
> "USER" also hat the field last_seen. If thios filed is set in a Query the
> Trigger has to to nothing.
>
> E.g.
> The Query UPDATE "USER" SET telefon = '123456789789' shold activate the
> trigger but
> UPDATE "USER" SET last_seen = current_timestamp should do nothing.
>
> But my trigger always updates the last_changed field.
>
> What am I doing wrong ?

That's because you're not checking for changes, but are checking to see
if any of the NEW.something is not null. Even when you don't modify
the columns, the NEW.columns will have the old values, hence, "not null"
and your trigger will update LAST_CHANGED.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com





[Non-text portions of this message have been removed]