0

I wrote a DB trigger to monitor an insert action. After inserting a new record, I would like to automatically set the CREATION_DATE to sysdate.

I get an error when I want to insert a new record:

error
ORA-04091: table REPORT is mutating, trigger/function may not see it
ORA-06512: at "CREATION_DATE_TEST", line 2
ORA-04088: error during execution of trigger 'CREATION_DATE_TEST'

My code: 

 CREATE OR REPLACE TRIGGER creation_date_test 
 AFTER INSERT ON REPORT FOR EACH ROW 
 BEGIN  
   UPDATE REPORT set CREATION_DATE = sysdate
   WHERE ROWID = :new.ROWID;
 END;

I also tried to replace ROWID = :new.ROWID with PROJECT_ID = new.PROJECT_ID. It throws the same error.

Tom
  • 6,988
  • 1
  • 26
  • 40
de li
  • 882
  • 1
  • 13
  • 25

1 Answers1

3

It sounds like you just want a before insert trigger that sets the :new.creation_date

create or replace trigger creation_date_test
  before insert on report
  for each row
begin
  :new.creation_date := sysdate;
end;
Justin Cave
  • 227,342
  • 24
  • 367
  • 384