Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Dec 26, 2003

The Trigger-Happy DBA - Part 2

By Steve Callan

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date