Field Value Comparisons On Update Trigger 2

[Robertio]

On update we create an Audit of the field values that have changed by comparing the new value to the old one, this works fine unless either value is NULL, at which point it regards the null value as the same as the field value, is there a better way of getting round this problem than running a couple or OR statemensts?

Ideal: (But does not pick up NULLs)
IF :OLD.Field1 <> :NEW.Field1 THEN
CreateAuditRecord(oldVal,NewVal)
END IF;

Work Around: (Is there a better way of doing this?)

IF OLD.Field1 <> :NEW.Field1)
OR
(NEW.Field1 is null) and Old.Field1 is not null))
OR
(NEW.Field1 is not null) and Old.Field1 is null))
THEN
CreateAuditRecord(oldVal,NewVal)
END IF;

Thanks for any advice

 

[SpiritOfLennon]

The problem arises from the fact theat NULL is considered as an unknown value, so as far as Oracle is concerned is cannot make a decision as to whether the values are equal or not. I had the same problem the other day and resolved it in the same manner. I'm not sure if their is a clever way around it. SOL
&quot;If I'm talking c**p, I'm probably p****d&quot;

 

[rcurva]

How about using NVL here;

IF (NVLOLD.Field1,'xyz') <> NVLNEW.Field1,'uvw')
THEN
CreateAuditRecord(oldVal,NewVal)
END IF;

Where;
'xyz' -- unique value that cannot be qual to Field1 or 'uvw'
'uvw' -- unique value that cannot be qual to Field1 or 'xyz'

 

[sem]

How about to use updating() function?

 

[Robertio]

Thanks all

IF UPDATING('Field1') THEN
CreateAuditRecord(oldVal,NewVal)
END IF;

Seems to be the simplist solution, just have to go through and change the triggers on the 100 or tables now, oh well, guess I should have asked here before I started

 

[SpiritOfLennon]

I didn't realise you could use the updating() function on individual fields. Have a star from me sem! SOL
&quot;If I'm talking c**p, I'm probably p****d&quot;