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?

You want to alter the structure of the table in a trigger? Am I reading that correctly? — HLGEM, Dec 06 '10 at 14:00
I'm sorry, I meant alter foo rows, not the structure...I'll edit — Shawn, Dec 06 '10 at 14:01

score 8 · Accepted Answer · answered Dec 06 '10 at 14:05

8

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?

answered Dec 06 '10 at 14:05

Tim

5,371
3
32
41

So you're saying put the delete logic in a stored procedure and call it from this trigger? – Shawn Dec 06 '10 at 14:07
No. Call the SP directly, to insert the row and do the side-effect deletes, wrapped in a transaction. The SP+transaction approach avoids the mess of mutating tables and is more self-documenting. – Tim Dec 06 '10 at 14:13

Tony Andrews · Answer 2 · 2017-01-18T10:08:12.710

6

See Tom Kyte's definitive article on this topic.

edited Jan 18 '17 at 10:08

answered Dec 06 '10 at 14:05

Tony Andrews

129,880
21
220
259

FYI - This link is dead. – Rich Bianco Jan 17 '17 at 19:07

score 2 · Answer 3 · answered Dec 06 '10 at 14:05

2

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?

answered Dec 06 '10 at 14:05

Benoit

76,634
23
210
236

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

3 Answers3

Linked