Featured Database Articles

The Trigger-Happy DBA - Part 2

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 "look and touch" refers to using select (the look) and DML
statements (the touch). In other words, you need to take your DML elsewhere.
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 error occurs not only in the "pure" database development
environment (CREATE or REPLACE trigger trigger_name... in a script or SQL*Plus
session), but also in the Oracle tools type of development environment such as
Oracle Forms. An Oracle form relies on triggers for a great many things,
ranging from capturing user interaction with the form (when-button-pressed) to
performing transaction processing (on-commit). A forms trigger may do nothing
more than change the focus to a new item or show a new canvas. What a form
trigger can do, and has in common with the "pure" development type of
trigger, is generate the ORA-04091 mutating table error.

One common solution
to avoid the mutating table error is to use three other triggers. Tom Kyte,
author of Expert One-on-One Oracle and Effective Oracle by Design,
two of the very best books on Oracle, provides an excellent example of this
technique at http://asktom.oracle.com/tkyte/Mutate/index.html
(part of the Ask Tom series at www.oracle.com).
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.

Here is a simple
example of where a trigger can generate the mutating table error. The hapless
Oracle user named Scott wants to generate a statement telling him how many
employees are left after an employee record is deleted. This code for this example
comes from Oracle's Application Developer's Guide.

Any hint so far
that there may be a problem with the trigger? Not with the "Trigger
created" feedback Oracle
provides. Looks like no errors and that the trigger should fire when the
triggering condition (after delete on the Emp_tab table) occurs.

Here is a DML
statement that will trigger the ORA-04091 error:

SQL> DELETE FROM Emp_tab WHERE Empno = 7499;
DELETE FROM Emp_tab WHERE Empno = 7499
*
ERROR at line 1:
ORA-04091: table SCOTT.EMP_TAB is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.EMP_COUNT", line 4
ORA-04088: error during execution of trigger 'SCOTT.EMP_COUNT'

Let's modify the
trigger code just a bit, and remove the FOR EACH ROW clause. If 'we are not "doing"
each row, the trigger becomes a statement-level trigger.

Note that the
trigger successfully fired with this one modification. But was it really a
modification or just a better design and use of a trigger? As stated in the
previous article, triggers can act on each row or act at the statement level.
In Scott's case, what he really needed was a statement-level trigger, not a
row-level trigger. Mastering this concept alone - knowing whether to base the
trigger on the statement or on rows - can prevent many instances of the
mutating trigger error.

Issuing an update
statement - does the trigger allow the ALTER_DATE to be updated?

SQL> update trigger_example_table
2 set ename = 'ALLEN_TRIG'
3 where empno = 7499;
update trigger_example_table
*
ERROR at line 1:
ORA-04091: table SCOTT.TRIGGER_EXAMPLE_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRIG_TRIGGER_EXAMPLE_TABLE", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG_TRIGGER_EXAMPLE_TABLE'

No, because it is
the same problem as before (touching a table that is being updated). This
example just reinforces the idea that the mutating trigger error still occurs
on a table based on other tables (which is still just a table as far as Oracle
is concerned). Another name for the concept of presenting data based on a
combination (i.e., a join) other tables? Straight out of the Concepts Guide: "A
view is a tailored presentation of the data contained in one or more
tables or other views." The
Application Developer's Guide (yes, this is a plug for Oracle's documentation)
presents a good example of how to construct an INSTEAD-OF trigger. You can
copy the sample code shown in the guide and experiment with using various DML
statements against the view.

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. This may require coordination between the DBA
and developer, which, of course, always happens in complete harmony (NOT! - but
that is a separate issue).