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
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.