Subject Re: [firebird-support] Problem with my Trigger
Author Matze
Thank you all !!

You helped me a lot !

Matthias Feist

----- Original Message -----
From: Helen Borrie
Sent: Tuesday, December 12, 2006 10:19 PM
Subject: RE: [firebird-support] Problem with my Trigger

At 07:45 AM 13/12/2006, Sean Leyne wrote:

>The correct logic should be (FB 1.5 or later)...
> if ((new.rbcard IS DISTINCT FROM old.rbcard)
> or (new.anrede IS DISTINCT FROM old.anrede)
> or (new.v_name IS DISTINCT FROM old.v_name)
> or (new.n_name IS DISTINCT FROM old.n_name)
> or (new.telefon IS DISTINCT FROM old.telefon)
> or (new.handy IS DISTINCT FROM old.handy)
> or (new.comment IS DISTINCT FROM old.comment)
> or (new.bonus IS DISTINCT FROM old.bonus)) then
> new.last_changed = current_timestamp;

Not available in v.1.5.x - it's new in v.2.0. From the v.2.0 release notes:

A new equivalence predicate behaves exactly like the
equality/inequality predicates, but tests whether one value is
distinct from the other. Thus, it treats (NULL = NULL) as TRUE. It is
available in both DSQL and PSQL.

Syntax Pattern
<value> IS [NOT] DISTINCT FROM <value>

For v.1.5, each of the tests should check for null both ways on
nullable columns:

if (
(new.rbcard <> old.rbcard) -- tests for 2 non-null values only
or (new.rbcard is null and old.rbcard is not null)
or (old.rbcard is null and new.rbcard is not null) )

Obviously you don't need the null checks on non-nullable columns.


[Non-text portions of this message have been removed]