In Chapter 13, you explored the concept of triggers. You learned about usage of triggers in the database, events that cause triggers to fire, and different types of triggers. In this chapter, you will continue exploring triggers. You will learn about mutating table issues and discover how triggers can be used to resolve these issues.
Lab 14.1 describes mutating tables and explains how to resolve issues associated with them in Oracle database prior to the version 11g. Lab 14.2 covers compound triggers, which were introduced in Oracle 11g, and discusses how they can be used to resolve mutating table issues.
A table against which a DML statement is issued is called a mutating table. For a trigger, the mutating table is the one on which the 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 this restriction applies to row-level triggers.
Consider the following example of a trigger causing a mutating table error.
For Example ch14_1a.sql
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 is teaching too many sections.
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:
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 states 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, therefore, is mutating.
To correct mutating table error described earlier, the following steps must be taken when using an Oracle version prior to 11g:
1. To record the instructor’s ID and name as described in the preceding example, two global variables must be declared with the help of a PL/SQL package. You will learn about global variables and packages in Chapter 21.
2. An existing trigger must be modified so that it records the instructor’s ID, queries the INSTRUCTOR
table, and records the instructor’s name.
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 that number is equal to or greater than 10.
Consider the package specification shown in Listing 14.1.
CREATE OR REPLACE PACKAGE instructor_adm
AS
g_instructor_id instructor.instructor_id%TYPE;
g_instructor_name varchar2(50);
END;
This package specification contains declarations for two global variables, g_instructor_id
and g_instructor_name
. Note that the CREATE OR REPLACE
clause is similar to the clause used for a trigger. (Packages are covered in detail in Chapter 21.)
Next, the existing trigger SECTION_BIU
is modified as follows:
For Example ch14_1b.sql
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
-- Assign new instructor ID to the global variable
instructor_adm.g_instructor_id := :NEW.INSTRUCTOR_ID;
SELECT first_name||' '||last_name
INTO instructor_adm.g_instructor_name
FROM instructor
WHERE instructor_id = instructor_adm.g_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 g_instructor_id
and g_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—a convention called dot notation.
Finally, a new statement-level trigger is created on the SECTION
table:
For Example ch14_2a.sql
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.g_instructor_id;
-- check if the current instructor is overbooked
IF v_total >= 10
THEN
RAISE_APPLICATION_ERROR
(-20000, 'Instructor, '||instructor_adm.g_instructor_name||', is overbooked'),
END IF;
END;
This trigger fires after an INSERT
or UPDATE
statement is issued against the SECTION
table. Because this is a statement-level trigger, the FOR EACH ROW
clause is omitted from the trigger header. This trigger checks the number of courses that are taught by a particular instructor and raises an error if that number is equal to or greater than 10. This is accomplished with the help of two global variables, g_instructor_id
and g_instructor_name
. As mentioned earlier, these variables are populated by the SECTION_BIU
trigger that fires before an INSERT
or 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;
produces ORA-20000 error as expected
ORA-20000: Instructor, Fernand Hanks, is overbooked
ORA-06512: at "STUDENT.SECTION_AIU", line 12
ORA-04088: error during execution of trigger 'STUDENT.SECTION_AIU'
This error is generated by the trigger SECTION_AIU
and does not contain any message about a mutating table.
Now consider a similar UPDATE
statement for a different instructor ID that does not cause any errors:
UPDATE section
SET instructor_id = 110
WHERE section_id = 80;
1 row updated.
A compound trigger allows you to combine different types of triggers into one trigger. Specifically, you are able to combine
A statement trigger that fires before the firing statement
A row trigger that fires before each row that the firing statement affects
A row trigger that fires after each row that the firing statement affects
A statement trigger that fires after the firing statement
For example, you can create a compound trigger on the STUDENT
table with portions of code that would fire once before the insert, before the insert for each affected row, after the insert for each affected row, and once after the insert.
The structure of a compound trigger is shown in Listing 14.2.
CREATE [OR REPLACE] TRIGGER trigger_name
triggering_event ON table_name
COMPOUND TRIGGER
Declaration Statements
BEFORE STATEMENT IS
BEGIN
Executable statements
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
Executable statements
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
Executable statements
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
Executable statements
END AFTER STATEMENT;
END;
First you specify the trigger header that includes the CREATE OR REPLACE
clause, the triggering event, the table name for which the trigger is defined, and the COMPOUND TRIGGER
clause that denotes that this is a compound trigger. Note the omission of the BEFORE
or AFTER
clause in the header of the compound trigger.
Next, you specify a declaration section that is common to all executable sections. In other words, any variable declared in this section can be referenced in any of the executable sections.
Finally, you specify the executable sections that fire at different timing points. Each of these sections is optional. Thus, if no action takes place after the firing statement, there is no AFTER STATEMENT
section.
Consider the following example of the compound trigger on the STUDENT
table that has BEFORE STATEMENT
and BEFORE EACH ROW
sections only.
For Example ch14_3a.sql
CREATE OR REPLACE TRIGGER student_compound
FOR INSERT ON STUDENT
COMPOUND TRIGGER
-- Declaration section
v_day VARCHAR2(10);
BEFORE STATEMENT IS
BEGIN
v_day := RTRIM(TO_CHAR(SYSDATE, 'DAY'));
IF v_day LIKE ('S%')
THEN
RAISE_APPLICATION_ERROR
(-20000, 'A table cannot be modified during off hours'),
END IF;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
:NEW.student_id := STUDENT_ID_SEQ.NEXTVAL;
:NEW.created_by := USER;
:NEW.created_date := SYSDATE;
:NEW.modified_by := USER;
:NEW.modified_date := SYSDATE;
END BEFORE EACH ROW;
END;
This trigger has a declaration section and two executable sections only. Each of the executable sections is optional and is specified only because there is an action associated with it.
First, the declaration section contains the declaration of a single variable used in the BEFORE STATEMENT
section. Second, the BEFORE STATEMENT
section initializes the variable and contains an IF
statement that prevents modification of the STUDENT
table during off hours. This section fires once before an INSERT
statement. Next, the BEFORE EACH ROW
section initializes some of the columns of the STUDENT
table to their default values.
Note that all references to the :NEW
pseudorecord are placed in the BEFORE EACH ROW
section of the trigger, as this section is available in the row-level section only. In fact, if you attempt to assign values to any of the members of the :NEW
pseudorecord in the BEFORE STATEMENT
section, the trigger compiles with the error message similar to one shown here:
PLS-00363: expression 'NEW.CREATED_BY' cannot be used as an assignment target
PLS-00679: trigger binds not allowed in before/after statement section
PL/SQL: Statement ignored
In Lab 14.1, you learned about mutating table issues and saw how they can be resolved in Oracle versions prior to 11g. In this lab, you will learn how to resolve mutating table issues by means of compound triggers, which were introduced in Oracle 11g. Recall the example of the trigger on the SECTION
table from Lab 14.1 that caused a mutating table error and the steps you took to resolve this error, as shown in Listing 14.3.
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;
To correct this problem, you took the following steps:
You created a package where you declared two global variables.
CREATE OR REPLACE PACKAGE instructor_adm
AS g_instructor_id instructor.instructor_id%TYPE;
g_instructor_name varchar2(50);
END;
You modified the existing trigger to record the instructor’s ID and name.
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.g_instructor_id := :NEW.INSTRUCTOR_ID;
SELECT first_name||' '||last_name
INTO instructor_adm.g_instructor_name
FROM instructor
WHERE instructor_id = instructor_adm.g_instructor_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RAISE_APPLICATION_ERROR (-20001, 'This is not a valid instructor'),
END;
END IF;
END;
You created a new statement trigger that fires after the INSERT
or UPDATE
statement has been issued.
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;
Now consider a compound trigger on the SECTION
table that fires with an INSERT
or UPDATE
operation.
For Example ch14_4a.sql
CREATE OR REPLACE TRIGGER section_compound
FOR INSERT OR UPDATE ON SECTION
COMPOUND TRIGGER
-- Declaration Section
v_instructor_id INSTRUCTOR.INSTRUCTOR_ID%TYPE;
v_instructor_name VARCHAR2(50);
v_total INTEGER;
BEFORE EACH ROW IS
BEGIN
IF :NEW.instructor_id IS NOT NULL
THEN
BEGIN
v_instructor_id := :NEW.instructor_id;
SELECT first_name||' '||last_name
INTO v_instructor_name
FROM instructor
WHERE instructor_id = v_instructor_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR
(-20001, 'This is not a valid instructor'),
END;
END IF;
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
SELECT COUNT(*)
INTO v_total
FROM section
WHERE instructor_id = v_instructor_id;
-- check if the current instructor is overbooked
IF v_total >= 10
THEN
RAISE_APPLICATION_ERROR
(-20000, 'Instructor, '||v_instructor_name||', is overbooked'),
END IF;
END AFTER STATEMENT;
END;
In this trigger, you declare three variables, two of which were previously declared in the package. Next, you place statements from two individual triggers into two corresponding sections of a compound trigger.
By using this compound trigger, you were able to resolve a mutating table issue with a simpler approach. You eliminated the need for the package that was used as a link between two triggers that fired at different times in a transaction.
Note that the UPDATE
statement used earlier
UPDATE section
SET instructor_id = 101
WHERE section_id = 80;
still causes an ORA-20000 error:
ORA-20000: Instructor, Fernand Hanks, is overbooked
ORA-06512: at "STUDENT.SECTION_COMPOUND", line 38
ORA-04088: error during execution of trigger 'STUDENT.SECTION_COMPOUND'
This error is generated by the trigger SECTION_COMPOUND
and does not contain any message about a mutating table.
In Chapter 13, you began exploring various types of triggers supported in PL/SQL. In this chapter, you continued this exploration and learned about mutating table issues. You learned how such issues were resolved in Oracle versions prior to 11g. Finally, you learned about compound triggers, which were introduced in Oracle 11g, and saw how these types of triggers can be used to resolve mutating table issues.