Re: !!! Help ..Mutating table problem

The best way around the mutating table error is to use a package to store your values, then do your insert in a statement level trigger (statement level triggers don't generate mutation errors).

For example, create a package that contains a PL/SQL table for each column you need to write to table 1. Include in this table an index variable of type binary integer.

In your after (or before) insert row level trigger on table 2, increment the index and write the data to the package variables.

In your after insert statement level trigger, read the PL/SQL tables from the package in a loop and insert the values into table 1. Then reset the value of the index to 0.

Just in case the transaction fails and is rolled back, it's a good idea to also reset the index to 0 in the before insert statement level trigger.

On Thu, 09 Jan 1997 20:04:01 GMT, kauj_at_lfs.loral.com (Joe) wrote:

> Any help is appreciated....
> We have two tables, table1 and table2.
> Table1 has three fields,
> obj_name, obj_type , total_number
> Table2 has threee fields,
> obj_name, obj_type, obj_threshold_vlaue,
>
> obj_name and obj_type are two primary keys in table1 and table2 is child
> table.
>
> We create a database trigger to update the total_number in table1, every
> time the table2 was inserted or deleted a record in that obj_name and
> obj_type. The trigger looks like this:
>
> Create or replace trigger name_trigger
> after
> INSERT or delete on table1
> for each row
> declare
> total number;
> begin
> select count(*) into total from table2
> where obj_name = :old.obj_name
> group by obj_name;
>
> update table1
> set total_number = total
> where obj_name = :old.obj_name;
> end;
>
> We are getting ORA-04091 errro and message said table "table2" is
> mutating, trigger/function may not see it.
>
> Please any help will be appreciated...
Received on Tue Jan 14 1997 - 00:00:00 CET