CODEWITHSUNDEEP
oracle-databaseplsqltriggersdatabase-trigger
de li
de li

Reputation: 922

How to solve the Oracle DB trigger error?

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.

Upvotes: 0

Views: 551

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

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;

Upvotes: 3

Related Questions