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