Mutating trigger while selecting a table on delete trigger

Question

Kindly help with the below. I have a table with two rows and while deleting one row I write a trigger and I want to write the records into a staging table(staging_tbl) of the other row which will be left after deletion. But it throws a mutating trigger error which is valid. But is there a way I can avoid it and write the records into staging table only there are 2 rows in main table and one of it is deleted(and not for all deletions on the table).

create or replace TRIGGER a_del_trg
  after delete on item_master
  for each row

DECLARE

  l_item NUMBER :=0;
  l_item_parent number :=0;
BEGIN

  INSERT INTO tmp_chk (item,item_parent) VALUES (:OLD.item,:OLD.item_parent);

  SELECT a.item,a.item_parent INTO l_item , l_item_parent
  FROM item_master a , tmp_chk  b  WHERE  a.item_parent = b.item_parent
  and a.item != b.item;

      INSERT INTO staging_tbl
        (create_date, table_name, item_sku, if_name)
      values
        (SYSDATE, 'Item_master', l_item, 'W'); -- want to add the remaining item here
    END IF;

END a_del_trg;

I just want to let you know `INSERT INTO tmp_chk (item,item_parent) VALUES (:OLD.item,:OLD.item_parent);` inserts the *deleted* row, and not the left one. — Hawk, Nov 26 '15 at 10:42
Hi Hawk, Yes i'm aware. I want the left one to be inserted into staging_tbl. — Shil N, Nov 26 '15 at 13:30

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

PRAGMA AUTONOMOUS_TRANSACTION

I reproduced your error using the following statement:

create table item_master(item number, item_parent number);
insert into item_master values (1, 10);
insert into item_master values (2, 10);

create table tmp_chk(item number, item_parent number);

create table staging_tbl(create_date date, table_name varchar2(30), item_sku number, if_name varchar2(10));

I used your trigger (after removing END IF residue code from the end of your trigger). I got the error "ORA-04091: table name is mutating, trigger/function may not see it." message.

Referring to this good explanation Fix Oracle mutating trigger table errors, one must reiterate the following excerpt:

At the end of the day, the mutating table error is usually the result of a poor application design and mutating triggers should be avoided whenever possible.

Following the fourth option in the reference autonomous transactions, I rewrote your trigger as follows:

create or replace TRIGGER a_del_trg
  after delete on item_master
  for each row

DECLARE

  l_item NUMBER :=0;
  l_item_parent number :=0;
  pragma autonomous_transaction;
BEGIN

  INSERT INTO tmp_chk (item,item_parent) VALUES (:OLD.item,:OLD.item_parent);
  
  SELECT a.item,a.item_parent INTO l_item , l_item_parent
  FROM item_master a , tmp_chk  b  WHERE  a.item_parent = b.item_parent
  and a.item != b.item;

      INSERT INTO staging_tbl
        (create_date, table_name, item_sku, if_name)
      values
        (SYSDATE, 'Item_master', l_item, 'W'); -- want to add the remaining item here
commit;
END a_del_trg;
/

Running the queries:

select * from item_master;
2   10

select * from tmp_chk ;
1   10

select * from staging_tbl;
27-NOV-15   Item_master 2   W

ROLLBACK

From here:

"... in 999 times out of 1000, if you find yourself "forced" to use an autonomous transaction - it likely means you have a serious data integrity issue you haven't thought about.

Where do people try to use them?

in that trigger that calls a procedure that commits (not an error logging routine). Ouch, that has to hurt when you rollback.

in that trigger that is getting the mutating table constraint. Ouch, that hurts even more

Error logging - OK.

Almost everything else - not OK."

Hawk , I have a question. Since the data gets committed every transaction, in case i rollback data after insert the data still remains in The staging table. How do i avoid this? — Shil N, Dec 16 '15 at 13:46
@ShilN That is exactly one of the main reasons *mutating table error is usually the result of a poor application design* Read my updated answer on this — Hawk, Dec 17 '15 at 01:21

Mutating trigger while selecting a table on delete trigger

1 Answers1