Search code examples
sqloracle-databasetriggersora-04091

ORA-04091 - How can I alter a table that a trigger fires on?

So I have table foo and I would like to delete other foo rows when trigger t_foo fires:

CREATE OR REPLACE TRIGGER "t_foo" AFTER INSERT OR DELETE OR UPDATE ON foo

/*delete some other records from foo that are not :NEW.* or :OLD.* \*

How would I go about doing this without getting a ORA-04091: table name is mutating, trigger/function may not see it. Is this even possible?

Solution

  • Why not do this in a stored procedure, where you can wrap the insert and deletes in a transaction, and can clearly document this side-effect behavior?