| 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
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;
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;
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;
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;
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;
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;
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
 |
 |