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