Subject Re: [firebird-support] null in triggers
Author Paul Vinkenoog
Hi Alexandre,

> if (New.Quantity <> Old.Quantity) or
> ((New.Quantity is null) and (Old.Quantity is not null)) or
> ((New.Quantity is not null) and (Old.Quantity is null))

You can make this shorter:

if not ( New.Quantity = Old.Quantity
New.Quantity is null and Old.Quantity is null )


> I think a built in function "updated" will be handy in situations
> like that,


Paul Vinkenoog

Shortest general field equality test (with NULL considered equal to
NULL) that I've been able to come up with is:

A = B or A is null and B is null

or with parens:

( A = B ) or ( A is null and B is null )

The shortest inequality test is just the negation of this:

not ( A = B or A is null and B is null )

Now if you know the laws of De Morgan, you might be tempted to
reformulate the inequality test like this:

( A <> B ) and ( A is not null or B is not null )

Don't do that! SQL's null handling BREAKS boolean logic. If A is null
and B isn't, the above test returns false. The test starting with
"not" does exactly what you want.