Controlling When a Trigger Is Fired

Firing Triggers With the BEORE and AFTER Options

The BEFORE or AFTER option in the CREATE TRIGGER statement specifies exactly when to fire the trigger body in relation to the triggering statement that is being run. In a CREATE TRIGGER statement, the BEFORE or AFTER option is specified just before the triggering statement.

In general, you use BEFORE or AFTER triggers to achieve the following results:

• Use a BEFORE row trigger to modify the row before the row data is written to disk. See Example: Creating a Database Trigger With the BEFORE Option for an example of a BEFORE trigger.

• Use an AFTER row trigger to obtain and perform operations using the row ID. See Example: Creating a Database Trigger WIth the AFTER Option for an example of an AFTER trigger.

  
  

  Note: BEFORE row triggers are slightly more efficient than AFTER row triggers. With AFTER row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with BEFORE row triggers, the data blocks must be read only once for both the triggering statement and the trigger.

  
  

If an UPDATE or DELETE statement detects a conflict with a concurrent UPDATE statement, then Oracle Database XE performs a transparent ROLLBACK and restarts the update operation. This can occur many times before the statement completes successfully. Each time the statement is restarted, the BEFORE statement trigger is fired again. The rollback does not undo changes to any package variables referenced in the trigger. Your package should include a counter variable to detect this situation.

Firing Triggers With the FOR EACH ROW Option

The FOR EACH ROW option determines whether the trigger is a row trigger or a statement trigger. If you specify FOR EACH ROW, then the trigger fires once for each row of the table that is affected by the triggering statement. These triggers are referred to as row-level triggers. See the use of FOR EACH ROW in Example: Creating a Database Trigger WIth the AFTER Option and Example: Creating a Database Trigger With the BEFORE Option.

The absence of the FOR EACH ROW option indicates that the trigger fires only once for each applicable statement, but not separately for each row affected by the statement. These triggers are referred to as statement-level triggers and are useful for performing validation checks for the entire statement. In Example: Creating a Trigger That Fires Only Once, the trigger fires only once for each update of the employees table.

Firing Triggers Based on Conditions (WHEN Clause)

An optional trigger restriction can be included in the definition of a row trigger by specifying a Boolean SQL expression in a WHEN clause.

If included, the expression in the WHEN clause is evaluated for each row that the trigger affects. If the expression evaluates to TRUE for a row, then the trigger body is fired on behalf of that row. Otherwise, if the expression evaluates to FALSE, the trigger body is not fired. See Example: Creating a Database Trigger With the BEFORE Option for an example of the use of the WHEN clause in a trigger.

The expression in a WHEN clause must be a SQL expression, and it cannot include a subquery. You cannot use a PL/SQL expression (including user-defined functions) in a WHEN clause. A WHEN clause cannot be included in the definition of a statement trigger.