Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

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.

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.