Using Update Join Trigger

Question

I dont understand where is the error coming from... Following error is shown in my code.I have tried to search but not found anything useful.

Error starting at line : 31 in command - delete from Item_Requisite where Item_ID=104

Error report - ORA-04091: table DB20168002.ITEM_REQUISITE is mutating, trigger/function may not see it

ORA-06512: at "DB20168002.TR", line 2 ORA-04088: error during execution of trigger 'DB20168002.TR'

drop table Item_Master;
drop table Item_Requisite;
create table Item_Master(Item_ID number primary key,Description varchar2(50),Bal_Stock number);
insert into Item_Master values(101,'keyboard',80);
insert into Item_Master values(102,'lathe_machine',70);
insert into Item_Master values(103,'compass',100);
insert into Item_Master values(104,'compass',90);
insert into Item_Master values(105,'Ammeter',60);

create table Item_Requisite(Item_ID number primary key,Dept_Code varchar2(50),Quantity number);
insert into Item_Requisite values(101,'cs',30);
insert into Item_Requisite values(102,'mech',20);
insert into Item_Requisite values(103,'civil',25);
insert into Item_Requisite values(104,'chem',35);
insert into Item_Requisite values(105,'elect',80);

create or replace trigger tr
before delete 
on
Item_Requisite
for each row
begin
    MERGE INTO Item_Master t1
    USING Item_Requisite t2
    ON (t1.Item_ID=t2.Item_ID)
    WHEN MATCHED THEN 
    UPDATE SET t1.Bal_Stock=t1.Bal_Stock-t2.Quantity
    where t1.Item_ID=104;   
end;
/
delete from Item_Requisite where Item_ID=104;
/
select * from Item_Master;

You are trying to read and update the same table on which your trigger is created hence getting the error. Use compound trigger to avoid this — XING

Littlefoot Littlefoot · Accepted Answer · 2019-04-17T06:44:15

Don't MERGE; you aren't performing any INSERT (and MERGE is known as upsert (update/insert in a single operation). UPDATE will do the job:

SQL> create or replace trigger tr
  2  before delete
  3  on
  4  Item_Requisite
  5  for each row
  6  begin
  7    update item_master t1 set
  8      t1.bal_stock = t1.bal_stock - :old.quantity
  9      where t1.item_id = 104
 10        and :old.item_id = t1.item_id;
 11
 12  --    MERGE INTO Item_Master t1
 13  --    USING Item_Requisite t2
 14  --    ON (t1.Item_ID=t2.Item_ID)
 15  --    WHEN MATCHED THEN
 16  --    UPDATE SET t1.Bal_Stock=t1.Bal_Stock-t2.Quantity
 17  --    where t1.Item_ID=104;
 18  end;
 19  /

Trigger created.

SQL> delete from Item_Requisite where Item_ID=104;

1 row deleted.

SQL> select * from Item_Master;

   ITEM_ID DESCRIPTION                                         BAL_STOCK
---------- -------------------------------------------------- ----------
       101 keyboard                                                   80
       102 lathe_machine                                              70
       103 compass                                                   100
       104 compass                                                    55
       105 Ammeter                                                    60

SQL>

Using Update Join Trigger

1 Answers