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

by

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

More Related Contents:

  1. Receive an unexpected symbol error in my PLSQL query [closed]
  2. PL SQL UPDATE STATEMENT
  3. Splitting string into multiple rows in Oracle
  4. Search All Fields In All Tables For A Specific Value (Oracle)
  5. What is the string concatenation operator in Oracle?
  6. Is it possible to output a SELECT statement from a PL/SQL block?
  7. REGEX to select nth value from a list, allowing for nulls
  8. SQL recursive query on self referencing table (Oracle)
  9. How can you tell if a value is not numeric in Oracle?
  10. IF EXISTS condition not working with PLSQL
  11. PLS-00428: an INTO clause is expected in this SELECT statement
  12. passing table and column name dynamically using bind variables
  13. SQL error “ORA-01722: invalid number”
  14. Is there any way to flush output from PL/SQL in Oracle?
  15. Sleep function in ORACLE
  16. Array in IN() clause oracle PLSQL
  17. PLSQL Insert into with subquery and returning clause
  18. Different CURRENT_TIMESTAMP and SYSDATE in oracle
  19. What does the colon sign “:” do in a SQL query?
  20. How to query a CLOB column in Oracle
  21. How to return multiple rows from the stored procedure? (Oracle PL/SQL)
  22. Issue when comparing result of to_char(myDate, ‘DAY’) to a string
  23. Advice Using Pivot Table in Oracle
  24. In Oracle, is it possible to INSERT or UPDATE a record through a view?
  25. Creating or simulating two dimensional arrays in PL/SQL
  26. How to handle optional parameters in SQL query?
  27. Oracle- Split string comma delimited (string contains spaces and consecutive commas)
  28. How to use Alias in Where clause? [duplicate]
  29. How to check if a column exists before adding it to an existing table in PL/SQL?
  30. How to call Oracle MD5 hash function?

Leave a Comment