Forum OpenACS Q&A: ORA-04091 Problem with trigger, when deleting acs_rels

I try to delete a couple of relationships in a trigger, which fails with a
table AIESEC4.I2_COMMITTEES is mutating, trigger/function may not see it

What I want to do is, if a committee's status is set to 'Closed', all users, who are member of the group mentioned in the 'group_id' column of that table should be removed from that group.
So I have a trigger after update
which loops over the memberhships and deletes them:
  cursor c_membership is
  select rel_id from acs_rels where
  rel_type='membership_rel'
  and object_id_one = :new.group_id;

in the loop I do
  for row2 in c_membership loop
        acs_rel.delete(
        rel_id => row2.rel_id
        );
  end loop;

which produces the following error message:
ORA-04091: table AIESEC4.I2_COMMITTEES is mutating, trigger/function may not see it
ORA-06512: at "AIESEC4.ACS_OBJECT", line 114
ORA-06512: at "AIESEC4.ACS_REL", line 39
ORA-06512: at "AIESEC4.I2_COMM_STAT_CHANGE", line 20
ORA-04088: error during execution of trigger 'AIESEC4.I2_COMM_STAT_CHANGE'

I don't understand that, because I don't access the table i2_committees directly anywhere in that trigger, only the
:new and :old values.
The line that this refers to is
      execute immediate 'delete from ' || object_type.table_name ||
        ' where ' || object_type.id_column || ' = :object_id'
      using in object_id;

in acs_object.delete

Have a look at http://osi.oracle.com/~tkyte/Mutate/index.html to see how Thomas Kyte provides a solution for your problem.
I am still trying to understand, why I actually get this error. I can't see where I access the mutating table. The trigger is not on acs_rels and the foreign key in acs_rels references acs_objects. How is acs_rel.delete affected by i2_committees being inaccessible?
Perhaps there is a delete cascade constraints from acs_objects to i2_committees... That could be the reason of your mutating table error.
But I am not deleting the object of the committee, but the object of the acs_rel. And that should not cascade, I think
ok at least I found out that one now. I have a reference to acs_objects in i2_committees (because its an object), so when I try to delete an object, oracle does not know if that might be referenced by that mutating table. Bad luck, su I guess I have to go with this approach you pointed me to (still hoped i ould get around it...).