MUTATING TABLES ERROR

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