Thanks for the question, Subhash.

Asked: October 20, 2017 - 5:02 am UTC

Last updated: June 12, 2020 - 1:10 pm UTC

Version: 12.1

Viewed 50K+ times! This question is

You Asked

Hi,

I am getting "ORA-04091: table is mutating, trigger/function may not see it" in my trigger.

Please find the test case details as follows:

create table test(bug_number number, subject varchar2(50),ANALYZED_BY varchar2(50));
insert into test(bug_number,subject) values(12345,'This is a test issue');
insert into test(bug_number,subject) values(12346,'This is a code issue, pls fix');
insert into test(bug_number,subject) values(12347,'This is a test issue, pls update test');

Now create a trigger for update on ANALYZED_BY column of table test:

create or replace TRIGGER CUST_BUG_SEND_ASSIGNMENT_EMAIL 
  after UPDATE of ANALYZED_BY ON test 
  for each row
 
  when (NEW.ANALYZED_BY is not null)
  DECLARE
  l_plain CLOB;
  l_html CLOB;
  cust_bug_number number;
  bug_subject varchar2(100);
  BEGIN
    select bug_number, subject into cust_bug_number,bug_subject from test where bug_number=:new.bug_number;
END;
/

Trigger is created.

Now when I try to update the ANALYZED_BY column using following query:

update test set ANALYZED_BY='SUBHASKU' where bug_number=12345;

It is giving error:
ORA-04091: table CUST_BUG.TEST is mutating, trigger/function may not see it
ORA-06512: at "CUST_BUG.CUST_BUG_SEND_ASSIGNMENT_EMAIL", line 8
ORA-04088: error during execution of trigger 'CUST_BUG.CUST_BUG_SEND_ASSIGNMENT_EMAIL'

Please let me know how to resolve this.

Thanks,
Subhash

and Chris said...

You can't query the table that caused a trigger to fire inside the trigger itself.

But in this case, I don't see any need to. You can use :new to access the values you're selecting from the table instead:

create table test(bug_number number, subject varchar2(50),ANALYZED_BY varchar2(50));
insert into test(bug_number,subject) values(12345,'This is a test issue');
insert into test(bug_number,subject) values(12346,'This is a code issue, pls fix');
insert into test(bug_number,subject) values(12347,'This is a test issue, pls update test');

create or replace TRIGGER CUST_BUG_SEND_ASSIGNMENT_EMAIL 
  after UPDATE of ANALYZED_BY ON test 
  for each row
 
  when (NEW.ANALYZED_BY is not null)
DECLARE
  l_plain CLOB;
  l_html CLOB;
  cust_bug_number number;
  bug_subject varchar2(100);
BEGIN
  dbms_output.put_line(
    'BUG: ' || :new.bug_number || ' Subj: ' || :new.subject
  );
END;
/

set serveroutput on
update test set ANALYZED_BY='SUBHASKU' where bug_number=12345;

BUG: 12345 Subj: This is a test issue

1 row updated.

Also: I see that the trigger is called "CUST_BUG_SEND_ASSIGNMENT_EMAIL". Suggesting you're using utl_mail to send emails...

DON'T DO THIS!

This can lead to massive bugs. Sending email is non-transactional. Once it's sent, it's sent. If there's a mistake or error leading to a rollback, the email will still have gone. But the row won't have changed!

http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html

Ideally, you should change the code that runs the update to send the email afterwards, once you're sure the change is committed.

If you can't do this (easily), you could submit a job in the trigger that sends the email. The job will then be committed or rolled back with the update statement.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Subhash Kumar, October 20, 2017 - 2:19 pm UTC

Thanks Chris.
Sure I will opt for alternate solution rather sending email from trigger.

Technical update: use a compound trigger

Thomas Kasten, July 11, 2019 - 2:39 pm UTC

When this problem occurs you are probably trying to read or modify the triggers table in the code of a row-trigger.
The correct (and simplest) solution with modern oracle versions is to use a compound trigger, i.e. a trigger which has a row-part and a statement-part. (1) The row-part collects the data to modify or to read and (2) the statement-part does the real reading or updating afterwards.
The compound trigger is so useful here because we can share data between the 2 parts.

July 11, 2019 - 2:51 pm UTC

A compound trigger is the way. But only IF you need to query the table!

In most cases you shouldn't need to do this. Accessing the :new values is the correct and simplest solution in MOST cases.

When i am using two seperate triggers it also dos not work

Sravan, June 12, 2020 - 12:54 pm UTC

create or replace trigger dummy_trigger
before delete on main_table for each row
declare
begin
insert into dummy_table values (:old.serial_number);
end;

create or replace trigger main_trigger
after insert on dummy_table for each row
begin
update main_table v
set v.serial_number=v.serial_number-1
where v.serial_number > :new.serial_number;
end;

I am still getting the same error:
ORA-04091: table C##SRAVAN.main_table is mutating, trigger/function may not see it
ORA-06512: at "C##SRAVAN.MAIN_TRIGGER", line 3
ORA-04088: error during execution of trigger 'C##SRAVAN.MAIN_TRIGGER'
ORA-06512: at "C##SRAVAN.DUMMY_TRIGGER", line 3
ORA-04088: error during execution of trigger 'C##SRAVAN.DUMMY_TRIGGER'

June 12, 2020 - 1:10 pm UTC

You have circular references between your triggers.

Deleting from main_table fires dummy_trigger.
Which inserts into dummy_table
Causing main_trigger to fire
Which has a reference back to main_table!

You'll need to rewrite this process - ideally without triggers.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Technical update: use a compound trigger

When i am using two seperate triggers it also dos not work

More to Explore

PL/SQL demos

PL/SQL docs