ORACLE After update trigger: solving ORA-04091 mutating table error

Question

I am trying to create a trigger:

  create or replace trigger NAME_OF_TRIGGER
  after insert or update on table1
  REFERENCING OLD AS OLD NEW AS NEW
  for each row

to fill in automatically a couple of non obligatory fields when updating/inserting on a table.

This requires me to use a cursor that selects from table2 and also table1 (the subject of the trigger).

Is there any way to avoid the mutating table error without using a temporary table for values or an autonomous transaction?

Solution

"Is there any way to avoid the mutating table error without using a temporary table for values or an autonomous transaction?"

tl;dr no.

---

The mutating table error is caused by querying the table which owns the trigger, or tables which are involved in a foreign key relationship with the owning table (at least in older versions of the database, not sure whether it still obtains).

In a properly designed application this should not be necessary. This is why many people regard mutating tables as an indicator of poor data modelling. For instance, mutation is often associated with insufficient normalisation.

---

To paraphrase Jamie Zawinski: Some people, when confronted with a mutating table exception, think "I know, I'll use autonomous transactions." Now they have two problems.

---

Sometimes the error can be avoided by simply modifying the :NEW values in a BEFORE INSERT OR UPDATE trigger or by using virtual columns. But you'll need to post more details to see whether these apply.

But the best workaround is not to need any other kind.

Comments

[JoséNunoFerreira]

Thanks! This confirms all i've been reading an experimenting. I am now changing the :new values on a before trigger; seems to work!

[Dexter]

Well, that's nice in theory, but what do you do when you want to use that mutating table in a complex join? Have a second temporary table you stuff the data of the trigger into, and then use that temporary table in the join? What do you do when you have hundreds such tables? Double your schema and have multiple versions of each query to cope for all possibilities of any joined table currently mutating .. or use dynamic SQL?

[Rich Bianco]

So either Oracle triggers are somewhat worthless... or I am - guessing the latter is probably valid yet the mutating trigger error is a pita royally.

[APC]

@RichBianco - It's probably true that triggers are worthless, or at least generally useful in only a few use cases. Mostly, mutating tables alert us to a problem in the data model or the business process.