Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 7 April 2016

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
Share this article :

1 comments:

  1. could you please share notes on dynamic sql.

    ReplyDelete