1
CREATE OR REPLACE TRIGGER  "TRANSACTION" 
before
update of  totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD
for each row
begin
  declare
    mportfolioname varchar2(20);
    mportfoliono number;
    moperationmode varchar2(8);
    mtransactionworth number;
    munits number;

    mnav number;

  begin
    select portfoliono into mportfoliono from sipholder where accno=:new.accno;
    select portfolioname into mportfolioname from sip_price 
      where portfoliono=mportfoliono;
    munits:= :new.totalunitsbought - :old.totalunitsbought;

    select nav into mnav from sip_price where portfoliono=mportfoliono;

   if :new.totalunitsbought>:old.totalunitsbought then
      moperationmode:='B';
      mtransactionworth:=munits*mnav;
   else
      moperationmode:='S';
      mtransactionworth:=munits*mnav;
   end if;

   insert into transaction
     (accno, ransdate, modeofoperation, units, transactionworth, portfoliono, 
      portfolioname, oldunits, newunits) 
    values
      (:new.accno, sysdate, moperationmode, munits, mtransactionworth, 
       :new.portfoliono, mportfolioname, :old.totalunitsbought, 
       :new.totalunitsbought);

  end;
end;

ERROR:

ERROR at line 1:
ORA-04091: table ORA-04091: table NETBANKING.SIPHOLDER is mutating,
trigger/function may not see it
ORA-06512: at "NETBANKING.TRANSACTION", line 12
ORA-04088: error during execution of trigger 'NETBANKING.TRANSACTION'
ORA-06512: at "NETBANKING.BUYUNITS_FIFTEEN", line 23
. is mutating, trigger/function may not see it
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 1

1
  • I guess this is for Oracle? Please add a tag that specifies the DBMS. May 13, 2013 at 19:26

1 Answer 1

2

Replace

select portfoliono into mportfoliono from sipholder where accno=:new.accno;

with

mportfoliono := new.portfoliono;

no need to use a SELECT for values of the updated row.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.