Helen Borrie wrote:

> 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
>

I currently use

IF (
COALESCE(new.BLAH,<impossible_value>)<>COALESCE(old.BLAH,<impossible_value>)
) THEN

It works for me on most of the cases (except when <impossible_value> is
unpredictable)

HTH

Radu