In the course of executing a database application, occasions may arise where if some specific action occurs, you want that action to cause another action, or perhaps a succession of actions, to occur. In a sense, that first action triggers the execution of the following actions. SQL provides the
TRIGGER mechanism to provide this capability.
Triggers, of course, are best known as those parts of a firearm that cause it to fire. More generally, a trigger is an action or event that causes another event to occur. In SQL, the word trigger is used in this more general sense. A triggering SQL statement causes another SQL statement (the triggered statement) to be executed.
The firing of a trigger is useful in a number of different situations. One example is to perform a logging function. Certain actions that are critical to the integrity of a database — such as inserting, editing, or deleting a table row — could trigger the making of an entry in a log that documents that action. Log entries can record not only what action was taken, but also when it was taken and by whom.
Triggers can also be used to keep a database consistent. In an order entry application, an order for a specific product can trigger a statement that changes the status of that product in the inventory table from available to reserved. Similarly, the deletion of a row in the orders table can trigger a statement that changes the status of the subject product from reserved ...