Link to home
Start Free TrialLog in
Avatar of ali şahin
ali şahin

asked on

ORA-04091:table is mutating, trigger/function may not see it

hi expert .
I want to write a trigger.
I want him to throw an error when the 'ucreti' column exceeds 500.
trigger is running in insert operation.
But Trigger is failing to update.
for example
CREATE PACKAGE ucret_islemyap IS
  ucretler Number;
END;
create table islemyap
(
rowid1 varchar2(20),
protokol_no number,
dosya_no number,
ucreti number,
fatura_tipi number
)
Insert into HASTANE.ISLEMYAP
   (ROWID1, PROTOKOL_NO, DOSYA_NO, UCRETI,fatura_tipi)
 Values
   ('A198211', 838, 725, 70,1);
Insert into HASTANE.ISLEMYAP
   (ROWID1, PROTOKOL_NO, DOSYA_NO, UCRETI)
 Values
   ('A198212', 838, 725, 22);

Open in new window

I wrote trigger as below
create or replace trigger burak_islem_ucreti
for insert or update on islemyap compound trigger
toplam number;
before each row  is 
  begin

        if :new.FATURA_TIPI in (1,3) then
        begin
           
              select sum(iy.ucreti) into ucret_islemyap.ucretler  from islemyap iy  where IY.PROTOKOL_NO=:new.protokol_no and IY.FATURA_TIPI in (1,3);
              toplam:=ucret_islemyap.ucretler+nvl(:new.ucreti,0);
                if toplam>=500 then
                        begin
                        raise_application_error(-20101, 'fiyatiniz 500 tl yi asmistir');
                        end;
                end if;
          end;
      end if;
      
end  before each row ;
end;

Open in new window

running for trigger insert operation.
but trigger update doesn't work
for example
update islemyap iy 
set 
UCRETI=700
where IY.ROWID1='A198211'

Open in new window

trigger gives me error
ORA-04091: table HASTANE.ISLEMYAP is mutating, trigger/function may not see it

Open in new window


how can I solve this problem?
thanks a lot .
Avatar of johnsone
johnsone
Flag of United States of America image

The short answer, is you cannot select from the table the trigger fires on in a row level trigger.

Can you explain what you are trying to accomplish?  Because once you get that package variable to 500, then you'll never be able to do anything in that session again.  You aren't resetting it.
Avatar of ali şahin
ali şahin

ASKER

If the invoice(fatura_tipi) type is 1 or 3 of the records with the same protocol_no when the update is done, I would like to sum the charge(ucreti) column.
after
if the sum of the column(ucreti) exceeds 500, I want trigger error
I loaded what I wanted the  (trigger) output as a picture.
there may be places in the code that need to be corrected.
Ads-z.png
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
its work. thank you so much :)