Catering for NULLS in comparitive statements
Amazing how your SQL gets rusty, but here goes.
Have a test that evaluates whether a value has changed in a trigger. Basically, if it has changed I do something. It can be NULL so I have to test if its been changed from NULL to a value and vice versa, as well as checking if values existed before and after the change, but are different.
The old and new referred to below are the before and after values of the same column in a DB trigger.
I have two methods:
(1) IF
NVL(old,'XXXX') <> NVL(new,'XXXX') THEN ...
or
(2) IF
old <> new OR
(old IS NULL AND new IS NOT NULL) OR
(old IS NOT NULL AND new IS NULL) THEN ...
(1) would work but I don't like coding the NVL alternative. I mean what if the value really was 'XXXX'! (Highly unlikely)
(2) works but seems cumbersome.
Is there a better, recommended method?