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

Question

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?

Answer 1

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?

Answer 2

See Tom Kyte's definitive article on this topic.

Answer 3

This basically implies you have interdependent rows in your table, possibly a hierarchical structure with a self-reference from a column to the primary key. Did you think about ON DELETE CASCADE?