CODEWITHSUNDEEP
oracle-databasetriggers
Mikayil Abdullayev
Mikayil Abdullayev

Reputation: 12376

Oracle After Update Trigger error

I want to keep track of changes to one table in another table. What I need is an after update trigger which writes the name of changed column (if multiple columns are changed then there will be multiple inserts to the CHANGES table),the column's old and new values. How do I do that. I tried this but got an error after updating the table.So I'm giving you just the body.

IF :NEW.STAJYEAR!=:OLD.STAJYEAR THEN
 INSERT INTO X_STAJ (USERID,EDITDATE,CHANGEDCOLUMN,OLDVALUE,NEWVALUE)
 VALUES (:NEW.USERID,SYSDATE,'STAJYEAR',:OLD.STAJYEAR,:NEW.STAJYEAR);
END IF;

the error code is :ORA-04098: trigger 'SYS.TR__TRACK_CHANGES' is invalid and failed re-validation

CREATE OR REPLACE TRIGGER STAJCHANGER.TR_TRACK_CHANGES
AFTER UPDATE
OF STAJYEAR
  ,STAJMONTH
  ,STAJDAY
ON STAJCHANGER.STAJ 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
OLDVALUE NUMBER;
NEWVALUE NUMBER;
COLUMNID NUMBER;


BEGIN


   IF :NEW.STAJYEAR!=:OLD.STAJYEAR THEN
    INSERT INTO X_STAJ (USERID,EDITDATE,CHANGEDCOLUMN,OLDVALUE,NEWVALUE)
    VALUES (:NEW.USERID,SYSDATE,'STAJYEAR',:OLD.STAJYEAR,:NEW.STAJYEAR);
    END IF;

   IF :NEW.STAJMONTH!=:OLD.STAJMONTH THEN
    INSERT INTO X_STAJ (USERID,EDITDATE,CHANGEDCOLUMN,OLDVALUE,NEWVALUE)
    VALUES (:NEW.USERID,SYSDATE,'STAJMONTH',:OLD.STAJMONTH,:NEW.STAJMONTH);
    END IF; 

       IF :NEW.STAJDAY!=:OLD.STAJDAY THEN
    INSERT INTO X_STAJ (USERID,EDITDATE,CHANGEDCOLUMN,OLDVALUE,NEWVALUE)
    VALUES (:NEW.USERID,SYSDATE,'STAJDAY',:OLD.STAJDAY,:NEW.STAJDAY);
    END IF;

END TR_TRACK_CHANGES;
/

Upvotes: 0

Views: 1888

Answers (2)

Dave Costa
Dave Costa

Reputation: 48151

The error appears to indicates that the trigger owner is SYS, but the creation statement you show explicitly gives the owner as STAJCHANGER.

This makes me wonder, did you accidentally create an (invalid) version of the trigger in SYS at some point, and forget to drop it?

Upvotes: 4

Tony Andrews
Tony Andrews

Reputation: 132750

This SQL Plus command will show the error:

SHOW ERROR TRIGGER STAJCHANGER.TR_TRACK_CHANGES

Upvotes: 0

Related Questions