Lab 17.3 Mutating Table Issues

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 17.  Triggers


Lab Objective

After this Lab, you will be able to:

  • Understand Mutating Tables

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.

graphics/intfig03.gif 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:

  1. An existing trigger must be modified so that it records the instructor's ID, queries the INSTRUCTOR table, and records the instructor's name.

  2. 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.

  3. 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 Answers

a)

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.

  1. _____ True

  2. _____ 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.

  1. _____ True

  2. _____ False

3)

Which of the following is an SQL statement restriction?

  1. _____ No SQL statement may be issued against any table in the body of a trigger.

  2. _____ No SQL statement may be issued against the mutating table in the body of a trigger.

  3. _____ 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?

  1. _____ A table having a SELECT statement issued against it

  2. _____ A table having a trigger defined on it

  3. _____ A table being modified by a DML statement

5)

Which of the following is a constraining table?

  1. _____ A table having a SELECT statement issued against it

  2. _____ A table having a trigger defined on it

  3. _____ A table needing to be read from for a referential integrity constraint


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net