"Mutating Table" Exception in Oracle

One common problem a new trigger writer will frequently run into is the "mutating table" exception. This exception is caused when the trigger is trying to query the table that is being changed or is possible to be changed. Here is a very simple example to demonstrate "mutating table" exception. (The queries are in the file "Mutating_Table_Exception.txt") Assume you have three tables, Student(ID, name) Course(ID, name) Registration(studentID, courseID, grade) with studentID and courseID referencing table Student and Course.  The restriction we want our trigger to be built on is that each course has a maximum capacity of 10 students.  It's common for us to build a trigger like below:  CREATE OR REPLACE TRIGGER Max_Course_Capacity  BEFORE INSERT OR UPDATE OF courseID ON Registration  FOR EACH ROW  DECLARE  num_of_student_registered NUMBER;  BEGIN  SELECT COUNT(*) INTO num_of_student_registered  FROM Registration  WHERE courseID=:NEW.courseID;   IF num_of_student_registered >=10 THEN  raise_application_error(-20000, 'Cannot perform this query: The number of students  registered in a certain course cannot exceed 10 ');  END IF;  END;  / This trigger works fine with insert operation. But for update, the "mutating table" exception will be raised.  I tried two approaches trying to work around "mutating table" exception, and one works and the other does not. They are listed as below.  Approach 1 (Does not work):  Split the original trigger into two, one for inserting and one for updating, and change the BEFORE key word to AFTER for the updating trigger. If the newly updated row makes the number of students of one course greater than the capacity, then the trigger will use  a SQL query to change that newly updated row back to its original value. The code are shown below:  CREATE OR REPLACE TRIGGER Max_Course_Capacity_1  BEFORE INSERT ON Registration  FOR EACH ROW  DECLARE  num_of_student_registered NUMBER;  BEGIN  SELECT COUNT(*) INTO num_of_student_registered  FROM Registration  WHERE courseID=:NEW.courseID;   IF num_of_student_registered >=10 THEN  raise_application_error(-20000, 'Cannot perform this query: The number of students  registered in a certain course cannot exceed 10 ');  END IF;  END;  / CREATE OR REPLACE TRIGGER Max_Course_Capacity_2  AFTER UPDATE OF courseID ON Registration  FOR EACH ROW  DECLARE  num_of_student_registered NUMBER;  BEGIN  SELECT COUNT(*) INTO num_of_student_registered  FROM Registration  WHERE courseID=:NEW.courseID;   IF num_of_student_registered >=10 THEN  raise_application_error(-20000, 'Cannot perform this query: The number of students  registered in a certain course cannot exceed 10 ');  --change the newly updated row back to its original value UPDATE Registration SET courseID=:OLD.courseID WHERE studentID=:NEW.studentID AND courseID=:NEW.courseID; END IF;  END;  / Approach 2 (Works) This is an easy way to make "mutating table" exception disappear. We need to use the PRAGMA EXCEPTION_INIT to bind the exception we defined to a particular error number, -4091 in this case. Below is the trigger body. CREATE OR REPLACE TRIGGER Max_Course_Capacity  BEFORE INSERT OR UPDATE OF courseID ON Registration  FOR EACH ROW  DECLARE  num_of_student_registered NUMBER; mutating_table EXCEPTION;  PRAGMA EXCEPTION_INIT(mutating_table, -4091); BEGIN  SELECT COUNT(*) INTO num_of_student_registered  FROM Registration  WHERE courseID=:NEW.courseID;   IF num_of_student_registered >=10 THEN  raise_application_error(-20000, 'Cannot perform this query: The number of students  registered in a certain course cannot exceed 10 ');  END IF;  EXCEPTION WHEN mutating_table THEN raise_application_error(-20000, 'Cannot perform this query: The number of students  registered in a certain course cannot exceed 10 '); END;  / When a "mutating table" exception is raised, we use the function "raise_application_error" to indicate that this query is illegal.  Conclusion This simple example didn't show all the aspects of "mutating table" exception. For example, "mutating table" exception will not occur if only a single record is inserted in the table, but it will occur for multiple row insertion. In addition, "mutating table" exception is not only encountered during queries, but also for insert, updates and deletes present in the trigger. The basic reason for "mutating" exception is the way Oracle manages a read consistent view of data. The "mutating table" exception is usually the result of a poor application design and mutating triggers should be avoided whenever possible. Thus the best way to work around "mutating table" exception in our example is to add a field to record the number of students enrolled in each course into the Course table. When a insertion of update is fired on table Registration, instead of querying table Registration, the trigger will query table Course, thus avoid "mutating table" exception.