The Trigger-Happy DBA - Part 2
December 26, 2003
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).