 |
|
WHEN Clause
Oracle Tips by
Burleson
|
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;
/
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
