221-226 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

When PL/SQL 2.1 was implemented in Oracle 7.1, this 12-trigger limitation was removed. It s now possible to have several triggers of the same type on a table, but Oracle cannot be forced to execute the individual triggers in any particular order. If your business rules must be enforced in a particular order, you must enforce the dependent rules using a single trigger.

While Oracle doesn t execute triggers of the same type in the same order each time, Oracle does fire triggers of different levels in a specific order. This order is illustrated in Figure 7.1.

Figure 7.1 The execution order of database triggers.

Each row-level trigger can also have a WHEN clause defined, which is often used to replace IF - THEN processing. In Listing 7.2, the IF statement from the trigger in Listing 7.1 has been replaced with a WHEN clause.

Listing 7.2 Using a WHEN clause.

 CREATE OR REPLACE TRIGGER STUDENTS_ARIU AFTER INSERT OR UPDATE OF overall_gpa ON STUDENTS FOR EACH ROW WHEN new.overall_gpa > 3.5 BEGIN    INSERT    INTO   DEANS_LIST_STUDENTS           (ssn)    VALUES (:new.ssn); END STUDENTS_ARIU; /

The WHEN clause is used to specify an expression or condition that must evaluate to TRUE before the trigger will be executed.

The WHEN clause is normally used to test a column value. Any column referenced in the WHEN clause must be preceded by either the new or old keyword.

Maintainability

Like other stored PL/SQL objects, database triggers provide a single block of code that enforces a business rule when called from any other block of code. Because the business rule is enforced only in the trigger, the amount of code that has to be modified if the rule changes is drastically reduced.

Triggers provide one level of maintainability that is not provided by other stored PL/SQL objects. Because triggers are not called explicitly from code, the trigger can be redefined without affecting the functionality of code that writes to the trigger s associated table. Because there are no calls to change when a trigger is modified, maintenance becomes even simpler.

Performance Improvement

Starting with PL/SQL version 2.3, database triggers were compiled into p-code for quicker execution. This p-code is of the same type as the p-code generated when a procedure or function is compiled and allows Oracle to directly call the executable version of the trigger.

If you re working with an earlier version of PL/SQL, your triggers should be bare-bones calls to other stored PL/SQL objects. Complex IF - THEN logic (or other types of procedural logic) will slow the execution of your trigger because this logic must be compiled each time the trigger is executed. In this situation, it s advisable to move trigger logic into stored procedures to improve performance.

Referencing Column Values

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.

Table of Contents