Re: On-Update Trigger Question

James Arvigo wrote:

> The logic flow is as follows:
>
> Insert record to Table-1
> { Time Passes }
> Update record in Table-1
> Update causes trigger to fire
> Trigger creates a new record in Table-2
> (somehow) Delete Updated record from Table-1
>
> I have written an Update trigger on Table-1 that does everything up to
> the last action of deleting the record getting updated, which of course
> it cannot do, because that will cause a Mutation Error, and the entire
> process will rollback.
>

The typical solution to the mutating table problem is to defer processing until a statement level trigger. In this case, you create the record in Table-2 with a row level trigger and also store the primary key of the record in Table-1 that you want to delete. I usually do that by writing a package with procedures to perform all of my processing and then store the keys in a PL/SQL table that is a package variable. A statement level trigger will fire after update of Table-1 and walk the PL/SQL table, deleting the record for each key in the table. You could just as easily store the keys in a temporary (database) table.

Row level trigger:

after update on table-1
for each row
begin
  my_package.create_table_2_row(:new.primary_key);   my_package.save_key(:new.primary_key); end;

Statement level trigger:

after update on table-1
begin
  for i in 1..my_package.key_count loop

       my_package.delete_table_1_row(my_package.key_table(i));   end loop;
end;

Hope this helps.

-Michael Received on Tue Jul 21 1998 - 08:36:05 CDT