Mutating Table Errors

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.

Foreign Key References

Tables that have a foreign key to the trigger’s associated table will generate a mutating table error if you attempt to read from or modify any of that table’s indexed columns.

The trigger in Listing 7.7 is designed to prevent a course’s location and time from being changed if at least one student is scheduled for the course. However, in order to do so, it must make a reference to the SCHEDULED_CLASSES table to determine which students are already enrolled in the course. This causes a mutating table error because the act of updating the primary key of the CLASSES table impacts the foreign key from the SCHEDULED_CLASSES table.

Listing 7.7 Referencing a foreign key column in another table.

CREATE OR REPLACE
TRIGGER CLASSES_BU
BEFORE UPDATE OF course_time, course_location
ON CLASSES

DECLARE
   nTotalStudents      number;
   xCLASS_HAS_STUDENTS  EXCEPTION;

BEGIN
   SELECT count (course_number)
   INTO   nTotalStudents
   FROM   SCHEDULED_CLASSES
   WHERE  course_number = :new.course_number;

   IF (nTotalStudents > :new.maximum_enrollment) THEN
      RAISE xCLASS_HAS_STUDENTS;
   END IF;
END CLASSES_BU
/

Cascading Deletes

A cascading delete occurs when a table with a foreign key reference to another table is given the ON DELETE CASCADE constraint. This constraint instructs Oracle to always delete the child rows of deleted parent rows. The use of this constraint is extremely common because it prevents application developers from writing code that leaves leftover children hanging around to muck up the works.

Unfortunately, this can cause problems with database triggers that fire on child tables due to a delete on parent tables. If the DELETE trigger attempts to reference the parent table, a mutating table error occurs. The same situation occurs if a DELETE trigger on a parent attempts to reference the child table.

This type of mutating table error occurs quite frequently in both statement-level and row-level database triggers, and there is no acceptable workaround using either type of trigger. The only real option is to not use the ON DELETE CASCADE constraint.