Re: Mutating Table - Not working with INSERT

On 8 feb, 09:39, "Carlos" <miotromailcar..._at_netscape.net> wrote:
> 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.

Ops ! sent it too early. It is the MUTATING TABLE exception what is not raising. The trigger will execute both ways.

Sorry for the confusion.

Cheers.

Carlos. Received on Thu Feb 08 2007 - 02:42:21 CST