CODEWITHSUNDEEP
sqloracle-databasetriggersora-04091
Shawn
Shawn

Reputation: 7365

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?

Upvotes: 1

Views: 1095

Answers (3)

Tony Andrews
Tony Andrews

Reputation: 132570

See Tom Kyte's definitive article on this topic.

Upvotes: 6

Benoit
Benoit

Reputation: 79175

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?

Upvotes: 2

Tim
Tim

Reputation: 5421

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?

Upvotes: 8

Related Questions