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
