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

April wrote:

> 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

I don't have the time right now to try to build or analyze this but it seems to me you have chosen a remarkably complex convoluted way to perform a straight-forward task. Tom's examples all work but your implementation may be flawed.

What is it you are actually trying to achieve? State the buiness case.

Daniel Morgan Received on Fri Feb 21 2003 - 16:48:43 CST