Understanding How Oracle Triggers are Executed

The Oracle relational database system (RDBMS) supports triggers. Essentially, a trigger is a piece of code that is executed when data in a specified table in the database changes. Oracle triggers are implemented in a language called PL/SQL, which is like standard SQL, but with procedural extensions such as IF statements and looping constructs.

When a change results in a trigger being executed, the trigger is said to have been "fired." The exact conditions under which a trigger will fire are defined when the trigger is created. A trigger can only monitor the state of a single table, i.e. - you cannot define a trigger that will react to changes in multiple tables. You can, however, constrain a trigger to fire only when specified columns within a table are altered.

Firing Conditions

There are three characteristics that can be defined to determine when a trigger will be fired:

  1. Insert, Update or Delete
  2. Before or After
  3. Row Level or Statement Level

The first characteristic is fairly obvious. A trigger can be set to fire when data is inserted into a table, updated or deleted from a table. Any combination of the three can also be set, so it's possible, for example, to set up a trigger to fire only on an insert or update operation.

The second characteristic has to do with the timing of the execution of the trigger. Since triggers are only fired when an operation that will change a table occurs, the issue that must be dealt with is whether the trigger fires before or after the change takes place.

The third characteristic determines whether a single database operation will result in only one firing of the trigger, or if the trigger will be fired once for every row that is affected. For example, an update operation is submitted that affects 10 rows. A statement-level trigger will fire only once, but a row-level statement will fire 10 times, once for each row.

These characteristics interact to allow 12 possible combinations under which a trigger can be fired. Some examples will illustrate how these characteristics can be used to define trigger firing conditions:

  Example 1: Store audit information about the EMPLOYEE table every time an employee is deleted.   A trigger would be defined to fire only on a delete operation. The trigger should fire before the database operation occurs, because the information will be gone after the change. For each row that is deleted, audit information will be stored in an audit table, so the trigger should fire for each row. This would be referred to as a BEFORE-DELETE-ROW trigger.
 
  Example 2: A table is used to track communication circuits (T1 lines). A business rule exists that states that only inactive circuits can be deleted. Conveniently, one of the columns in the table indicates whether a circuit is active or inactive.   A trigger would be defined to fire only on a delete operation. The trigger would enforce the business rule by checking the active/inactive state of each row (which makes it a row-level trigger). Finally, the trigger would fire before the change occurs. That way, if the change is not valid, the trigger can raise an error, which will prevent the database operation from occurring. Like the first example, this would be a BEFORE-DELETE-ROW trigger.
 
  Example 3: Users can request emergency production installs online, which may or may not be approved by management. If approved, the status of a request will be updated to "Approved". At this point, information from various tables should be copied into another table that is used to generate reports.   A trigger would be defined to fire only on an update operation. The code in the trigger would check whether the status of a row had been updated. If so, it would copy required information from request-related tables into the table from which reports will be generated. This trigger could be a BEFORE-UPDATE-ROW trigger or an AFTER-UPDATE-ROW trigger.

A Useful Convention for Oracle Triggers

In the previous section, the examples illustrated how easy it is to fall into the practice of referring to triggers by their firing characteristics, such as a BEFORE-DELETE-ROW trigger. Since this provides an easy handle for understanding when the trigger will operate, it should come as no surprise that this can be codified into a highly beneficial convention that can save a lot of time and trouble when it comes to maintenance.

Triggers should be named according to the following convention:

  Naming Format: <Table Name>_XYZ
 
  Where:    X is either 'B' or 'A' for Before or After.
Y is 'I', 'U' or 'D' for Insert, Update or Delete.
Z is 'R' or 'S' for Row-Level or Statement-Level.

Following this convention, a BEFORE-INSERT-ROW trigger on the EMPLOYEE table would be called "EMPLOYEE_BIR". Another nice feature about using a convention like this is that, should an error occur in the trigger, the resulting Oracle error message would reference the name of the trigger, which will be distinct. No more hunting through various triggers to discover which one caused a problem.

Other Trigger Types

For the record, recent releases of Oracle have added a few new trigger types. However, the new trigger types are more esoteric and the situations when their use is appropriate are relatively rare. Understanding the "standard" twelve combinations of trigger firing conditions will allow you to easily craft triggers that audit data changes, enforce business rules or perform numerous other functions.

Related Links

  • http://www.oracle.com

    Oracle is the dominant vendor of relational database products in the world. Their flagship product, the Oracle relational database system (RDBMS) is used by a majority of the large web sites in the world.

  • http://www.oracle.com/technology/index.html

    This is the extensive technology resource area that Oracle provides for its user community. It is one of the most comprehensive technology resources provided by any company on the web.



Comments

No comments yet. Be the first.



Leave a Comment

Comments are moderated and will not appear on the site until reviewed.

(not displayed)