CODEWITHSUNDEEP
sqloracle-databasetriggers
yshaikh20x
yshaikh20x

Reputation: 39

ORA-04091: table is mutating, trigger/function may not see it, ORA-06512:, ORA-06512: at "SYS.DBMS_SQL", line 1721

Question: Create a single trigger named trg_job_date that will update the JOB_LAST_UPDATE column in the job table to the current date on insert or update. Test the trigger with an insert and update statement – include the insert and update statements.

So I'm trying to update job_last_update in job through a trigger but I read that I can have the same table I want to update in the trigger. My main problem is that job_last_update is only available in the job table.

Here is the erd: enter image description here

And here is my code so far:

create or replace trigger trg_job_date 
after insert or update
on job
for each row 

begin
    update job
        set job_last_update = current_date;
end;

insert into job (job_code, job_description, job_chg_hour, job_last_update)
values('511', 'Hardware management', '22.11', '4/4/2021');

update job 
set job_description = 'Hardware Manager' 
where job_code = 511;

select * from job; --using this to test whether the trigger worked properly

The trigger runs no problem, however when I try to run my insert statement it gives me the error:

ORA-04091: table SQL_IUARPVSJLXIJWEVKOWRJRCSXZ.JOB is mutating, trigger/function may not see it ORA-06512: at "SQL_IUARPVSJLXIJWEVKOWRJRCSXZ.TRG_JOB_DATE", line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721

What do I need to do in order to make my code do what I need it to do?

Upvotes: 0

Views: 1203

Answers (1)

pmdba
pmdba

Reputation: 7033

Don't create a new transaction with a separate update. Use the :new pseudo table in a before trigger to modify the column as part of the same transaction.

create or replace trigger trg_job_date 
before insert or update
on job
for each row 
begin
    :new.job_last_update := sysdate;
end;

See documentation, here: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-triggers.html#GUID-4F93F21E-BA7F-4378-87E6-46A8E4C03287

Upvotes: 3

Related Questions