Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

Dig Deeper on Using Oracle PL-SQL

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.

Join the conversation

2 comments

Send me notifications when other members comment.

Please create a username to comment.

Replase after update with before update in your trigger
Cancel
Change After update with before update in your trigger
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close