How to solve the Oracle DB trigger error?

Question

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.

Justin Cave · Accepted Answer · 2016-09-02 05:05:16Z

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;

answered Sep 2, 2016 at 5:05

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

de li Over a year ago

Thanks so much! Your answer really helpful!!

Justin Cave Over a year ago

@deli - Is creation_date a varchar2 rather than a date as the name would imply? I wouldn't expect there to be any strings used in the code I posted.

de li Over a year ago

I have changed the data type now. Thanks !!

Collectives™ on Stack Overflow

How to solve the Oracle DB trigger error?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related