Creating Oracle Update Trigger

[davidrm]

Hello All,
I am completely new to triggers and could use some assistance. I've created a table called Audit_Carrier. I'd like for this table to have a row inserted whenever there is a change of carrier_id on the OCN table. The row should consist of ocn_id, customer_id, and old carrier id. I've tried this several times with no positive outcome. Any help on this would be greatly appreciated.

CREATE TABLE AUDIT_CARRIER (OCN_ID CHAR(20), CUSTOMER_ID CHAR(20), OLD_CARRIER_ID CHAR(10) );

CREATE OR REPLACE TRIGGER CHECK_CARRIER
BEFORE UPDATE ON OCN
FOR EACH ROW
WHEN (NEW.CARRIER_ID <> OLD.CARRIER_ID)
BEGIN
INSERT INTO AUDIT_CARRIER (OCN_ID, CUSTOMER_ID, OLD_CARRIER_ID)
VALUES OLD.ID, :OLD.CUSTOMER_ID, :OLD.CARRIER_ID);
END;

 

[sem]

What was your &quot;negative&quot; outcome?

Regards, Dima

 

[davidrm]

It didn't capture any events, even though there were situations where the carrier id had changed.

 

[sem]

I see 2 possible reasons:

1. initial (old) value for CARRIER_ID was NULL, thus the condition NEW.CARRIER_ID <> OLD.CARRIER_ID was NOT evaluated as TRUE
2. the trigger was DISABLEd

Regards, Dima