On Monday 2002-02-11 at 09:13:48 -0000, Jason Chapman (JAC2) wrote:

> >
> > IF (OLD.mast_Column_Name IS NULL OR
> > NEW.mast_Column_Name IS NULL) THEN
> I don't understand this line

The reason for it is that NULL does not equal NULL. See below.

>
> >
> > BEGIN
> >
> > IF ((OLD.mast_Column_Name IS NULL AND
> > NEW.mast_Column_Name IS NOT NULL) OR
> > (OLD.mast_Column_Name IS NOT NULL AND
> > NEW.mast_Column_Name IS NULL)) THEN
> >
> > EXECUTE PROCEDURE Audit_The_Change(parameters, ...);
> >
> > END
> >
> > ELSE IF (OLD.mast_Column_Name <> NEW.mast_Column_Name) THEN
> >
> > EXECUTE PROCEDURE Audit_The_Change(parameters, ...);
> >
> >
> Slightly better is
>
> if (old.fld is null and new.fld is not null) or
> (old.fld is not null and new.fld is null) or
> (old.fld <> new.fld) then
> begin
> EXECUTE PROCEDURE Audit_The_Change(parameters, ...);
> end

The third condition will be true when OLD and NEW are both NULL, but I
don't want to log that case.


Another variation that I believe does what I want is the following:


IF (
(
(OLD.mast_Column_Name IS NULL AND
NEW.mast_Column_Name IS NOT NULL) OR
(OLD.mast_Column_Name IS NOT NULL AND
NEW.mast_Column_Name IS NULL)
)

OR

(
(OLD.mast_Column_Name <> NEW.mast_Column_Name) AND
(OLD.mast_Column_Name IS NOT NULL) AND
(NEW.mast_Column_Name IS NOT NULL)
)

) THEN

EXECUTE PROCEDURE Audit_Trail(args, ...);


I've also thought of the following, but I don't at present want to go
hunting for and testing foreign UDFs:


IF (NVL(OLD.mast_Column_Name, 1) <> NVL(NEW.mast_Column_Name, 1)) THEN
EXECUTE PROCEDURE Audit_Trail(args, ...);


As you see, it requires an external function that doesn't come with
Firebird or InterBase out of the box, so to speak. There is such a
beast available somewhere, but at the cost of additional maintenance
worries. I wonder if there's any chance of having some "NULL"
functions like NVL in the standard ib_udf?


By the way, as I mentioned in another post, I've put this code in an
"After Update" trigger, not a "Before Update" as I first indicated.


Cheers,

David Trudgett