Oracle7 Server Concepts

Contents Index Home Previous Next

Parts of a Trigger

A trigger has three basic parts:

Figure 15 - 3 represents each of these parts of a trigger and is not meant to show exact syntax. Each part of a trigger is explained in greater detail in the following sections.

Figure 15 - 3. The REORDER Trigger

Triggering Event or Statement

A triggering event or statement is the SQL statement that causes a trigger to be fired. A triggering event can be an INSERT, UPDATE, or DELETE statement on a table.

For example, in Figure 15 - 3, the triggering statement is

. . . UPDATE OF parts_on_hand ON inventory . . .

which means that when the PARTS_ON_HAND column of a row in the INVENTORY table is updated, fire the trigger. Note that when the triggering event is an UPDATE statement, you can include a column list to identify which columns must be updated to fire the trigger. Because INSERT and DELETE statements affect entire rows of information, a column list cannot be specified for these options.

A triggering event can specify multiple DML statements, as in

. . . INSERT OR UPDATE OR DELETE OF inventory . . .

which means that when an INSERT, UPDATE, or DELETE statement is issued against the INVENTORY table, fire the trigger. When multiple types of DML statements can fire a trigger, conditional predicates can be used to detect the type of triggering statement. Therefore, a single trigger can be created that executes different code based on the type of statement that fired the trigger.

Trigger Restriction

A trigger restriction specifies a Boolean (logical) expression that must be TRUE for the trigger to fire. The trigger action is not executed if the trigger restriction evaluates to FALSE or UNKNOWN.

A trigger restriction is an option available for triggers that are fired for each row. Its function is to control the execution of a trigger conditionally. You specify a trigger restriction using a WHEN clause. For example, the REORDER trigger in Figure 15 - 3 has a trigger restriction. The trigger is fired by an UPDATE statement affecting the PARTS_ON_HAND column of the INVENTORY table, but the trigger action only fires if the following expression is TRUE: 

new.parts_on_hand < new.reorder_point

Trigger Action

A trigger action is the procedure (PL/SQL block) that contains the SQL statements and PL/SQL code to be executed when a triggering statement is issued and the trigger restriction evaluates to TRUE.

Similar to stored procedures, a trigger action can contain SQL and PL/SQL statements, define PL/SQL language constructs (variables, constants, cursors, exceptions, and so on), and call stored procedures. Additionally, for row trigger, the statements in a trigger action have access to column values (new and old) of the current row being processed by the trigger. Two correlation names provide access to the old and new values for each column.

Types of Triggers

When you define a trigger, you can specify the number of times the trigger action is to be executed: once for every row affected by the triggering statement (such as might be fired by an UPDATE statement that updates many rows), or once for the triggering statement, no matter how many rows it affects.

Row Triggers A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not executed at all.

Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. For example, Figure 15 - 3 illustrates a row trigger that uses the values of each row affected by the triggering statement.

Statement Triggers A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected). For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, if a trigger makes a complex security check on the current time or user, or if a trigger generates a single audit record based on the type of triggering statement, a statement trigger is used.

BEFORE vs. AFTER Triggers

When defining a trigger, you can specify the trigger timing. That is, you can specify whether the trigger action is to be executed before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.

BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement. This type of trigger is commonly used in the following situations:

AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is executed. AFTER triggers are used in the following situations:

Combinations

Using the options listed in the previous two sections, you can create four types of triggers:

You can have multiple triggers of the same type for the same statement for any given table. For example you may have two BEFORE STATEMENT triggers for UPDATE statements on the EMP table. Multiple triggers of the same type permit modular installation of applications that have triggers on the same tables. Also, Oracle snapshot logs use AFTER ROW triggers, so you can design your own AFTER ROW trigger in addition to the Oracle-defined AFTER ROW trigger.

You can create as many triggers of the preceding different types as you need for each type of DML statement (INSERT, UPDATE, or DELETE). For example, suppose you have a table, SAL, and you want to know when the table is being accessed and the types of queries being issued. Figure 15 - 4 contains a sample package and trigger that tracks this information by hour and type of action (for example, UPDATE, DELETE, or INSERT) on table SAL. A global session variable, STAT.ROWCNT, is initialized to zero by a BEFORE statement trigger, then it is increased each time the row trigger is executed, and finally the statistical information is saved in the table STAT_TAB by the AFTER statement trigger. 

DROP TABLE stat_tab; 
CREATE TABLE stat_tab(utype CHAR(8), 
			rowcnt INTEGER, uhour INTEGER); 
 
CREATE OR REPLACE PACKAGE stat IS 
 rowcnt INTEGER; 
END; 
/ 
 
CREATE TRIGGER bt BEFORE UPDATE OR DELETE OR INSERT ON sal 
BEGIN 
 stat.rowcnt := 0; 
END; 
/ 
 
CREATE TRIGGER rt BEFORE UPDATE OR DELETE OR INSERT ON sal 
FOR EACH ROW BEGIN 
 stat.rowcnt := stat.rowcnt + 1; 
END; 
/ 
 
CREATE TRIGGER at AFTER UPDATE OR DELETE OR INSERT ON sal 
DECLARE 
 typ  CHAR(8); 
 hour NUMBER; 
BEGIN 
 IF updating 
 THEN typ := 'update'; END IF; 
 IF deleting  THEN typ := 'delete'; END IF; 
 IF inserting THEN typ := 'insert'; END IF; 
 
 hour := TRUNC((SYSDATE - TRUNC(SYSDATE)) * 24); 
 UPDATE stat_tab 
    SET rowcnt = rowcnt + stat.rowcnt 
   WHERE utype = typ 
      AND uhour = hour; 
 IF SQL%ROWCOUNT = 0 THEN 
   INSERT INTO stat_tab VALUES (typ, stat.rowcnt, hour); 
 END IF; 
 
EXCEPTION 
 WHEN dup_val_on_index THEN 
   UPDATE stat_tab 
      SET rowcnt = rowcnt + stat.rowcnt 
     WHERE utype = typ 
       AND uhour = hour; 
END; 
/

Figure 15 - 4. Sample Package and Trigger for SAL Table

Contents Index Home Previous Next