CODEWITHSUNDEEP
oracle-databasedatabase-trigger
Mirwais Faizi
Mirwais Faizi

Reputation: 17

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;

Upvotes: 0

Views: 4791

Answers (2)

APC
APC

Reputation: 146179

Oracle hurls ORA-04091 when a trigger issues DML against the table which owns the trigger; this includes SELECT statements. The reason is simple: the state of the table is unknown because the trigger is firing during a transaction, so the outcome of the trigger's DML is unpredictable.

The solution is usually quite simple: remove the DML. That certainly would seem to be the answer here, because your :NEW record has all the values you need to execute the update on stock_item:

create or replace trigger beforeItem
    before update on item
    for each row 
 begin

    update stock_item si
    set si.stock_Qty = si.stock_Qty - :new.qty 
    where si.no = :new.no; 

end;

but the stock_item table don't know what is the current value of the item table qty to subtract from.

Okay, so what you mean is, you want to update the STOCK_ITEM.QTY with the difference between the old (current) ITEM.QTY and the new (updated) value. Then that would be something like:

create or replace trigger beforeItem
    before update on item
    for each row 
 begin

    update stock_item si
    set si.stock_Qty = si.stock_Qty - ( (nvl(:old.qty,0) - nvl(:new.qty,0)) )
    where si.no = :new.no; 

end;

Here is a demo of my solution on SQL Fiddle.

Incidentally, note that I have corrected your UPDATE statement: subtracting Item Quantity from the Stock Name really doesn't make sense. Also, there is no need to use itemName in the WHERE clause when there is a primary key to use.

Upvotes: 1

Jacek Wróbel
Jacek Wróbel

Reputation: 1222

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;

Upvotes: 0

Related Questions