oracle-databasedatabase-trigger

How to fix mutating table error in trigger?


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; 

Solution

  • 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;