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