TRIGGER

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database. For example, when a new record (representing a new worker) added to the employees table, new records should be created also in the tables of the taxes, vacations, and salaries.

DML Triggers

There are typically three triggering events that cause data triggers to 'fire':

• INSERT event (as a new record is being inserted into the database).
• UPDATE event (as a record is being changed).
• DELETE event (as a record is being deleted).

Structurally, triggers are either "row triggers" or "statement triggers". Row triggers define an action for every row of a table, while statement triggers occur only once per INSERT, UPDATE, or DELETE statement. DML triggers cannot be used to audit data retrieval via SELECT statements, because SELECT is not a triggering event.
Furthermore, there are "BEFORE triggers" and "AFTER triggers" which run in addition to any changes already being made to the database, and "INSTEAD OF trigger" which fully replace the database's normal activity.
Triggers do not accept parameters, but they do receive information in the form of implicit variables. For row-level triggers, these are generally OLD and NEW variables, each of which have fields corresponding to the columns of the affected table or view; for statement-level triggers, something like SQL Server's Inserted and Deleted tables may be provided so the trigger can see all the changes being made.
For data triggers, the general order of operations will be as follows:

1. a statement requests changes on a row: OLD represents the row as it was before the change (or is all-null for inserted rows), NEW represents the row after the changes (or is all-null for deleted rows)
2. each statement-level BEFORE trigger is fired
3. each row-level BEFORE trigger fires, and can modify NEW (but not OLD); each trigger can see NEW as modified by its predecessor, they are chained together
4. if an INSTEAD OF trigger is defined, it is run using OLD and NEW as available at this point
5. if no INSTEAD OF trigger is defined, the database modifies the row according to its normal logic; for updateable view this may involve modifying one or more other tables to achieve the desired effect; if a view is not updatable, and no INSTEAD OF trigger is provided, an error is raised
6. each row-level AFTER trigger fires, and is given NEW and OLD, but its changes to NEW are either disallowed or disregarded
7. each statement-level AFTER trigger is fired
8. implied triggers are fired, such as refrencial action in support of foreign key constraints: on-update or on-delete CASCADE, SET NULL, and SET DEFAULT rules

In ACID databases, an exception raised in a trigger will cause the entire stack of operations to be rolled back, including the original statement.