ORA-04091: Table is Mutating, Trigger/Function may not see it

This is not a super-table nor a x-table (X-Men joke, this was awfull, I know… I’m sorry).

ORA-04091: Table "TABLE NAME" is Mutating, Trigger/Function may not see it
ORA-06512: em "TRC_INSERT_TABLE", line 14
ORA-04088: error during execution of trigger 'TRC_INSERT_TABLE'

Very interesting. But not hard to understand. The cause is that the trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

In other words, your trying to read a data the you are modifying. The obviously cause an inconsistency, the reason to this error. The data is “mutant”. But the error could be less annoying, right? Oracle and his jokes…

The solution is to rewrite the trigger to not use the table, or, in some situation, you can use an autonomous transaction, to turn it independent. It can be done using the clause PRAGMA AUTONOMOUS_TRANSACTION.

This FAQ can be useful to you: http://www.orafaq.com/wiki/Autonomous_transaction


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s