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 :

3 comments:

  1. could you please share notes on dynamic sql.

    ReplyDelete
  2. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com

    ReplyDelete
  3. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

    ReplyDelete