Oracle PL/SQL Mutating Triggers

A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

A mutation table is defined as a table that is changing. But in dealing with triggers, it is a table that has the possibility of changing.

What this means to a trigger is that if the trigger reads a table, it can not change the table that it read from. This does not impact the exclusive use of :OLD and :NEW.

It says that if the trigger reads the table (such as using a SELECT query), that changes (even using :NEW) will fail. This can also happen when a trigger on a parent table causes an insert on a child table referencing a foreign key.

If we are trying this oracle server returns mutating error oracle-4091: table is mutating.

This error is called mutating error, and this trigger is called mutating trigger, and table is called mutating table.

Mutating errors are not occurred in statement level trigger because through these statement level trigger when we are performing DML operations, data automatically committed into the database, whereas in row level trigger when we are performing transaction, data is not committed and also again we are reading this data from the same table then only mutating errors are occurred.

Demonstration of Mutating Table Error

Create a row level trigger on EMP table.

CREATE OR REPLACE TRIGGER emp_sal_trg
AFTER INSERT OR UPDATE
ON emp
FOR EACH ROW
DECLARE
    l_max_allowed emp.sal%TYPE;
BEGIN
    SELECT MIN(sal) * 10 INTO l_max_allowed FROM emp;
    IF l_max_allowed < :NEW.sal
    THEN 
        UPDATE emp SET sal = l_max_allowed
        WHERE empno= :NEW.empno;
    END IF;
END emp_sal_trg;

SELECT MIN(sal) FROM EMP;
 

MIN(SAL)
-------
800

Now update the sal in emp table using below block.

UPDATE emp SET sal = 1100 WHERE empno= 7944;
 

ORA-04091: table DE.EMP is mutating, trigger/function may not see it
ORA-06512: at "DE.EMP_SAL_TRG", line 4
ORA-04088: error during execution of trigger 'DE.EMP_SAL_TRG'

Avoiding Mutating triggers

• Don't use triggers -
  The best way to avoid the mutating table error is not to use triggers. While the object-oriented Oracle provides "methods" that are associated with tables, most savvy PL/SQL developers avoid triggers unless absolutely necessary.
• Use statement-level trigger
• Use an "after" or "instead of" trigger -
  If you must use a trigger, it's best to avoid the mutating table error by using an "after" trigger, to avoid the concurrency issues associated with a mutating table. For example, using a trigger ":after update on xxx", the original update has completed and the table will not be mutating.
• Use autonomous transactions -
  You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.
• Using compound trigger -
  

  CREATE OR REPLACE TRIGGER emp_sal_trg
  FOR INSERT OR UPDATE
  ON emp
  COMPOUND TRIGGER
  TYPE r_emp_type IS RECORD(
  empno  emp.empno%TYPE,
  sal emp.sal%TYPE
  );
  TYPE t_emp_type IS TABLE OF r_emp_type INDEX BY PLS_INTEGER;
      t_emp t_emp_type;
  
      AFTER EACH ROW IS
      BEGIN
          t_emp(t_emp.COUNT+1).empno :=:NEW.empno;
          t_emp(t_emp.COUNT+1).sal :=:NEW.sal;
      END AFTER EACH ROW;
      AFTER STATEMENT IS
          l_max_allowed emp.sal%TYPE;
      BEGIN
      SELECT MIN(sal) * 10 INTO l_max_allowed FROM emp;
  
      FOR idx IN 1.. t_emp.COUNT
          LOOP
              IF l_max_allowed < t_emp(idx).sal
              THEN
                  UPDATE emp SET sal = l_max_allowed
                  WHERE empno = t_emp(idx).empno;
              END IF;
          END LOOP;
      END AFTER STATEMENT;
  END emp_sal_trg;
  

  Now run the below statement again

  UPDATE emp SET sal = 800 WHERE empno = 7944;
  

There are exceptions about views being inherently updateable. The exceptions (or restrictions) include views that use aggregate functions; group functions; use of the DISTINCT keyword; use of GROUP BY, CONNECT BY or START WITH clauses; and use of some joins. In many cases, use of the INSTEAD-OF trigger feature allows you to work around these restrictions.

INSTEAD-OF triggers are also useful for Forms developers because forms are commonly based on views. The INSTEAD-OF trigger, being a "real" trigger, and not a true form trigger, is stored on the server."