Lab 17.3 Mutating Table Issues
A table having a DML statement issued against it is called mutating table . For a trigger, it is the table on which this trigger is defined. If a trigger tries to read or modify such a table, it causes a mutating table error. As a result, a SQL statement issued in the body of the trigger may not read or modify a mutating table. Note that prior to Oracle 8i, there was another restriction on the SQL statement issued in the body of a trigger that caused a different type of error called a constraining table error. A table read from for a referential integrity constraint is called a constraining table . So an SQL statement issued in the body of a trigger could not modify the columns of a constraining table having primary, foreign, or unique constraints defined of them. However, staring with Oracle 8i, there is no such restriction. Consider the following example of a trigger causing a mutating table error. It is important for you to note that a mutating table error is a runtime error. CREATE OR REPLACE TRIGGER section_biu BEFORE INSERT OR UPDATE ON section FOR EACH ROW DECLARE v_total NUMBER; v_name VARCHAR2(30); BEGIN SELECT COUNT(*) INTO v_total FROM section -- SECTION is MUTATING WHERE instructor_id = :NEW.INSTRUCTOR_ID; -- check if the current instructor is overbooked IF v_total >= 10 THEN SELECT first_name' 'last_name INTO v_name FROM instructor WHERE instructor_id = :NEW.instructor_id; RAISE_APPLICATION_ERROR (-20000, 'Instructor, ' v_name', is overbooked'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'This is not a valid instructor'); END; This trigger fires before an INSERT or UPDATE statement is issued on the SECTION table. The trigger checks whether the specified instructor is teaching too many sections. If the number of sections taught by an instructor is equal to or greater than 10, the trigger issues an error message stating that this instructor teaches too much. Now, consider the following UPDATE statement issued against the SECTION table: UPDATE section SET instructor_id = 101 WHERE section_id = 80; When this UPDATE statement is issued against the SECTION table, the following error message is displayed: UPDATE section * ERROR at line 1: ORA-04091: table STUDENT.SECTION is mutating, trigger/function may not see it ORA-06512: at "STUDENT.SECTION_BIU", line 5 ORA-04088: error during execution of trigger 'STUDENT.SECTION_BIU' Notice that the error message is stating that the SECTION table is mutating and the trigger may not see it. This error message is generated because there is a SELECT INTO statement, SELECT COUNT(*) INTO v_total FROM section WHERE instructor_id = :NEW.INSTRUCTOR_ID; issued against the SECTION table that is being modified and is therefore mutating. In order to correct this problem, the following steps must be accomplished:
Consider the following package: CREATE OR REPLACE PACKAGE instructor_adm AS v_instructor_id instructor.instructor_id%TYPE; v_instructor_name varchar2(50); END; Notice that this package does not have a package body and is used to declare two global variables only, v_instructor_id and v_instructor_name . Next, the existing trigger SECTION_BIU is modified as follows : CREATE OR REPLACE TRIGGER section_biu BEFORE INSERT OR UPDATE ON section FOR EACH ROW BEGIN IF :NEW.INSTRUCTOR_ID IS NOT NULL THEN BEGIN instructor_adm.v_instructor_id := :NEW.INSTRUCTOR_ID; SELECT first_name' 'last_name INTO instructor_adm.v_instructor_name FROM instructor WHERE instructor_id = instructor_adm.v_instructor_id; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'This is not a valid instructor'); END; END IF; END; In this version of the trigger, the global variables v_instructor_id and v_instructor_name are initialized if the incoming value of the instructor's ID is not null. Notice that the variable names are prefixed by the package name. This type of notation is called dot notation . Finally, a new trigger is created on the SECTION table as follows: CREATE OR REPLACE TRIGGER section_aiu AFTER INSERT OR UPDATE ON section DECLARE v_total INTEGER; BEGIN SELECT COUNT(*) INTO v_total FROM section WHERE instructor_id = instructor_adm.v_instructor_id; -- check if the current instructor is overbooked IF v_total >= 10 THEN RAISE_APPLICATION_ERROR (-20000, 'Instructor, ' instructor_adm.v_instructor_name ', is overbooked'); END IF; END; This trigger checks the number of courses that are taught by a particular instructor and raises an error if the number is equal to or greater than 10. This is accomplished with the help of two global variables, v_instructor_id and v_instructor_name . As mentioned earlier, these variables are populated by the SECTION_BIU trigger that fires before the UPDATE statement is issued against the SECTION table. As a result, the UPDATE statement used earlier UPDATE section SET instructor_id = 101 WHERE section_id = 80; causes a different error UPDATE section * ERROR at line 1: ORA-20000: Instructor, Fernand Hanks, is overbooked ORA-06512: at "STUDENT.SECTION_AIU", line 11 ORA-04088: error during execution of trigger 'STUDENT.SECTION_AIU' Notice that this error has been generated by the trigger SECTION_AIU and does not contain any message about a mutating table. Next, consider a similar UPDATE statement for a different instructor ID that does not cause any errors: UPDATE section SET instructor_id = 109 WHERE section_id = 80; 1 row updated. |
Understand Mutating Tables 
![Oracle PL[s]SQL by Example](/icons/blank_book.jpg "Oracle PL[s]SQL by Example"){kind=icon}