CODEWITHSUNDEEP
sqloracle-databaseoracle11g
sNOWsYSTEM
sNOWsYSTEM

Reputation: 59

Oracle update trigger issue

Question is I have the following trigger to watch my specific column "CHANGED" if its updated to a 1 I would like it to update the same row and the column "CHANGED_DATE" to the current SYSDATE. For some reason I keep getting this error any help would be greatly appreciated.

Error

UPDATE "SYSADM"."SHIP_CALENDAR" SET CHANGED = '1' WHERE ROWID = 'AABCxCAAEAAAKYEAAB' AND ORA_ROWSCN = '6165377066'
ORA-04091: table SYSADM.SHIP_CALENDAR is mutating, trigger/function may not see it
ORA-06512: at "SYSADM.SHIP_CALENDAR_CHANGED", line 5
ORA-04088: error during execution of trigger 'SYSADM.SHIP_CALENDAR_CHANGED'

Trigger

create or replace TRIGGER SHIP_CALENDAR_CHANGED
AFTER UPDATE
   OF CHANGED
   ON SHIP_CALENDAR
   REFERENCING OLD AS o NEW AS n
   FOR EACH ROW
DECLARE  
  BEGIN
     -- UPDATE SHIP CALENDAR SET UPDATE_CHANGE TO SYSDATE IF CHANGED CHANGES
     IF :n.CHANGED = '1' then  
       UPDATE SHIP_CALENDAR
         SET CHANGED_DATE = SYSDATE
         WHERE SHIPMENT_ID = :o.SHIPMENT_ID;
     END IF;  
END;

Table

ShipCalCols

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use a before update trigger:

create or replace TRIGGER SHIP_CALENDAR_CHANGED
BEFORE UPDATE
   OF CHANGED
   ON SHIP_CALENDAR
   REFERENCING OLD AS o NEW AS n
   FOR EACH ROW
DECLARE  
  BEGIN
     -- UPDATE SHIP CALENDAR SET UPDATE_CHANGE TO SYSDATE IF CHANGED CHANGES
     IF :n.CHANGED = '1' then  
        :n.CHANGED_DATE := SYSDATE
     END IF;  
END;

Upvotes: 3

Related Questions