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 last published in August 2006

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close