Mutating Table Error (with a twist)

From: Robert Goodwin <robertgoodwin_at_yahoo.com>
Date: Wed, 19 Aug 1998 14:32:48 -0500
Message-ID: <6rf992$esd$1@hammer.msfc.nasa.gov>

Okay, I'm familiar with all the normal ways to get a mutating table error, but here's a new one:

I have two tables, defined as follows:

CREATE TABLE T1
(

     field_1     VARCHAR2(20),
     field_2     VARCHAR2(1),
     PRIMARY KEY (field1)

);

CREATE TABLE T2
(

     field_A     VARCHAR2(20),
     field_B     VARCHAR2(5),
     field_C     NUMBER,
     PRIMARY KEY (field_A, field_B),
     FOREIGN KEY (field_A) REFERENCES T1(field_1)

);

On table T2, we have a trigger defined as follows:

CREATE OR REPLACE TRIGGER t2_trigger
BEFORE UPDATE
ON T2 FOR EACH ROW
BEGIN

     UPDATE T1
     SET field_2 = 'Y'
     WHERE field_1 = :new.field_A;

END; On table T1, we have a trigger defined as follows:

CREATE OR REPLACE TRIGGER t1_trigger
BEFORE INSERT OR UPDATE
ON T1 FOR EACH ROW
BEGIN

     IF INSERTING THEN
          :new.field_1 := UPPER(:new.field_1);
     END IF;
     ....

END; Now, when we execute an UPDATE on table T2, we get a mutating table error on table T2. However, if we remove the IF INSERTING THEN block from t1_trigger, we no longer get the mutating table error. Since we are updating and not inserting, it seems that this block should make no difference. But it is definitely the cause of the mutating table error.

Any help (or even a suggestion) would be appreciated on this. Thanks.

Robert Received on Wed Aug 19 1998 - 14:32:48 CDT