Help on Mutating

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.

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! Received on Fri Mar 26 1999 - 11:46:07 CST