DBS Trigger/Mutuating Table (PL/SQL Procedure works)

From: <srinivas_at_cs.utep.edu>
Date: 26 Oct 1994 21:44:14 -0500
Message-ID: <9410270244.AA29698_at_cs.utep.edu>

Hi Oracle Gurus,

I have a PL/SQL Procedure which works fine. I would like to have a dbs trigger.  I would appreciate if anyone could help me. I dont mind if it can be done by creating temp table or where values can be inserted in temp and then passing the same to table MPS. I tried using temporary and pl/sql tables inside the trigger as suggested in` database programming design oct94 "Oracle7 trigger fixing mutuating tables". But had no luck.

---

Table1: MPS			|	Table2: Structure
				|
      SNO      MPVAL PARTN	|	PARTN COMPO    LEADOFF        QTY
---------- ---------- -----	|	----- ----- ---------- ----------
	 1          0 a01	|	a01   c01            1          3
	 2        100 a01	|	a01   b01            1          2
	 3          0 a01	|
         4        100 a01	|


............................My PL/SQL Procedure is ......................

DECLARE
CURSOR c2 is select partno,component,qty from structure; CURSOR c3 is select partno,sno,mpval from mps;

c2_partno     structure.partno%TYPE;
c2_comp       structure.component%TYPE;
c2_qty        structure.qty%TYPE;
c3_partno     mps.partno%TYPE;
c3_sno        mps.sno%TYPE;
c3_mpval      mps.mpval%TYPE;

BEGIN
OPEN c2;
LOOP

	FETCH c2 INTO c2_partno,c2_comp,c2_qty;
	EXIT WHEN c2%NOTFOUND;

OPEN c3;
LOOP

	FETCH c3 INTO c3_partno,c3_sno,c3_mpval;
	EXIT WHEN c3%NOTFOUND;
	IF c2_comp != c3_partno THEN
	IF c2_partno= c3_partno THEN   
	c3_mpval:=c3_mpval*c2_qty;
	INSERT INTO SCOTT.MPS(SNO,PARTNO,MPVAL) VALUES(c3_sno,c2_comp,c3_mpval);
	END IF;
	END IF;
END LOOP;
CLOSE c3;

END LOOP;
CLOSE c2;
COMMIT;
END;
/
............................CUT HERE.........................................
       SNO MPVAL PARTN
---------- ---------- -----

         1          0 a01
         2        100 a01
         1          0 c01
         3          0 a01	After the PL/SQL procedure is executed
	 2        300 c01	Result is as shown here.
         4        100 a01
         3          0 c01
         4        300 c01
         1          0 b01
         2        200 b01
         3          0 b01
         4        200 b01
----------------------------- 

I tried different ways in constructing a dbstrigger of AFTER INSERT ON mps FOR EACH ROW. When I do an insert on table MPS I got the following error. ORA-04091: table SCOTT.MPS is mutating, trigger may not read or modify it ORA-04088: error during execution of trigger 'SCOTT.MPSCHK'. Please send ur replies to srinivas_at_cs.utep.edu .Thanks in advance for your help.Regards Srini, < srinivas_at_cs.utep.edu > Received on Thu Oct 27 1994 - 03:44:14 CET