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
but trigger update doesn't work
for example
how can I solve this problem?
thanks a lot .
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);
I wrote trigger as belowcreate 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;
running for trigger insert operation.but trigger update doesn't work
for example
update islemyap iy
set
UCRETI=700
where IY.ROWID1='A198211'
trigger gives me errorORA-04091: table HASTANE.ISLEMYAP is mutating, trigger/function may not see it
how can I solve this problem?
thanks a lot .
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
its work. thank you so much :)
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.