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 -> Re: Mutating Table - Not working with INSERT

Re: Mutating Table - Not working with INSERT

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 8 Feb 2007 22:48:08 -0800
Message-ID: <1171003688.150390.230390@k78g2000cwa.googlegroups.com>


On Feb 8, 2:58 pm, "Carlos" <miotromailcar..._at_netscape.net> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Anything in english? :-) Received on Fri Feb 09 2007 - 00:48:08 CST

Original text of this message

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