At 02:58 AM 21-12-02 +0200, you wrote:

>Hi!
>
>If i have an after(or before, does not matter) update trigger with the
>following in it:
>if new.field <> old.field then [do something that takes a long time and
>needs to be done only when this field changes]
>
>..it evaluates to false when old value is null.

Null is not a value, it is a state: it means the value of the data is
unknown. This is not a bug - it's the way null is meant to work.

>This would solve it:
>if ((new.field <> old.field) or ((new.field is not null) and (old.field
>is null))) then ...
>
>..but it's very inconvenient when i have more than one field in the
>condition. I solved my problem (a while ago) in the client programm (had
>4 fields). So this is not an active problem, but i'm just being curious.
>
>This seems absurd. Is this a feature or a bug or does some standard say
>that this is the way it should be?

Yes, it is dictated by both the standard and Boolean logic. If you would
prefer to simplify *your* logic, so that new.field <> old.field always
gives true when not (new.field = old.field) then make the columns in
question non-nullable and enforce a default value via a Before Insert trigger.

"Inconvenient" is strange. After all, you only write the trigger once.

When you find uses for null, you'll appreciate it as a feature.

>Again - is there another way to do this?

Testing for negative conditions can span a broader scope than positive
logic. Perhaps you could explore testing (new.field = old.field) and
arrive at something less cumbersome, e.g.

declare variable do_nothing char;
begin
do_nothing = 'F';
if (new.field = old.field) then
do_nothing = 'T';
if do_nothing = 'F' then
begin
[[do stuff]]
end
end

Just remember that if new.field and old.field are both null, you won't get
True on an equivalence comparison either...

I think you'll learn to know and love null, all the same. <g>

heLen