Re: !!! Help ..Mutating table problem

Hi Joe,
What is probably causing the problem in your example is the constraint you have defined.

In cases like this, I've eliminated the foreign key constraint and implemented it via the trigger code. Since you have a trigger defined anyway, you can also add code to provide the constraint logic.

This should get around the mutating table problem in your example.

• Mike

Make SURE that the trigger provides all the levels of integrity that your constraint provided!

Joe <kauj_at_lfs.loral.com> wrote in article <19970109.150401.086_at_lfs.loral.com>...
> 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...
>
> Please e-mail to (joe.kau_at_lmco.com)
>
>
>
Received on Thu Jan 23 1997 - 00:00:00 CET