Mutating Tables

There are restrictions on the tables and columns that a trigger body may access.

In order to define these restrictions, it is necessary to understand mutating and constraining tables.

    A mutating table is table that is currently being modified by a DML statement and the trigger event also DML statement.

A mutating table error occurs when a row-level trigger tries to examine or change a table that is already undergoing change.

A constraining table is a table that might need to be read from for a referential integrity constraint.

Ex:

CREATE OR REPLACE TRIGGER mutating_trigger

   BEFORE DELETE

   ON student

   FOR EACH ROW

DECLARE

   ct   NUMBER;

BEGIN

   SELECT COUNT (*)

     INTO ct

     FROM student

    WHERE NO = :OLD.NO;

END mutating_trigger;  

Output:

           SQL> delete student where no = 1;

                     delete student where no = 1

                        *

                     ERROR at line 1:

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

                     ORA-06512: at "SCOTT.T", line 4

                     ORA-04088: error during execution of trigger 'SCOTT.T'

HOW TO AVOID MUTATING TABLE ERROR?

Ø By using autonomous transaction

Ø By using statement level trigger