Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter