Oracle7 Server Concepts

Contents Index Home Previous Next

Trigger Execution

A trigger can be in either of two distinct modes:

enabled An enabled trigger executes its trigger action if a triggering statement is issued and the trigger restriction (if any) evaluates to TRUE.
disabled A disabled trigger does not execute its trigger action, even if a triggering statement is issued and the trigger restriction (if any) would evaluate to TRUE.
For enabled triggers, Oracle automatically

The Execution Model for Triggers and Integrity Constraint Checking

A single SQL statement can potentially fire up to four types of triggers: BEFORE row triggers, BEFORE statement triggers, AFTER row triggers, and AFTER statement triggers. A triggering statement or a statement within a trigger can cause one or more integrity constraints to be checked. Also, triggers can contain statements that cause other triggers to fire (cascading triggers).

Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:

1. Execute all BEFORE statement triggers that apply to the statement.
2. Loop for each row affected by the SQL statement.
a. Execute all BEFORE row triggers that apply to the
statement.
b. Lock and change row, and perform integrity constraint
checking (The lock is not released until the
transaction is committed.)
c. Execute all AFTER row triggers that apply to the
statement.
3. Complete deferred integrity constraint checking.
4. Execute all AFTER statement triggers that apply to the statement.

The definition of the execution model is recursive. For example, a given SQL statement can cause a BEFORE row trigger to be fired and an integrity constraint to be checked. That BEFORE row trigger, in turn, might perform an update that causes an integrity constraint to be checked and an AFTER statement trigger to be fired. The AFTER statement trigger causes an integrity constraint to be checked. In this case, the execution model executes the steps recursively, as follows:

1. Original SQL statement issued.
2. BEFORE row triggers fired.
3. AFTER statement triggers fired by UPDATE in
BEFORE row trigger.
4. Statements of AFTER statement triggers
executed.
5. Integrity constraint on tables changed by
AFTER statement triggers checked.
6. Statements of BEFORE row triggers executed.
7. Integrity constraint on tables changed by
BEFORE row triggers checked.
8. SQL statement executed.
9. Integrity constraint from SQL statement checked.

An important property of the execution model is that all actions and checks done as a result of a SQL statement must succeed. If an exception is raised within a trigger, and the exception is not explicitly handled, all actions performed as a result of the original SQL statement, including the actions performed by fired triggers, are rolled back. Thus, integrity constraints cannot be compromised by triggers. The execution model takes into account integrity constraints and disallows triggers that violate declarative integrity constraints.

For example, in the previously outlined scenario, suppose that Steps 1 through 8 succeed; however, in Step 9 the integrity constraint is violated. As a result of this violation, all changes made by the SQL statement (in Step 8), the fired BEFORE row trigger (in Step 6), and the fired AFTER statement trigger (in Step 4) are rolled back.

Note: Be aware that triggers of different types are fired in a specific order. However, triggers of the same type for the same statement are not guaranteed to fire in any specific order. For example, all BEFORE ROW triggers for a single UPDATE statement may not always fire in the same order. Design your applications not to rely on the firing order of multiple triggers of the same type.

Data Access for Triggers

When a trigger is fired, the tables referenced in the trigger action might be currently undergoing changes by SQL statements contained in other users' transactions. In all cases, the SQL statements executed within triggers follow the common rules used for standalone SQL statements. In particular, if an uncommitted transaction has modified values that a trigger being fired either needs to read (query) or write (update), the SQL statements in the body of the trigger being fired use the following guidelines:

The following examples illustrate these points.

Example

Assume that the SALARY_CHECK trigger (body) includes the following SELECT statement: 

SELECT minsal, maxsal INTO minsal, maxsal 
	FROM salgrade 
	WHERE job_classification = :new.job_classification;

For this example, assume that transaction T1 includes an update to the MAXSAL column of the SALGRADE table. At this point, the SALARY_CHECK trigger is fired by a statement in transaction T2. The SELECT statement within the fired trigger (originating from T2) does not see the update by the uncommitted transaction T1, and the query in the trigger returns the old MAXSAL value as of the read-consistent point for transaction T2.

Example

Assume the following definition of the TOTAL_SALARY trigger, a trigger to maintain a derived column that stores the total salary of all members in a department:

CREATE TRIGGER total_salary 
AFTER DELETE OR INSERT OR UPDATE OF deptno, sal ON emp 
  FOR EACH ROW BEGIN 
  /* assume that DEPTNO and SAL are non-null fields */ 
   IF DELETING OR (UPDATING AND :old.deptno != :new.deptno) 
   THEN UPDATE dept 
	SET total_sal = total_sal - :old.sal 
	WHERE deptno = :old.deptno; 
   END IF; 
   IF INSERTING OR (UPDATING AND :old.deptno != :new.deptno) 
   THEN UPDATE dept 
    SET total_sal = total_sal + :new.sal 
    WHERE deptno = :new.deptno; 
   END IF; 
   IF (UPDATING AND :old.deptno = :new.deptno AND 
	  :old.sal != :new.sal ) 
   THEN UPDATE dept 
    SET total_sal = total_sal - :old.sal + :new.sal 
   WHERE deptno = :new.deptno; 
  END IF; 
 END;

For this example, suppose that one user's uncommitted transaction includes an update to the TOTAL_SAL column of a row in the DEPT table. At this point, the TOTAL_SALARY trigger is fired by a second user's SQL statement. Because the uncommitted transaction of the first user contains an update to a pertinent value in the TOTAL_SAL column (in other words, a row lock is being held), the updates performed by the TOTAL_SALARY trigger are not executed until the transaction holding the row lock is committed or rolled back. Therefore, the second user waits until the commit or rollback point of the first user's transaction.

Storage for Triggers

For release 7.3, Oracle stores triggers in their compiled form, just like stored procedures. When a CREATE TRIGGER statement commits, the compiled PL/SQL code, called P code (for pseudocode), is stored in the database and the source code of a trigger is flushed from the shared pool.

For More Information

See "How Oracle Stores Procedures and Packages" [*].

Execution of Triggers

Oracle internally executes a trigger using the same steps used for procedure execution. The subtle and only difference is that a user automatically has the right to fire a trigger if he/she has the privilege to execute the triggering statement. Other than this, triggers are validated and executed the same way as stored procedures.

For More Information

See "How Oracle Executes Procedures and Packages" [*].

Dependency Maintenance for Triggers

Oracle automatically manages the dependencies of a trigger on the schema objects referenced in its trigger action. The dependency issues for triggers are the same as dependency issues for stored procedures. In releases earlier than 7.3, triggers were kept in memory. In release 7.3, triggers are treated like stored procedures; they are inserted in the data dictionary. Like procedures, triggers are dependent on referenced objects. Oracle automatically manages dependencies among objects.

For More Information

See Chapter 16, "Dependencies Among Schema Objects".

Contents Index Home Previous Next