Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Mutating Table - Not working with INSERT

Mutating Table - Not working with INSERT

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 7 Feb 2007 22:38:56 -0800
Message-ID: <1170916736.340984.211730@p10g2000cwp.googlegroups.com>


Greetings,

               I was gng through the mutating table concept and found this statement on net :

"A mutating table is a table that is currently being modified by an
update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered
"mutating" and raises an error since Oracle should not return data
that has not yet reached its final state"

I tried on my schema:

SQL> desc t

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 ID                                                 NUMBER(38)
 SAL                                                NUMBER

SQL> select text from user_source where name='TEST_TRIG';

TEXT



TRIGGER TEST_TRIG
BEFORE INSERT OR UPDATE OR DELETE ON T
FOR EACH ROW
DECLARE
X NUMBER;
BEGIN
SELECT ID INTO X
FROM T
WHERE SAL=3000;
DBMS_OUTPUT.PUT_LINE('ID IS:'||X);
END; SQL> UPDATE T SET SAL=3000;
UPDATE T SET SAL=3000
       *
ERROR at line 1:
ORA-04091: table test.T is mutating, trigger/function may not see it
ORA-06512: at "test.TEST_TRIG", line 4
ORA-04088: error during execution of trigger 'test.TEST_TRIG'

SQL> DELETE FROM T WHERE SAL=3000;
DELETE FROM T WHERE SAL=3000
            *
ERROR at line 1:

ORA-04091: table test.T is mutating, trigger/function may not see it
ORA-06512: at "test.TEST_TRIG", line 4
ORA-04088: error during execution of trigger 'test.TEST_TRIG'

SQL> INSERT INTO T VALUES(1,3000);
ID IS:1

1 row created.

Why is trigger not getting fired for the insert statement?

I am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

TIA Received on Thu Feb 08 2007 - 00:38:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US