Also note that one trigger can be defined to fire for more than one SQL statement. The example in Listing 11.1 shows a trigger that is both an insert trigger and an update trigger.

Because there are four possible trigger types that can be created for a specific SQL statement, it makes sense to ask about the execution order. Which trigger gets executed first? Which last? What if there are multiple triggers defined of the same type? Figure 11.1 shows the order of execution of the various trigger types in relation to each other and in relation to the triggering SQL statement.

Figure 11.1.
Trigger execution
order.

Triggers of the same type are executed in no particular order. If you write several triggers of the same type, for example three triggers that fire before a row is updated, you must ensure that the integrity of the database does not depend on the order of execution.

Syntax

The syntax used to define a database trigger is shown following. There are a number of optional and repeating clauses that cause the syntax explanation to look a bit cluttered, but don't be intimidated by them. The examples in the rest of this chapter make use of all the trigger features. Reading through these examples should give you a good feel for how the different variations in trigger definitions work and how you can use them.

The Syntax for Defining a Database Trigger

CREATE [OR REPLACE] TRIGGER trigger_name
  {BEFORE|AFTER} verb_list ON table_name
  [[REFERENCING correlation_names] FOR EACH ROW [WHEN (condition)]]
DECLARE
  declarations
BEGIN
  pl/sql_code
END;
/

In this syntax, the parameters are as follows:

• trigger_name is the name you want to give the trigger. By default, Oracle will create the trigger in your schema, but you can specify a specific schema by using the standard schema_name.trigger_name notation.
• verb_list identifies the SQL verbs that fire the trigger. The syntax of the verb_list is as follows:

  {INSERT|DELETE|UPDATE [OF column_list]} [OR verb_list]
  

In the preceding syntax, the parameters are

• table_name is the table on which the trigger is defined. As with the trigger name, you can qualify this with a schema name; otherwise, the table is assumed to be in your schema.
• correlation_names allows you to specify correlation names other than the default of OLD and NEW. This is useful if the table on which the trigger is defined happens to be OLD or NEW and can also be helpful in making the trigger code self-documenting. The referencing clause looks like this:

  {OLD AS old_alias|NEW AS new_alias [correlation_names]}
  

  In the preceding syntax, the parameters are

  old_alias	This is a name you want to use when referring to the value of a field before the SQL verb executes.
  new_alias	This is a name you want to use when referring to the value of a field after the SQL verb executes.
  correlation_names	This is another iteration of the alias list. You can specify an alias for both old and new values.

• condition is an optional condition placed on the execution of the trigger. This can only be used on row-level triggers, and if condition is present, the trigger will only be fired when the condition is true. The condition can be any boolean expression, cannot contain any queries, and must use correlation names, in other words NEW and OLD, to refer to column values in the row being changed.
• declarations consists of any variable, record, or cursor declarations needed by this PL/SQL block.
• pl/sql_code is the PL/SQL code that gets executed when the trigger fires.

NEW TERM
The correlation names :OLD and :NEW deserve some extra explanation. It is common when writing a trigger to need to reference the values in the record being inserted, updated, or deleted. Further, in the case of an update, it is often necessary to access both the before and after values of a given field. The correlation names :OLD and :NEW are provided for this purpose. These function much like a PL/SQL record. :OLD contains the field values

before they are updated, and :NEW contains the field values after the update takes place. Use standard dot notation, in other words :OLD.field_name, to refer to the value of a particular field. You will see examples of this in several of the listings in this chapter.

Uses for Triggers

The possible uses for database triggers are varied and are limited only by your imagination. Some common uses are listed in the following:

• Enforcing business rules
• Maintaining referential integrity
• Enforcing security
• Maintaining a historical log of changes
• Generating column values, including primary key values
• Replicating data

The next few sections give examples of these uses.

Enforcing a Business Rule

Oracle provides a fine set of declarative options for enforcing referential integrity and business rules. The business world, however, is fast-paced and always changing. It is also full of exceptions, and some rules might be impossible to write as simple declarative statements. Triggers provide another option, allowing you to write code in support of even the most complex and convoluted rules.

Consider this rather simple business rule involving an employee's pay rate in the sample database:

When an employee's pay type is changed from hourly to salary, or the reverse, his pay rate must also change.