At 12:18 PM 15/02/2006, you wrote:

>
>
>I am currently having trouble developing a before update trigger to set a
>timestamp field in a record based
>
>on values in other fields.
>
>The question I need answered is
>
>What is stored in new.Field if no change has been made to Field?
>
>Is it null or is it the same as Old.Field?

The two variables will be same if nothing changed, different if something did.

>My logic seems to break down when I assume that new.Field=old.Field
>indicates that no change has been made to the
>Field, and the possibility of Null in either new.Field or Old.Field seems to
>complicate it more.
>I am using the current release 1.5.3 of firebird

Yes, because (something = null) is not a valid comparison.

By the way, NEW.* and OLD.* are not "commands". They are context
variables, i.e. containers for values which can be read. In the case
of the NEW.* variables, you can assign to them, too, under the right
conditions.

You don't say what you're doing with them but, if you are testing a
variable and there's a chance it is null, then you explicitly have to
test for null using

IF (
(NEW.BLAH IS NULL AND OLD.BLAH IS NOT NULL)
OR
(OLD.BLAH IS NULL AND NEW.BLAH IS NOT NULL)
) THEN

So, assuming you want to make some decision according to whether the
NEW and the OLD are the same, and the column is nullable, you'll have
to do three tests:

IF (
(NEW.BLAH IS NULL AND OLD.BLAH IS NOT NULL)
OR
(OLD.BLAH IS NULL AND NEW.BLAH IS NOT NULL)
OR
(NEW.BLAH <> OLD.BLAH)
) THEN

./hb