Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Triggers

You can use a constraint to automatically enforce a data integrity rule whenever a user tries to write or modify a row in a table. There are times when you want the same kind of enforcement of your own database or application-specific logic. Oracle includes triggers to give you this capability.

Tip

Although you can write triggers to perform the work of a constraint, Oracle has optimized the operation of constraints, so it’s best to always use a constraint instead of a trigger if possible.

A trigger is a block of code that is fired whenever a particular type of database event occurs to a table. There are three types of events that can cause a trigger to fire:

• A database UPDATE

• A database INSERT

• A database DELETE

You can, for instance, define a trigger to write a customized audit record whenever a user changes a row.

Triggers are defined at the row level. You can specify that a trigger is to be fired either for each row or for the SQL statement that fires the trigger event. As with the previous discussion of constraints, a single SQL statement can affect many rows, so the specification of the trigger can have a significant effect on the operation of the trigger and the performance of the database.

There are three times when a trigger can fire:

• Before the execution of the triggering event

• After the execution of the triggering event

• Instead of the triggering event

Combining the first two timing options with the row and statement versions of a trigger gives you four possible trigger ...