Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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

share|improve this question
    
I guess this is for Oracle? Please add a tag that specifies the DBMS. –  ypercube May 13 '13 at 19:26
add comment

1 Answer

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.

share|improve this answer
    
Thank you so much.it works. –  HImadri May 14 '13 at 2:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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