Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Mutating Table Errors
Oracle Tips by Burleson
 

A mutating table error is the most common runtime error for database triggers. This type of error occurs when a row-level database trigger (or an object called from within the trigger) violates one of the following several rules for SQL statements:

  • The triggerís associated table canít be queried or modified from the trigger.

  • Primary key, unique, and foreign key columns in other tables may not be modified or queried. Columns that are not within one of these indexes may be altered or queried.

  • Data in any other tables affected by the triggering DML statement, specifically tables altered because of an ON DELETE CASCADE constraint, cannot be read or altered. (This rule applies to statement-level triggers as well.)

Following, youíll find an example of triggers that attempt to violate these rules. Youíll also find a way to write triggers that donít break these rules.

Referencing a Triggerís Associated Table

In its simplest incarnation, this error is a reference to the triggerís associated table by the trigger itself. The table is in a state of flux because an operation is in progress, so the trigger canít read from the table. Consider the trigger in Listing 7.5, which attempts to read from the SCHEDULED_CLASSES table when a row is created or modified.

Listing 7.5 A trigger that causes a mutating table error.

CREATE OR REPLACE
TRIGGER SCHEDULED_CLASSES_ARU
AFTER UPDATE OF course_time, course_location
ON SCHEDULED CLASSES
FOR EACH ROW
 
DECLARE
   xLOCATION_CONFLICT   EXCEPTION;
 
BEGIN
   SELECT 1
   INTO   nClassConflicts
   FROM   SCHEDULED_CLASSES
   WHERE  course_number   = :new.course_number
   AND    course_location = :new.course_location
   AND    course_time     = :new.course_time;
 
   RAISE xLOCATION_CONFLICT;
 
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        NULL;
END SCHEDULED_CLASSES_ARU;
/

This trigger is designed to verify that there are no scheduling conflicts for the class based on time or location. The trigger fails because it must reference its own associated table, SCHEDULED_CLASSES, to determine if there is a conflict.

An Exception to This Rule

The before row INSERT trigger and the after row INSERT trigger are the only types of triggers that can read from or modify a triggerís base table. This may only be done when the INSERT statement on the triggerís associated table generated only one new row of data. Consider the trigger in Listing 7.6.

Listing 7.6 A trigger that can read from its associated table.

CREATE OR REPLACE
TRIGGER CLASSES_ARI
AFTER INSERT
ON CLASSES
FOR EACH ROW
 
DECLARE
   xLOCATION_CONFLICT   EXCEPTION;
 
BEGIN
   SELECT 1
   INTO   nClassConflicts
   FROM   CLASSES
   WHERE  course_number   = :new.course_number
   AND    course_location = :new.course_location
   AND    course_time     = :new.course_time;
 
   RAISE xLOCATION_CONFLICT;
 
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        NULL;
END CLASSES_ARI;
/

This trigger will not cause a mutating table error as long as only a single class is inserted into the CLASSES table at one time. Itís possible to insert multiple rows of data using a multiple INSERT statement like this one:

INSERT
INTO   CLASSES
SELECT *
FROM   IMPORTED_CLASSES;

Even if this statement returns only one row, itís still a multiple INSERT statement, and a mutating table error will result.

              
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter