Oracle Database Discussions

I have three triggers set up - on insert/modify/delete - to record row changes to a history table. Because an external application periodically sends dumps to our system containing many unchanged records I need to check that row updates actually change something before writing history records.

I test for changes using code like

IF (
( :new.ACTIVE <> :old.ACTIVE )
OR ( :new.TYPE <> :old.TYPE )
OR ( :new.SURNAME <> :old.SURNAME )
[etc] (
THEN
[write history record]

The problem is that where a field is converted to or from null value the comparison fails to detect a change. This appears to be because the test 'some value' <> NULL produces an UNKNOWN result and the THEN section doesn't occur.

I can rewrite the trigger with a large number of IS NULL tests to get around the problem but this will result in some nasty looking code.

Is there an elegant way to rewriting this trigger to handle changes including changes to and from null values?

Thanks

JimB

The full trigger:

create or replace TRIGGER EPROVIDER_UPDATE
AFTER UPDATE ON EPROVIDER
FOR EACH ROW
BEGIN
IF (
( :new.ACTIVE <> :old.ACTIVE )
OR ( :new.TYPE <> :old.TYPE )
OR ( :new.SURNAME <> :old.SURNAME )
OR ( :new.FIRSTNAME <> :old.FIRSTNAME )
OR ( :new.ADDRESS1 <> :old.ADDRESS1 )
OR ( :new.ADDRESS2 <> :old.ADDRESS2 )
OR ( :new.SUBURB_TOWN <> :old.SUBURB_TOWN )
OR ( :new.STATE <> :old.STATE )
OR ( :new.POSTCODE <> :old.POSTCODE )
OR ( :new.TELEPHONE <> :old.TELEPHONE )
OR ( :new.PREFERRED_MODE <> :old.PREFERRED_MODE )
OR ( :new.PRACTICE_FAX <> :old.PRACTICE_FAX )
OR ( :new.PRACTICE_EMAIL <> :old.PRACTICE_EMAIL )
OR ( :new.LOCATION_CERTIFICATE <> :old.LOCATION_CERTIFICATE )
OR ( :new.INDIVIDUAL_CERTFICATE <> :old.INDIVIDUAL_CERTFICATE )
OR ( :new.HMS_MAILBOX <> :old.HMS_MAILBOX )
)
THEN
INSERT INTO EPROVIDER_HISTORY
(GP_ID, DIV_ID, ACTIVE, TYPE, SURNAME,
FIRSTNAME, ADDRESS1, ADDRESS2, SUBURB_TOWN, STATE,
POSTCODE, TELEPHONE, PREFERRED_MODE, PRACTICE_FAX, PRACTICE_EMAIL,
LOCATION_CERTIFICATE, INDIVIDUAL_CERTFICATE, HMS_MAILBOX, AUDIT_DATE, CHANGE_TYPE,
OSUSER )
VALUES
(:new.GP_ID, :new.DIV_ID, :new.ACTIVE, :new.TYPE, :new.SURNAME,
:new.FIRSTNAME, :new.ADDRESS1, :new.ADDRESS2, :new.SUBURB_TOWN, :new.STATE,
:new.POSTCODE, :new.TELEPHONE, :new.PREFERRED_MODE, :new.PRACTICE_FAX, :new.PRACTICE_EMAIL,
:new.LOCATION_CERTIFICATE, :new.INDIVIDUAL_CERTFICATE, :new.HMS_MAILBOX, systimestamp, 'update',
ora_login_user);
END IF;
END;