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

sql oracle plsql triggers mutating-table
JoséNunoFerreira picture JoséNunoFerreira · Aug 2, 2011 · Viewed 12.9k times · Source

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

APC picture APC · Aug 2, 2011

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

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More