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.