237-241

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


High Performance Oracle Database Automation
High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL
ISBN: 1576101525
EAN: 2147483647
Year: 2005
Pages: 92

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net