Re: Mutating Table - Not working with INSERT

On 8 feb, 07:38, "pankaj_wolfhun..._at_yahoo.co.in" <pankaj_wolfhun..._at_yahoo.co.in> wrote:
> 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
This is a known issue (bug?) for the BEFORE INSERT TRIGGERS. If you do an INSERT of a SINGLE ROW via INSERT INTO T VALUES(1,3000) the BEFORE INSERT trigger will NOT be fired.

But if you do:
INSERT INTO T SELECT 1, 3000 FROM DUAL the trigger will BE fired.

HTH Cheers.

Carlos. Received on Thu Feb 08 2007 - 02:39:35 CST