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

 

 

   
 

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.

  
 

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