I have two tables with the name of item
and stock_item
.
When I update the item
table then trigger with the name beforeItem
should fire, which subtracts new updated qty
from stock_qty
. But it throws
ORA-04091: table **** is mutating trigger/function may not see it
How can I fix this?
My tables:
create table stock_item
(no number primary key,itemName varchar2(10),stock_Qty number);
create table item
(no number,Name varchar2(10),qty number);
My trigger:
create or replace trigger beforeItem
before update on item
for each row
declare
chk_no number;
chk_item varchar2(10);
chk_qty number;
--pragma AUTONOMOUS_TRANSACTION;
-- this code will skip the update code.
begin
select no,name,qty into chk_no, chk_item,chk_qty from item where no=:new.no
and name=:new.name;
update stock_item set itemName = itemName - chk_qty where no=chk_no and
itemName=chk_item;
--commit;
end;
You cannot reference table ITEM in this trigger because it causes your error. Instead of using SELECT statement use new/old parameters. Try this version of the trigger.
create or replace trigger beforeItem
before update on item
for each row
begin
-- if :new.qty is not null then
update stock_item set
-- logic to maintaint if no changes on qty field were done
stock_Qty = stock_Qty - ( nvl(:new.qty,0) - nvl(:old.qty,0) )
where no=:new.no and itemName=:new.name;
-- end if;
end;