Previous | Table of Contents | Next |
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:
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.
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.
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.
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.
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.
The basic structure of a database trigger consists of the following several distinct components :
Each of these components is explained and illustrated in the following text.
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; /
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; /
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; /
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 |