sql - 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?

Answer 1

"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.