| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help on Mutating
Thomas Kyte wrote:
>
> A copy of this was sent to kelly <mekelly002_at_hotmail.com>
> (if that email address didn't require changing)
> On Fri, 26 Mar 1999 12:46:07 -0500, you wrote:
>
> >Question as following:
> >
> >From Oracle8 document:
> > BEFORE ROW and AFTER ROW triggers fired by a single row INSERT to a
> >table do
> > not treat that table as mutating or constraining. Note that INSERT
> >statements that may involve more than one row, such
> > as INSERT INTO emp SELECT . . ., are not considered single row
> >inserts, even if they only result in one row being
> > inserted.
> >
>
> I confirmed with a small test case:
>
> SQL> create table t ( x int );
>
> Table created.
>
> SQL> create or replace trigger t1
> 2 before insert on t for each row
> 3 begin
> 4 dbms_output.put_line( 'hi' );
> 5 for x in ( select * from t ) loop dbms_output.put_line( x.x ); end loop;
> 6 end;
> 7 /
>
> Trigger created.
>
> SQL> insert into t values ( 1 );
> hi
> hi
>
> 1 row created.
>
> SQL> delete from t;
>
> 1 row deleted.
>
> SQL>
> SQL> create or replace trigger t1
> 2 after insert on t for each row
> 3 begin
> 4 dbms_output.put_line( 'hi' );
> 5 for x in ( select * from t ) loop dbms_output.put_line( x.x ); end loop;
> 6 end;
> 7 /
>
> Trigger created.
>
> SQL> insert into t values ( 1 );
> insert into t values ( 1 )
> *
> ERROR at line 1:
> ORA-04091: table TKYTE.T is mutating, trigger/function may not see it
> ORA-06512: at "TKYTE.T1", line 3
> ORA-04088: error during execution of trigger 'TKYTE.T1'
>
> that this does in fact occurr but only on the AFTER FOR EACH ROW trigger. I
> filed bug 859769 on this.
>
> So, in light of that -- how best to proceed... It looks like you want to
> maintain the total for the details in the parent table. You'll need to do that
> for inserts/updates/deletes and you'll probably want to have it work for
> multi-row inserts/updates/deletes as well. If so, here is one solution tested
> with inserts/updates/deletes and multi-row operations. The trick here is to
> defer processing till the AFTER trigger...
>
> SQL> CREATE TABLE PO_2 (
> 2 PO_ID NUMBER (10) NOT NULL,
> 3 DESCR_TXT VARCHAR2(100) NOT NULL,
> 4 ADDR_ID_SHIP_TO NUMBER (10) NOT NULL,
> 5 ADDR_ID_BILL_TO NUMBER (10) NOT NULL,
> 6 CTCT_ID NUMBER (10) NOT NULL,
> 7 VENDOR_ID NUMBER (10) NOT NULL,
> 8 X_AMT NUMBER (10,2) NOT NULL)
> 9 ;
>
> Table created.
>
> SQL>
> SQL>
> SQL>
> SQL> CREATE TABLE PO_2_DTL (
> 2 PO_ID NUMBER (10) NOT NULL,
> 3 PO_DTL_ID NUMBER (10) NOT NULL,
> 4 ITEM_ID NUMBER (10) NOT NULL,
> 5 ORDR_QTY NUMBER (10,2) NOT NULL,
> 6 ORDR_PRC NUMBER (10,2) NOT NULL)
> 7 ;
>
> Table created.
>
> SQL>
> SQL>
> SQL> create or replace package po_pkg
> 2 as
> 3 type numArray is table of number index by binary_integer;
> 4
> 4 newIds numArray;
> 5 empty numArray;
> 6
> 6 procedure add2array( p_id in number );
> 7 end;
> 8 /
>
> Package created.
>
> SQL> create or replace package body po_pkg
> 2 as
> 3
> 3 procedure add2array( p_id in number )
> 4 as
> 5 l_found boolean default FALSE;
> 6 begin
> 7 for i in 1 .. newIds.count loop
> 8 l_found := newIds(i) = p_id;
> 9 exit when ( l_found );
> 10 end loop;
> 11 if ( not l_found ) then
> 12 newIds(newIds.count+1) := p_id;
> 13 end if;
> 14 end;
> 15
> 15 end;
> 16 /
>
> Package body created.
>
> SQL>
> SQL> create or replace trigger po_2_dtl_biud
> 2 before insert or update or delete on po_2_dtl
> 3 begin
> 4 po_pkg.newIds := po_pkg.empty;
> 5 end;
> 6 /
>
> Trigger created.
>
> SQL>
> SQL> create or replace trigger po_2_dtl_aiudfer
> 2 after insert or update or delete on po_2_dtl for each row
> 3 begin
> 4 if (inserting or updating ) then
> 5 po_pkg.add2array( :new.po_id );
> 6 end if;
> 7 if (updating or deleting ) then
> 8 po_pkg.add2array( :old.po_id );
> 9 end if;
> 10 end;
> 11 /
>
> Trigger created.
>
> SQL>
> SQL> create or replace trigger po_2_dtl_aiud
> 2 after insert or update or delete on po_2_dtl
> 3 begin
> 4 for i in 1 .. po_pkg.newIds.count loop
> 5 dbms_output.put_line( 'processing ' || po_pkg.newIds(i) );
> 6 update po_2 set x_amt = (select nvl(sum(ordr_qty*ordr_prc),0)
> 7 from po_2_dtl
> 8 where po_id = po_pkg.newIds(i) )
> 9 where po_id = po_pkg.newIds(i);
> 10 end loop;
> 11 end;
> 12 /
>
> Trigger created.
>
> SQL>
> SQL> insert into po_2 values(1,'First POrder',111,222,3333,4444,0);
>
> 1 row created.
>
> SQL> insert into po_2 values(2,'Second POrder',111,222,3333,4444,0);
>
> 1 row created.
>
> SQL> insert into po_2 values(3,'Third POrder',111,222,3333,4444,0);
>
> 1 row created.
>
> SQL>
> SQL>
> SQL> insert into po_2_dtl values(1,10,222,100,9.9);
>
> 1 row created.
>
> SQL> insert into po_2_dtl
> 2 select 1,10+rownum,222+rownum,100+rownum,9.9
> 3 from all_users where rownum < 5
> 4 /
>
> 4 rows created.
>
> SQL>
> SQL> insert into po_2_dtl values(2,10,222,100,9.9);
>
> 1 row created.
>
> SQL> insert into po_2_dtl
> 2 select 2,10+rownum,222+rownum,100+rownum,9.9
> 3 from all_users where rownum < 5
> 4 /
>
> 4 rows created.
>
> SQL>
> SQL> update po_2_dtl set po_id = po_id+1
> 2 /
>
> 10 rows updated.
>
> SQL>
> SQL> delete from po_2_dtl where po_id = 2 and rownum < 3
> 2 /
>
> 2 rows deleted.
>
> SQL>
> SQL> select po_id, x_amt from po_2;
>
> PO_ID X_AMT
> ---------- ----------
> 1 0
> 2 3059.1
> 3 5049
>
> SQL> select po_id, sum(ORDR_QTY*ORDR_PRC) from po_2_dtl group by po_id;
>
> PO_ID SUM(ORDR_QTY*ORDR_PRC)
> ---------- ----------------------
> 2 3059.1
> 3 5049
>
> SQL> select po_id, po_dtl_id, ordr_qty*ordr_prc from po_2_dtl;
>
> PO_ID PO_DTL_ID ORDR_QTY*ORDR_PRC
> ---------- ---------- -----------------
> 2 12 1009.8
> 2 13 1019.7
> 2 14 1029.6
> 3 10 990
> 3 11 999.9
> 3 12 1009.8
> 3 13 1019.7
> 3 14 1029.6
>
> 8 rows selected.
>
> >Create Table/Trigger
> >
> >CREATE or replace TABLE PO_2 (
> >PO_ID NUMBER (10) NOT NULL,
> >DESCR_TXT VARCHAR2(100) NOT NULL,
> >ADDR_ID_SHIP_TO NUMBER (10) NOT NULL,
> >ADDR_ID_BILL_TO NUMBER (10) NOT NULL,
> >CTCT_ID NUMBER (10) NOT NULL,
> >VENDOR_ID NUMBER (10) NOT NULL,
> >X_AMT NUMBER (10,2) NOT NULL)
> >;
> >
> >insert into po_2 values(1,'First POrder',111,222,3333,4444,0);
> >
> >
> >
> >CREATE or replace TABLE PO_2_DTL (
> >PO_ID NUMBER (10) NOT NULL,
> >PO_DTL_ID NUMBER (10) NOT NULL,
> >ITEM_ID NUMBER (10) NOT NULL,
> >ORDR_QTY NUMBER (10,2) NOT NULL,
> >ORDR_PRC NUMBER (10,2) NOT NULL)
> >;
> >
> >CREATE OR REPLACE TRIGGER AIU_PO_2_DTL
> >AFTER INSERT
> >ON PO_2_DTL
> >FOR EACH ROW
> >
> >DECLARE
> > CURSOR C1 IS
> > SELECT ORDR_QTY * ORDR_PRC X_AMT_PO_DTL
> > FROM PO_2_DTL
> > WHERE PO_ID = :NEW.PO_ID;
> >
> > X_AMT_PO_2 PO_2.X_AMT%TYPE := 0;
> >
> >BEGIN
> > FOR C1_REC IN C1 LOOP
> > X_AMT_PO_2 := X_AMT_PO_2 + C1_REC.X_AMT_PO_DTL;
> > END LOOP;
> > UPDATE PO_2 SET X_AMT = X_AMT_PO_2 WHERE PO_ID = :NEW.PO_ID;
> >END;
> >
> >Then I issued The following insert statement
> >insert into po_2_dtl values(1,10,222,100,9.9);
> >
> >But I got the following error. Oracle still treats trigger table as
> >mutating table. Why?
> >
> >error message:
> >SQLWKS> insert into po_2_dtl values(1,101,222,200,19.9);
> >ORA-04091: table SCOTT.PO_2_DTL is mutating, trigger/function may not
> >see it
> >ORA-06512: at "SCOTT.AIU_PO_2_DTL", line 3
> >ORA-06512: at "SCOTT.AIU_PO_2_DTL", line 10
> >ORA-04088: error during execution of trigger 'SCOTT.AIU_PO_2_DTL'
> >
> >Thank you for help!
> >
> >
> >
> >
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
(If this of use to Oracel support)
Interestingly this is a "new-ish" bug - I tried the same in 7.3.3 and
7.3.4 and did not notice the error...
--
 |
 |