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

Chapter 14. Mutating Tables and Compound Triggers


In this Chapter, you will learn about

Image Mutating Tables

Image Compound Triggers


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 11gLab 14.2 covers compound triggers, which were introduced in Oracle 11g, and discusses how they can be used to resolve mutating table issues.

Lab 14.1: Mutating Tables


After this lab, you will be able to

Image Understand Mutating Tables

Image Resolve Mutating Tables Issues


What Is a Mutating Table?

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.


Watch Out!

A mutating table error is a runtime error. In other words, this error occurs not at the time of trigger creation (compilation), but rather when the trigger fires.


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.

Resolving Mutating Table Issues

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.


Did You Know?

These steps are used to resolve mutating table errors in versions of Oracle prior to 11g. Starting with Oracle 11g, compound triggers are used to resolve this error. Compound triggers are covered in Lab 14.2.


Consider the package specification shown in Listing 14.1.

Listing 14.1 INSTRUCTOR_ADM Package Specification

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

Lab 14.2: Compound Triggers


After this lab, you will be able to

Image Define a Compound Trigger

Image Use Compound Triggers to Resolve Mutating Table Issues


What Is a Compound Trigger?

A compound trigger allows you to combine different types of triggers into one trigger. Specifically, you are able to combine

Image A statement trigger that fires before the firing statement

Image A row trigger that fires before each row that the firing statement affects

Image A row trigger that fires after each row that the firing statement affects

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

Listing 14.2 General Syntax for Creating a Compound Trigger

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


Watch Out!

Compound triggers have several restrictions:

Image A compound trigger may be defined on a table or a view only.

Image A triggering event of a compound trigger is limited to the DML statements.

Image A compound trigger may not contain an autonomous transaction. In other words, its declaration portion cannot include PRAGMA AUTOTONOMOUS_TRANSACTION.

Image An exception that occurs in one executable section must be handled within that section. For example, if an exception occurs in the AFTER EACH ROW section, it cannot propagate to the AFTER STATEMENT section; rather, it must be handled in the AFTER EACH ROWsection.

Image References to :OLD and :NEW pseudocolumns cannot appear in the declaration, BEFORE STATEMENT, and AFTER STATEMENT sections.

Image The value of the :NEW pseudocolumn can be changed in the BEFORE EACH ROW section only.

Image The firing order of the compound and simple triggers is not guaranteed. In other words, the firing of the compound trigger may interleave with the firing of the simple triggers.

Image If a DML statement issued on a table that has a compound trigger defined on it fails (rolls back) due to an exception:

Image Variables declared in the compound trigger sections are reinitialized. In other words, any values assigned those variable are lost.

Image DML statements issued by the compound trigger are not rolled back.


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:

Image 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;

Image 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;

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


By the Way

The companion website provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.