Lab Objective After this Lab, you will be able to: 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. FOR EXAMPLE 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: -
An existing trigger must be modified so that it records the instructor's ID, queries the INSTRUCTOR table, and records the instructor's name. -
In order to record the instructor's ID and name as described in the preceding step, two global variables must be declared with the help of a package. -
A new trigger must be created on the SECTION table. This trigger should be a statement-level trigger that fires after the INSERT or UPDATE statement has been issued. It will check the number of courses that are taught by a particular instructor and will raise an error if the number is equal to or greater than 10. 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. Lab 17.3 Exercises 17.3.1 Understand Mutating Tables In this exercise, you modify a trigger that causes a mutating table error when an INSERT statement is issued against the ENROLLMENT table. Create the following trigger: -- ch17_4a.sql, version 1.0 CREATE OR REPLACE TRIGGER enrollment_biu BEFORE INSERT OR UPDATE ON enrollment FOR EACH ROW DECLARE v_total NUMBER; v_name VARCHAR2(30); BEGIN SELECT COUNT(*) INTO v_total FROM enrollment WHERE student_id = :NEW.STUDENT_ID; -- check if the current student is enrolled into too -- many courses IF v_total >= 3 THEN SELECT first_name||' '||last_name INTO v_name FROM student WHERE student_id = :NEW.STUDENT_ID; RAISE_APPLICATION_ERROR (-20000, 'Student, '||v_name|| ', is registered for 3 courses already'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'This is not a valid student'); END; Issue the following INSERT and UPDATE statements: INSERT INTO ENROLLMENT (student_id, section_id, enroll_date, created_by, created_date, modified_by, modified_date) VALUES (184, 98, SYSDATE, USER, SYSDATE, USER, SYSDATE); INSERT INTO ENROLLMENT (student_id, section_id, enroll_date, created_by, created_date, modified_by, modified_date) VALUES (407, 98, SYSDATE, USER, SYSDATE, USER, SYSDATE); UPDATE ENROLLMENT SET student_id = 404 WHERE student_id = 407; Answer the following questions: a) | What output is produced after the INSERT and UPDATE statements are issued? | b) | Explain why two of the statements did not succeed. | c) | Modify the trigger so that it does not cause a mutating table error when an UPDATE statement is issued against the ENROLLMENT table. | Lab 17.3 Exercise Answers This section gives you some suggested answers to the questions in Lab 17.3, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with. 17.3.1 Answersa) | What output is produced after the INSERT and UPDATE statements are issued? | A1: | Answer: Your output should look as follows: INSERT INTO ENROLLMENT (student_id, section_id, enroll_date, created_by, created_date, modified_by, modified_date) VALUES (184, 98, SYSDATE, USER, SYSDATE, USER, SYSDATE); INSERT INTO ENROLLMENT * ERROR at line 1: ORA-20000: Student, Salewa Zuckerberg, is registered for 3 courses already ORA-06512: at "STUDENT.ENROLLMENT_BIU", line 17 ORA-04088: error during execution of trigger 'STUDENT.ENROLLMENT_BIU' INSERT INTO ENROLLMENT (student_id, section_id, enroll_date, created_by, created_date, modified_by, modified_date) VALUES (407, 98, SYSDATE, USER, SYSDATE, USER, SYSDATE); 1 row created. UPDATE enrollment SET student_id = 404 WHERE student_id = 407; UPDATE enrollment * ERROR at line 1: ORA-04091: table STUDENT.ENROLLMENT is mutating, trigger/function may not see it ORA-06512: at "STUDENT.ENROLLMENT_BIU", line 5 ORA-04088: error during execution of trigger 'STUDENT. ENROLLMENT_BIU' | b) | Explain why two of the statements did not succeed. | A2: | Answer: The INSERT statement does not succeed because it tries to create a record in the ENROLLMENT table for a student that is already registered for three courses. The IF statement -- check if the current student is enrolled into too many -- courses IF v_total >= 3 THEN SELECT first_name||' '||last_name INTO v_name FROM student WHERE student_id = :NEW.STUDENT_ID; RAISE_APPLICATION_ERROR (-20000, 'Student, '||v_name|| ', is registered for 3 courses already'); END IF; in the body of the trigger evaluates to TRUE, and as a result the RAISE_ APPLICATION_ERROR statement raises a user-defined exception. The UPDATE statement does not succeed, because a trigger tries to read data from the mutating table. The SELECT INTO SELECT COUNT(*) INTO v_total FROM enrollment WHERE student_id = :NEW.STUDENT_ID; statement is issued against the ENROLLMENT table that is being modified and therefore is mutating. | c) | Modify the trigger so that it does not cause a mutating table error when an UPDATE statement is issued against the ENROLLMENT table. | A3: | Answer: First, create a package to hold the student's ID and name as follows: CREATE OR REPLACE PACKAGE student_adm AS v_student_id student.student_id%TYPE; v_student_name varchar2(50); END; Next, the existing trigger, SECTION_BIU, is modified as follows: CREATE OR REPLACE TRIGGER enrollment_biu BEFORE INSERT OR UPDATE ON enrollment FOR EACH ROW BEGIN IF :NEW.STUDENT_ID IS NOT NULL THEN BEGIN student_adm.v_student_id := :NEW.STUDENT_ID; SELECT first_name||' '||last_name INTO student_adm.v_student_name FROM student WHERE student_id = student_adm.v_student_id; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'This is not a valid student'); END; END IF; END; Finally, create a new statement-level trigger on the ENROLLMENT table as follows: CREATE OR REPLACE TRIGGER enrollment_aiu AFTER INSERT OR UPDATE ON enrollment DECLARE v_total INTEGER; BEGIN SELECT COUNT(*) INTO v_total FROM enrollment WHERE student_id = student_adm.v_student_id; -- check if the current student is enrolled into too -- many courses IF v_total >= 3 THEN RAISE_APPLICATION_ERROR (-20000, 'Student, '|| student_adm.v_student_name|| ', is registered for 3 courses already '); END IF; END; Once the package and two triggers are created, the UPDATE statement does not cause a mutating table error. | Lab 17.3 Self-Review Questions In order to test your progress, you should be able to answer the following questions. Answers appear in Appendix A, Section 17.3. 1) | You are allowed to issue any SQL statement in the body of a trigger. _____ True _____ False
| 2) | It is always permissible to issue a SELECT statement in the body of a trigger. However, it is not always permissible to issue an INSERT, UPDATE, or DELETE statement. _____ True _____ False
| 3) | Which of the following is an SQL statement restriction? _____ No SQL statement may be issued against any table in the body of a trigger. _____ No SQL statement may be issued against the mutating table in the body of a trigger. _____ A SQL statement can be issued only against the mutating table in the body of a trigger.
| 4) | Which of the following is a mutating table? _____ A table having a SELECT statement issued against it _____ A table having a trigger defined on it _____ A table being modified by a DML statement
| 5) | Which of the following is a constraining table? _____ A table having a SELECT statement issued against it _____ A table having a trigger defined on it _____ A table needing to be read from for a referential integrity constraint
| |