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;

Answer 1

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."