|
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:
- Insert, Update or Delete
- Before or After
- 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.
|
