Oracle triggers not firing

Question

I have one problem with my trigger; they are getting fire on DDL statement on the tables applied.

I could see the data being inserted and updated in the tables which I have applied but no data in the log tables.

For example: ADDRESS is one sample table and ADDRESS_LOG is the log table where in which trigger puts the data from ADDRESS table in any DML statements happen.

Any help

CREATE OR REPLACE TRIGGER TR_ADDRESSES
 BEFORE UPDATE ON ADDRESSES 
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW
BEGIN
IF UPDATING THEN
IF
UTILS.IS_EQUAL (:OLD.ADDRESSES_ID,:NEW.ADDRESSES_ID)  OR
UTILS.IS_EQUAL(:OLD.ADDR_1,:NEW.ADDR_1)  OR
UTILS.IS_EQUAL(:OLD.ADDR_2,:NEW.ADDR_2)  OR
UTILS.IS_EQUAL(:OLD.CITY,:NEW.CITY)  OR
UTILS.IS_EQUAL(:OLD.COUNTY,:NEW.COUNTY)  OR
UTILS.IS_EQUAL(:OLD.STATE,:NEW.STATE)  OR
UTILS.IS_EQUAL(:OLD.ZIP,:NEW.ZIP)  OR
UTILS.IS_EQUAL(:OLD.COUNTRY,:NEW.COUNTRY)  OR
UTILS.IS_EQUAL(:OLD.PO_BOX,:NEW.PO_BOX)
THEN
INSERT INTO CMS_DATA.ADDRESSES_LOG
VALUES (
:OLD.ADDRESSES_ID,
:OLD.ADDR_1,
:OLD.ADDR_2,
:OLD.CITY,
:OLD.COUNTY,
:OLD.STATE,
:OLD.ZIP,
:OLD.COUNTRY,
:OLD.PO_BOX
);
END IF;
END IF;
END TR_ADDRESSES;

you don't provide what "UTILS_IS_EQUAL" is doing .. however, your "logic" looks off: you stated with that IF: "If any column is the same old = new", then insert into log. I'm assuming you want to add the log if any columns is changed .. ?? — Ditto, Aug 19 '16 at 18:17
Seeing as how the sample code is a `BEFORE UPDATE` trigger, I'm fairly certain it was meant to be DML. — Chris Hep, Aug 19 '16 at 18:41
Yes,I got it working actually there was permission issue and administrator has grant me access to run. — Avinash.Byalihal, Aug 19 '16 at 18:57

Oracle triggers not firing

0 Answers0