Friday, April 26, 2013

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

No comments:

Post a Comment