 |
|
Referencing Column
Values
Oracle Tips by
Burleson
|
Row-level database triggers can reference both old and new column
values using the :old and :new specifications. This
allows a trigger to determine the change made to a row and then take
the appropriate action. These specifications will be discussed in
detail later in this chapter.
Reusability
A database trigger is the most inherently reusable stored PL/SQL
object. Any code that modifies data in the trigger’s associated table
will fire the trigger (assuming, of course, that the statement meets
the execution criteria for the trigger). This is accomplished without
any calls to the trigger from other blocks of code.
The :old And :new Specifications
In row-level triggers, SQL*Plus and PL/SQL statements can reference
both the previous and new values of individual columns (other than
columns of type long and long raw ) by using the :old
and :new specifications. The :old specification refers
to the previous value of a column, and the :new specification
refers to the new value of a column. These specifications allow
application developers to test column changes using more complex
conditions than can be satisfied by using the WHEN clause.
The use of the :old and :new specifications varies
depending on the type of statement that causes the trigger to execute.
Figure 7.2 illustrates how these specifications are used in each type
of trigger.
Figure 7.2 Using the :old and :new
specification in a row-level trigger.
Row-level database triggers that fire before a table is modified
can also alter data within a new row by assigning a value to a column
using the :new specification.
Boolean Functions
Database triggers that fire for more than one type of DML statement
can use several Boolean functions for logical control. These Boolean
functions are listed in Table 7.2.
|
Table 7.2 The Boolean functions used in database triggers.
|
|
Boolean Function |
Return Value |
|
DELETING |
Returns TRUE if the trigger was fired
because of a DELETE statement; otherwise returns FALSE.
|
|
INSERTING |
Returns TRUE if the trigger was fired
because of an INSERT statement; otherwise returns FALSE.
|
|
UPDATING |
Returns TRUE if the trigger was fired
because of an UPDATE statement; otherwise returns FALSE.
|
The use of these Boolean functions is illustrated in Listing 7.3.
Listing 7.3 Using Boolean functions in a database trigger.
CREATE OR REPLACE
TRIGGER STUDENTS_ARIU
AFTER INSERT OR UPDATE OF overall_gpa
ON STUDENTS
FOR EACH ROW
DECLARE
xGPA_TOO_HIGH EXCEPTION;
BEGIN
IF UPDATING THEN
IF (:new.overall_gpa > 4.0) THEN
RAISE xGPA_TOO_HIGH;
END IF;
IF (:new.overall_gpa > 3.5) THEN
INSERT
INTO DEANS_LIST_STUDENTS
(ssn)
VALUES (:new.ssn);
ELSIF (:new.overall_gpa < 1.5) THEN
INSERT
INTO STUDENTS_NEEDING_ASSISTANCE
(ssn)
VALUES (:new.ssn);
END IF;
ELSIF INSERTING THEN
:new.overall_gpa := NULL;
END IF;
END STUDENTS_ARIU;
/
In Listing 7.3, the trigger STUDENTS_ARIU uses the
UPDATING boolean function to determine if a student’s record is
being updated, and the INSERTING boolean function to determine
if the row is for a new student.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
