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.

Here is the data
before the delete statement is issued:


SQL> select empno, ename
2 from emp_tab; — a copy of the scott.emp table

EMPNO ENAME
———- ———-
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER

14 rows selected.

Here is the trigger
code:


CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(‘ There are now ‘|| n ||
’ employees.’);
END;

In a SQL*Plus
session, here is what the coding looks like (same as above, but without the
feedback statement):


SQL> CREATE OR REPLACE TRIGGER Emp_count
2 AFTER DELETE ON Emp_tab
3 FOR EACH ROW
4 DECLARE
5 n INTEGER;
6 BEGIN
7 SELECT COUNT(*) INTO n FROM Emp_tab;
8 DBMS_OUTPUT.PUT_LINE(‘ There are now ‘|| n ||
9 ‘ employees.’);
10 END;
11 /

Trigger created.

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.


SQL> CREATE OR REPLACE TRIGGER Emp_count
2 AFTER DELETE ON Emp_tab
3 — FOR EACH ROW
4 DECLARE
5 n INTEGER;
6 BEGIN
7 SELECT COUNT(*) INTO n FROM Emp_tab;
8 DBMS_OUTPUT.PUT_LINE(‘ There are now ‘|| n ||
9 ‘ employees.’);
10 END;
11 /

Trigger created.

SQL> DELETE FROM Emp_tab WHERE Empno = 7499;
There are now 13 employees.

1 row deleted.

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.

Suppose Scott has a
table based on two other tables:


SQL> create table trigger_example_table
2 as
3 select empno, ename, a.deptno, dname, alter_date
4 from emp a, dept2 b
5 where a.deptno = b.deptno;

Table created.
SQL> select * from trigger_example_table;

EMPNO ENAME DEPTNO DNAME ALTER_DAT
———- ———- ———- ————– ———
7782 CLARK 10 ACCOUNTING 09-DEC-03
7839 KING 10 ACCOUNTING 09-DEC-03
7934 MILLER 10 ACCOUNTING 09-DEC-03
7369 SMITH 20 RESEARCH 09-DEC-03
7876 ADAMS 20 RESEARCH 09-DEC-03
7902 FORD 20 RESEARCH 09-DEC-03
7788 SCOTT 20 RESEARCH 09-DEC-03
7566 JONES 20 RESEARCH 09-DEC-03
7499 ALLEN2 30 SALES 09-DEC-03
7698 BLAKE 30 SALES 09-DEC-03
7654 MARTIN 30 SALES 09-DEC-03
7900 JAMES 30 SALES 09-DEC-03
7844 TURNER 30 SALES 09-DEC-03
7521 WARD 30 SALES 09-DEC-03

14 rows selected.

And, for whatever
reason, whenever an update is made on someone, the ALTER_DATE column is updated
to reflect the current date:


SQL> create or replace trigger
2 trig_trigger_example_table
3 after update on trigger_example_table
4 for each row
5 begin
6 update trigger_example_table
7 set alter_date = sysdate;
8 end;
9 /

Trigger created.

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

»


See All Articles by Columnist
Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles