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


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

Solution: There are various solutions to solve this, however it requires deeper understanding on what mutating error is and how to resolve this. You will encounter this when working on a trigger for a particular table and the trigger code is accessing the same table on which the trigger is operating on.

Simple solution is to remove references to any usage of the table on which trigger is operating on, if at all possible. How ? Go on…

If you have a ORDER table that has ORDER_ID AND CUSTOMER_ID AND LAST_UPDATED

An ORDER_DETAILS table with ORDER_ID,ORDER_DETAILS.

Suppose the trigger is on the ORDERS table FOR EACH ROW and BEFORE  UPDATE. Trigger is to update  the ORDER_DETAILS table for a particular existing order along with updating the timestamp in ORDERS table. To do so, if you try to access the ORDER_DETAILS as below, you will end up with mutating error, as in

SELECT ORDER_DETAILS INTO DETAILS

FROM ORDERS O, ORDER_DETAILS OD

WHERE O.ID = OD.ID AND O.ID = :OLD.ID

I have added some reference down below to understand :NEW and :OLD for those who want to understand how to use them in triggers. In the above query, problem is trigger is on ORDERS table and trigger is also trying to access the ORDERS table. Here there is no need to access ID from ORDERS table to join with ORDER_DETAILS Details.   

Instead, simplify the query by removing the references to ORDERS table to avoid mutating error

SELECT ORDER_DETAILS INTO DETAILS

FROM  ORDER_DETAILS OD

WHERE OD.ID  = :OLD.ID

Identifying scenarios like this before dwelling into complex solutions will save time 🙂

Accessing Column Values in Row Triggers

Within a trigger body of a row trigger, the PL/SQL code and SQL statements have access to the old and new column values of the current row affected by the triggering statement. Two correlation names exist for every column of the table being modified: one for the old column value, and one for the new column value. Depending on the type of triggering statement, certain correlation names might not have any meaning.

  • A trigger fired by an INSERT statement has meaningful access to new column values only. Because the row is being created by the INSERT, the old values are null.
  • A trigger fired by an UPDATE statement has access to both old and new column values for both BEFORE and AFTER row triggers.
  • A trigger fired by a DELETE statement has meaningful access to :old column values only. Because the row no longer exists after the row is deleted, the :new values are NULL. However, you cannot modify :new values:ORA4084 is raised if you try to modify :new values.

The new column values are referenced using the new qualifier before the column name, while the old column values are referenced using the old qualifier before the column name. For example, if the triggering statement is associated with the Emp_tab table (with the columns SALCOMM, and so on), then you can include statements in the trigger body. For example:

IF :new.Sal > 10000 ...
IF :new.Sal < :old.Sal ...
  1. #1 by jack on August 19, 2013 - 6:38 am

    I just extra up your RSS feed to my Bing News Reader.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: