HELP! Package for avoiding Mutating Trigger by tracking rowids is empty

From: April <PrivateBenjamin_at_hushmail.com>
Date: 21 Feb 2003 13:29:32 -0800
Message-ID: <54df0379.0302211329.63f70c08@posting.google.com>

I receive this error

ORA-04092: cannot ROLLBACK in a trigger
ORA-06512: at "BRPRTEST.TRG_TBLSUBINVESTMENT_CASHFLOWS", line 46
ORA-06502: PL/SQL: numeric or value error
ORA-04088: error during execution of trigger

'BRPRTEST.TRG_TBLSUBINVESTMENT_CASHFLOWS' when attempting to insert a row into a table which has a 3 triggers, defined as per Tom Kyte's example for avoiding mutating triggers.

here is the package:
CREATE OR REPLACE package state_pkg as

   type ridArray is table of rowid index by binary_integer;    newrows ridarray;
   empty ridarray;
   cnt number;
End;

here are the triggers:
CREATE OR REPLACE TRIGGER TRG_subinvestments_AVOIDMT before insert on TBLSUB_INVESTMENT_CASHFLOWS begin
 state_pkg.cnt := 0;
end;

CREATE OR REPLACE TRIGGER trg_subinvestments_afterIU after insert on tblsub_investment_cashflows for each row
begin
 state_pkg.cnt := state_pkg.cnt + 1;
 state_pkg.newRows(state_pkg.cnt) := :new.rowid; end;

CREATE OR REPLACE TRIGGER TRG_TBLSUBINVESTMENT_CASHFLOWS AFTER INSERT ON TBLSUB_INVESTMENT_CASHFLOWS DECLARE vInv Number(11);
vSubInv Number(11);
vYear Number (4);
i Number ;
vInterest Number;

• comparison view (unsent unapproved capital cashflows, max effective dated)
• if record not in this view, then don't calculate the interest CURSOR Cashflows (vInv Number, vSubInv Number, vYear Number) is select * from VW_CASHFLOWS_FOR_INT_CALC WHERE Investment# = vInv and Sub_Investment# = vSubInv and Year = vYear; dupCashflows Cashflows%RowType;
  • get the rows that were processed CURSOR NewRows is select * From tblSub_Investment_Cashflows where rowid = state_pkg.newrows(i); dupNewRows NewRows%Rowtype;

BEGIN

• loop through the processed rows FOR i in 1 .. state_pkg.cnt Loop OPEN NewRows; FETCH NewRows into dupNewRows; If NewRows%rowcount > 0 Then vInv := dupNewRows.INVESTMENT#; vSubInv := dupNewRows.SUB_INVESTMENT#; vYear := dupNewRows.Year; CLOSE NewRows;

		OPEN CashFlows(vInv,vSubInv,vYear);
		LOOP
	   	FETCH Cashflows Into dupCashflows;
	   	EXIT WHEN Cashflows%notfound;
                IF Cashflows%rowcount>0 THEN
	  	-- calculate the new interest
	           vInterest := CalculateInterest(vInv,vSubInv,vYear);
		-- update the record with the interest value.
		   UPDATE tblSub_Investment_CashFlows SET INTEREST = vInterest WHERE
rowid = state_pkg.newrows(i);
               	END IF;
	        END LOOP;
	   	CLOSE CashFlows;
	END IF;

 END LOOP;    EXCEPTION

     WHEN OTHERS THEN
       ROLLBACK;
	   RAISE;

END TRG_TBLSUBINVESTMENT_CASHFLOWS; When debugging this error comes up:
Error on line 11
ALTER PACKAGE "BRPRTEST".STATE_PKG COMPILE DEBUG BODY ORA-04043: object STATE_PKG does not exist

however it does exist and if I disable the after statement trigger then the other two triggers don't complain.

If i look at the value of (i) or the count of the state_pkg.. it is null...
then it fails again when trying to open the NewRows cursor because the (i) is null.

I have this same setup working on other tables using the same package...

Does anyone know what's going on here!

Thanks very much,
April Received on Fri Feb 21 2003 - 15:29:32 CST