|
|
Fix Oracle mutating trigger table errors
Oracle Database Tips by Donald Burleson |
A mutation table
is defined as a table that is changing. But in dealing with triggers, it is a
table that has the possibility of changing.
What this means to a trigger is that if the trigger reads a
table, it can not change the table that it read from. This does not impact
the exclusive use of :OLD and :NEW.
It says that if the trigger reads the table (such as
using a SELECT query), that changes (even using :NEW) will fail. This can
also happen when a trigger on a parent table causes an insert on a child
table referencing a foreign key.
Each new release of the Oracle database reduces the
impact of the mutating table error on triggers and they are much less of a
problem with Oracle9i and above. If a trigger does result in a mutating
table error, the only real option is to rewrite the trigger as a
statement-level trigger.
Mutating table errors only impact row level triggers.
But to use a statement level trigger, some data may need to be preserved
from each row, to be used by the statement level trigger. This data can be
stored in a PL/SQL collection or in a temporary table. A simple row level
trigger that causes a mutating table error can result in a very complicated
statement level trigger to achieve the needed result.
Here are some important items to remember about
triggers.
-
On insert triggers have no :OLD values.
-
On delete triggers have no :NEW values.
-
Triggers do not commit transactions. If a
transaction is rolled back, the data changed by the trigger is also
rolled back.
-
Commits, rollbacks and save points are not allowed
in the trigger body. A commit/rollback affects the entire transaction,
it is all or none.
-
Unhandled exceptions in the trigger will cause a
rollback of the entire transaction, not just the trigger.
-
If more than one trigger is defined on an event, the
order in which they fire is not defined. If the triggers must fire in
order, you must create one trigger that executes all the actions in the
required order.
-
A trigger can cause other events to execute triggers.
-
A trigger can not change a table that it has read
from. This is the mutating table error issue.
The fact that a trigger can cause other triggers to fire
is an important item to remember. A trigger that causes other database
events to execute triggers can cause the database crash. For example, the
database can capture server errors by defining a trigger on the database
server error event.
But if this trigger causes a server error, the database
will spin in a loop, with each firing of the trigger causing the error,
firing the trigger again, and again, and again. The only way to regain
control of the database is to disable the trigger.
Avoiding Mutating triggers
The insert to the child table caused the foreign key to
validate the data on the parent (which fired the trigger) causing the insert
of the child table to result in a mutating table error on the parent table.
The Oracle mutating trigger
error occurs when a trigger references the table that owns the trigger,
resulting in the "ORA-04091: table name is mutating, trigger/function may not
see it." message.
- Don't use triggers
- The best way to avoid the mutating table error is not to use triggers.
While the object-oriented Oracle provides "methods" that are associated with
tables, most savvy PL/SQL developers avoid triggers unless absolutely
necessary.
- Use an "after"
or "instead of" trigger
- If you must use a trigger, it's best to avoid the mutating table error
by using an "after" trigger, to avoid the currency issues associated with a
mutating table. For example, using a trigger ":after update on xxx",
the original update has completed and the table will not be mutating.
- Re-work the trigger
syntax -
Dr. Hall has some great notes on mutating table errors, and offers other
ways to avoid mutating tables with a combination of row-level
and statement-level triggers.
- Use autonomous transactions - You can avoid the mutating table
error by marking your trigger as an
autonomous transaction, making it independent from the table that calls
the procedure.
At the end of the day, the
mutating table error is usually the result of a poor application design and
mutating triggers should be avoided whenever possible.
Steve Callan has these notes on
the ORA-04091 error:
"Here is a problem many
developers run into: ORA-04091 table owner.table_name is mutating,
trigger/function may not see it. In many cases, the cause of this error is
due to code within a trigger that looks at or touches the data within the
table the trigger is being called or invoked from.
The reason Oracle raises
this error is related to one of Oracle's primary strengths as a relational
database management system. The particular strength in question here is that
of having a read consistent view of data.
It is worthwhile to note that this ORA-04091 error occurs not only in the
"pure" database development environment, but also in the Oracle tools type
of development environment such as Oracle SQL*Forms.
Another solution relies on
using an INSTEAD-OF trigger instead of the trigger you meant to use when you
received the error. Another solution is actually more of a preventative
measure, namely, using the right type of trigger for the task at hand.
Perhaps the greatest strength
or utility of an INSTEAD-OF trigger is its ability to update what would
normally appear to be non-updateable views. Simple views (pretty much based
on a single base table) generally are inherently updateable via DML
statements issued against the view.
However, when a view becomes more
complex (multiple tables or views used in various join conditions to create
the new single view), there is a good chance that many columns, as
referenced by the view, lose their "updateable-ness."
So, being the data
dictionary view/table name trivia wizard that you are, you know to query the
XXX_UPDATABLE_COLUMNS views, substituting USER, ALL or DBA for XXX as
applicable.
There are exceptions to this rule about views being inherently updateable.
The exceptions (or restrictions) include views that use aggregate functions;
group functions; use of the DISTINCT keyword; use of GROUP BY, CONNECT BY or
START WITH clauses; and use of some joins. In many cases, use of the
INSTEAD-OF trigger feature allows you to work around these restrictions.
INSTEAD-OF triggers are also useful for Forms developers because forms are
commonly based on views. The INSTEAD-OF trigger, being a "real" trigger, and
not a true form trigger, is stored on the server."
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|