Re: Mutating Table - Not working with INSERT

On 8 feb, 10:44, "pankaj_wolfhun..._at_yahoo.co.in" <pankaj_wolfhun..._at_yahoo.co.in> wrote:
> On Feb 8, 1:42 pm, "Carlos" <miotromailcar..._at_netscape.net> wrote:
>
>
>
> > 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.- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks Carlos. But I still quite didnt for what you said here? Can you
> elaborate?

Sure.

You can see a complete example here:

http://carlosal.wordpress.com/2007/02/08/tablas-mutantes-que-no-mutan/

(but in spanish)

HTH. Cheers.

Carlos. Received on Thu Feb 08 2007 - 03:58:44 CST