Q

ORA-04091: table is mutating

I encountered the error "ORA-04091: table XXXX is mutating, trigger/function may not see it" when I created an after insert trigger. I tried adding PRAGMA AUTONOMOUS_TRANSACTION to the code. The error went away but the table didn't get updated.

I encountered the error "ORA-04091: table XXXX is mutating, trigger/function may not see it" when I created an after insert trigger. The trigger is as follows:
CREATE OR REPLACE TRIGGER trg1 AFTER INSERT ON 
   regaddress FOR EACH ROW
DECLARE BEGIN
   UPDATE regaddress SET
   destname = name WHERE
   detailscode :NEW.detailscode;
 commit;
END;
As seen on the net I tried adding PRAGMA AUTONOMOUS_TRANSACTION to the code. The error went away but the table didn't get updated. Also I want this kind of update to happen as I cannot modify it through the front end because the same form is running in different departments, so the only option is to write a trigger.
A mutating table is a table that is currently being modified by an update, delete or insert statement. Oracle returns the ORA-04091 error if a row trigger reads or modifies the mutating table (for example, if a trigger contains a select statement or an update statement referencing the table it is triggering off of).

One way to handle this situation is to use a package PL/SQL table to store ROWIDs of updated records in a row trigger,

and reprocess the updated records in a statement trigger.

Search for Note:156388.1 at metalink.oracle.com for more info.

This was first published in August 2006

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close