241-245

Previous

Table of Contents

Next

WHEN Clause

The WHEN clause is a boolean expression that is evaluated by Oracle before the trigger s body is executed. Using the WHEN clause allows a developer to improve performance by testing simple conditions before the trigger s body is executed. Listing 7.15 illustrates the use of the WHEN clause.

Listing 7.15 Using the WHEN clause.

 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 Body

The body of a database trigger, with the restrictions that we ve already discussed, is a standard block of PL/SQL. The body of a trigger is highlighted in Listing 7.16.

Listing 7.16 A trigger body.

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

Documenting Triggers

Documentation for a trigger is quite similar to the documentation for a procedure, function, or package. However, some differences do come into play, such as:

   Triggers don t have parameters.

   Triggers are always public objects.

   Triggers always fire whenever the conditions are right.

   Triggers often implement unusual code to work around mutating table errors.

The basics of writing good documentation for your code remain the same, regardless of what type of stored PL/SQL object you re writing. The proper use of a header, pseudocode, commenting, and meaningfully named identifiers all contribute to communicating the purpose and functionality of your trigger to the person who must maintain the code. The use of commenting, identifier names , and pseudocode are discussed in detail earlier in this book. So, let s skip those topics here and take a look at the trigger header.

Trigger Header

Your trigger s header must provide certain information about the trigger. The header for a trigger, like the header for a procedure or function, needs to describe the code and its purpose. These are some of the questions that the header should answer:

   With which table is the trigger associated?

   What type(s) of DML statement causes the trigger to fire?

   If the trigger is an UPDATE trigger, does it fire for any particular columns ?

   What error conditions can be raised from the trigger?

   Through which hoops does the trigger jump to avoid mutating table errors?

Listing 7.17 shows an example of a header that deals with each of these issues.

Listing 7.17 A sample header for a trigger.

 -- ****************************************************************** -- Description: The ENROLLED_CLASSES_ARIU trigger fires whenever a --   row is created or modified in the ENROLLED_CLASSES table. The --   trigger determines the student's current academic level and  --   the minimum academic level for the course. If the minimum level --   for the course exceeds the student's academic level, the  --   exception xSTUDENT_NOT_QUALIFIED is raised and the transaction --   is aborted. -- -- Fires: On an INSERT or UPDATE of the ENROLLED_CLASSES table, for --        every new or modified row --  -- REVISION HISTORY -- Date         Author      Reason for Change -- ------------------------------------------------------------------ -- 21 APR 1997  J. Ingram   Trigger created. -- ****************************************************************** 

Previous

Table of Contents

Next