 |
|
Foreign Key References
Oracle Tips by
Burleson
|
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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
