Oracle PL/SQL by Example, Fifth Edition (2015)

---

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 STATEMENTsection, 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

Resolving Mutating Table Issues with Compound Triggers

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.

Listing 14.3 Preventing Mutating Table Issue Prior to Oracle 11g

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.

Summary

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.