Subject | Re: [firebird-support] null in triggers |
---|---|
Author | Paul Vinkenoog |
Post date | 2003-09-16T22:58:05Z |
Hi Alexandre,
if not ( New.Quantity = Old.Quantity
or
New.Quantity is null and Old.Quantity is null )
But:
Greetings,
Paul Vinkenoog
PS:
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.
> if (New.Quantity <> Old.Quantity) orYou can make this shorter:
> ((New.Quantity is null) and (Old.Quantity is not null)) or
> ((New.Quantity is not null) and (Old.Quantity is null))
if not ( New.Quantity = Old.Quantity
or
New.Quantity is null and Old.Quantity is null )
But:
> I think a built in function "updated" will be handy in situationsAbsolutely!
> like that,
Greetings,
Paul Vinkenoog
PS:
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.