Ask the Expert

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: