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

DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E56ACCA.70D4C76E_at_exesolutions.com>...
> 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

I agree with Daniel. This code implementation looks pretty messy to me. I personally don't like to use triggers, but that is a separate discussion.

Just taking a cursory look at your after insert trigger, you open NewRows cursor, fetch it into dupNewRows but then you close it inside an IF statement. What if your IF statement fails... then you have an open cursor sitting inside a loop and you open it again on next iteration. There should be an ELSE to close NewRows cursor if your IF statement fails.

Secondly, What is the purpose of Cashflows cursor? It looks like you are not using any values out of it. You are also opening Cashflows in a loop but you keep updating only one rowid?

Regards
/Rauf Sarwar Received on Fri Feb 21 2003 - 23:35:16 CST