Re: [Q] Database Trigger mutating error...

I have a solution, but it involves another table. The additional table will essentially stage the primary key(s). I am truly uncertain if this is the best method but, I do know that it works.

The solution involves a ROW level trigger BEFORE update and a STMT level trigger AFTER update. The test scripts I used to validate this method are as follows:

create or replace trigger close_work_order before update of status on current_table for each row when (status = 'C')
begin

   insert into history_table(key, status) values (:old.key, :old.status); end;
/

create or replace trigger close_work_order_AFTER after update of flag on temp
begin

   delete current_table where key in (select key from temp_key);    delete temp_key;
end;
/

As mentioned above, the additional table TEMP_KEY, provides a staging area for the index to be deleted, so when you arrive to your statement level trigger, you will have access to the row(s) that require cleanup, including the staging table.

Good Luck

-D Received on Fri Aug 04 1995 - 00:00:00 CEST