ORA-04091: table XXXX is mutating, trigger/function may not see it

Avoiding Mutating Tables

Ok, so you've just recieved the error:

ORA-04091: table XXXX is mutating, trigger/function may not see it

and you want to get around that. This short article will describe and demonstrate the various methods of getting around the mutating table error.

If you are interested in why you are getting it and in what cases you will get it, please see the Oracle Server Application Developers Guide (click here to read it right now -- this link is to technet.oracle.com.  You need a password to access this site but you can get one right away for free).

Avoiding the mutating table error is fairly easy.  We must defer processing against the mutating or constrainng table until an AFTER trigger.  We will consider two cases:
 

• Hitting the ORA-4091 in an INSERT trigger or an UPDATE trigger where you only need access to the :new values
• Hitting the ORA-4091 in a DELETE trigger or an UPDATE trigger where you need to access the :old values

Case 1 - you only need to access the :new values

This case is the simplest.  What we will do is capture the ROWIDS of the inserted or udpated rows.  We can then use these ROWIDS in an AFTER trigger to query up the affected rows.

It always takes 3 triggers to work around the mutating table error.  They are:
 

• A before trigger to set the package state to a known, consistent state
• An after, row level trigger to capture each rows changes
• An after trigger to actually process the change.

As an example -- to show how to do this, we will attempt to answer the following question:
 

I have a table containing a key/status/effective date combination.  When status
changes, the values are propagated by trigger to a log table recording the
status history.  When no RI constraint is in place everything works fine.

When an RI trigger enforces a parent-child relationship, the status change
logging trigger fails because the parent table is mutating.  Propagating the
values to the log table implicitly generates a lookup back to the parent table
to ensure the RI constraint is satisfied.

I do not want to drop the RI constraint.  I realize that the status is
denormalized.  I want it that way.  What is a good way to maintain the log?

Here is the implementation:

SQL> create table parent
  2  ( theKey        int primary key,
  3    status        varchar2(1),
  4    effDate       date
  5  )
  6  /
Table created.

SQL> create table log_table
  2  (       theKey  int references parent(theKey),
  3          status  varchar2(1),