Previous Table of Contents Next


Typical Uses For Triggers

Database triggers are an ideal tool for enforcing business rules that are directly related to data. There are many common uses of database triggers, including:

  Enforcing business rules that cannot be enforced with check constraints
  Updating data in other tables
  Marking rows for processing or rows that have been processed
  Signaling that an event has occurred

Each of these uses is described in the following text, but keep in mind that this is certainly not an exhaustive list of uses for database triggers. Every business and every system has different rules.

Enforcing Complex Business Rules

The most complicated rule that can be enforced with a check constraint is a simple mathematical expression. If business rules didn’t exceed this level of complication, database triggers probably wouldn’t exist. In the real world, business rules are often more complicated than simple equations. It’s extremely common for application developers to use a database trigger to enforce an extremely complicated rule.

Updating Relevant Data

If related data is kept in multiple tables (a fairly common occurrence in Oracle systems), it’s desirable to use a database trigger to keep related data in sync. Care must be taken when dealing with referential integrity constraints, because references to indexed columns can cause mutating table errors if the trigger isn’t structured properly.

Marking Rows For Processing

In some instances, a row-level database trigger is used to make certain that new and modified rows of data are distinguishable from rows that have had certain processing performed.

A similar approach assumes that all unmarked rows haven’t been processed. A trigger processes each row of data and flags the row to ensure that processing isn’t repeated.

Signaling An Event

A trigger can be used to signal that a particular event has occurred. This can be accomplished by using the DBMS_Alert package. This package is discussed in Chapter 9.

Trigger Structure

The basic structure of a database trigger consists of the following several distinct components:

  Trigger declaration—Defines the name of the trigger.
  Triggering event—Defines the types of DML statements that cause a trigger to fire.
  Associated table—Defines the table with which the trigger is associated.
  Trigger level—Defines whether the trigger fires at the row or statement level.
  WHEN clause—Specifies a boolean condition that is evaluated before the trigger is executed.
  Trigger body—Specifies to execute a standard block of PL/SQL when the trigger fires.

Each of these components is explained and illustrated in the following text.

Trigger Declaration

The trigger declaration is the portion of the trigger that defines the name of the trigger. The trigger declaration is highlighted in Listing 7.11.

Listing 7.11 A trigger declaration.

CREATE OR REPLACE
TRIGGER CLASSES_ARU
AFTER UPDATE
ON CLASSES
FOR EACH ROW
WHEN new.course_number != old.course_number

DECLARE
   xLOCATION_CONFLICT   EXCEPTION;

BEGIN
   SELECT 1
   INTO   nClassConflicts
   FROM   CLASSES
   WHERE  course_number   = :new.course_number
   AND    course_location = :new.course_location
   AND    course_time     = :new.course_time;

   RAISE xLOCATION_CONFLICT;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
        NULL;
END CLASSES_ARU;
/

Triggering Event

The triggering event of a trigger specifies which DML statements ( DELETE, INSERT, and/or UPDATE statements) will cause the trigger to be executed. The triggering event is highlighted in Listing 7.12.

Listing 7.12 A triggering event.

CREATE OR REPLACE
TRIGGER CLASSES_ARU
AFTER UPDATE
ON CLASSES
FOR EACH ROW
WHEN new.course_number != old.course_number

DECLARE
   xLOCATION_CONFLICT   EXCEPTION;

BEGIN
   SELECT 1
   INTO   nClassConflicts
   FROM   CLASSES
   WHERE  course_number   = :new.course_number
   AND    course_location = :new.course_location
   AND    course_time     = :new.course_time;

   RAISE xLOCATION_CONFLICT;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
        NULL;
END CLASSES_ARU;
/

Associated Table

Each database trigger, whether statement-level or row-level, is associated with a table. This table is often called the trigger’s associated table or base table. A database trigger is fired when a DELETE, INSERT, and/or UPDATE statement modifies data contained in the trigger’s associated table. Listing 7.13 illustrates how a trigger’s associated table is defined.

Listing 7.13 Defining a trigger’s associated table.

CREATE OR REPLACE
TRIGGER CLASSES_ARU
AFTER UPDATE
ON CLASSES
FOR EACH ROW
WHEN new.course_number != old.course_number

DECLARE
   xLOCATION_CONFLICT   EXCEPTION;

BEGIN
   SELECT 1
   INTO   nClassConflicts
   FROM   CLASSES
   WHERE  course_number   = :new.course_number
   AND    course_location = :new.course_location
   AND    course_time     = :new.course_time;
   RAISE xLOCATION_CONFLICT;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
        NULL;
END CLASSES_ARU;
/

Trigger Level

Each database trigger is defined at the statement-level or the row-level. A statement-level trigger fires once for each statement that causes the trigger to fire. If 10 rows are updated because of a single UPDATE statement, the trigger fires once. A row-level trigger fires once for each row of data modified by any given DML statement. If an UPDATE statement modifies 10 rows, the row-level trigger will fire 10 times. The definition of a trigger’s level is highlighted in Listing 7.14.

Listing 7.14 Declaring a trigger’s level.

CREATE OR REPLACE
TRIGGER CLASSES_ARU
AFTER UPDATE
ON CLASSES
FOR EACH ROW
WHEN new.course_number != old.course_number

DECLARE
   xLOCATION_CONFLICT   EXCEPTION;

BEGIN
   SELECT 1
   INTO   nClassConflicts
   FROM   CLASSES
   WHERE  course_number   = :new.course_number
   AND    course_location = :new.course_location
   AND    course_time     = :new.course_time;

   RAISE xLOCATION_CONFLICT;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
        NULL;
END CLASSES_ARU;
/


Previous Table of Contents Next