Triggers are active database objects associated with a subject table. A trigger essentially consists of a stored SQL statement or a block of SQL statements.
Triggers run when an INSERT, UPDATE, DELETE, or MERGE modifies a specified column or columns in the subject table.
Typically, a stored trigger performs an UPDATE, INSERT, DELETE, MERGE, or other SQL operation on one or more tables, which may possibly include the subject table.
Triggers in the database conform to the ANSI/ISO SQL:2008 standard, and also provide additional features.
- Row triggers fire once for each row of the subject table that is changed by the triggering event and that satisfies any qualifying condition included in the row trigger definition.
- Statement triggers fire once on the execution of the triggering statement.
You can create, alter, and drop triggers.
| Goal | Statement to Use |
|---|---|
| Define trigger | CREATE TRIGGER |
|
ALTER TRIGGER Disabling a trigger stops the trigger from functioning, but leaves the trigger definition in place as an object. This allows utility operations on a table that are not permitted on tables with enabled triggers. Enabling a trigger restores its active state. |
| Permanently remove trigger from system | DROP TRIGGER |