Previous | Table of Contents | Next |
The WHEN clause is a boolean expression that is evaluated by Oracle before the triggers body is executed. Using the WHEN clause allows a developer to improve performance by testing simple conditions before the triggers 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; /
The body of a database trigger, with the restrictions that weve 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; /
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:
The basics of writing good documentation for your code remain the same, regardless of what type of stored PL/SQL object youre 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, lets skip those topics here and take a look at the trigger header.
Your triggers 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:
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 |