A trigger defines a set of actions that are performed
in response to an insert, update, or delete operation on a specified
table. When such an SQL operation is executed, the trigger is said
to have been activated. Triggers are optional and are defined
using the CREATE TRIGGER statement.
Triggers can be used, along with referential constraints and check
constraints, to enforce data integrity rules. Triggers can also be
used to cause updates to other tables, automatically generate or transform
values for inserted or updated rows, or invoke functions to perform
tasks such as issuing alerts.
The following types of triggers are supported:
- BEFORE triggers
- Run before an update, or insert. Values that are being updated
or inserted can be modified before the database is actually modified.
You can use triggers that run before an update or insert in several
ways:
- To check or modify values before they are actually updated or
inserted in the database. This is useful if you must transform data
from the way the user sees it to some internal database format.
- To run other non-database operations coded in user-defined functions.
- BEFORE DELETE triggers
- Run before a delete. Checks values (a raises an error, if necessary).
- AFTER triggers
- Run after an update, insert, or delete. You can use triggers that
run after an update or insert in several ways:
- To update data in other tables. This capability is useful for
maintaining relationships between data or in keeping audit trail information.
- To check against other data in the table or in other tables. This
capability is useful to ensure data integrity when referential integrity
constraints aren't appropriate, or when table check constraints limit
checking to the current table only.
- To run non-database operations coded in user-defined functions.
This capability is useful when issuing alerts or to update information
outside the database.
- INSTEAD OF triggers
- Describe how to perform insert, update, and delete operations
against views that are too complex to support these operations natively.
They allow applications to use a view as the sole interface for all
SQL operations (insert, delete, update and select).