Forum OpenACS Q&A: ORA-04091 Problem with trigger, when deleting acs_rels
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
and object_id_one = :new.group_id;
in the loop I do
for row2 in c_membership loop
rel_id => row2.rel_id
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;