Overview of Triggers

Overview of Triggers
A trigger is a named program unit that is stored in the database and fired (executed) in response to a specified event. The specified event is associated with either a table, a view, a schema, or the database, and it is one of the following:
•A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
•A database definition (DDL) statement (CREATE, ALTER, or DROP)
•A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)
The trigger is said to be defined on the table, view, schema, or database.
Topics:
• Trigger Types
• Trigger States
• Data Access for Triggers
• Uses of Triggers
Trigger Types
A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on.
An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The database fires the INSTEAD OF trigger instead of executing the triggering DML statement. For more information, see Modifying Complex Views (INSTEAD OF Triggers).
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.
A simple trigger can fire at exactly one of the following timing points:
•Before the triggering statement executes
•After the triggering statement executes
•Before each row that the triggering statement affects
•After each row that the triggering statement affects
A compound trigger can fire at more than one timing point. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data. For more information, see Compound Triggers.
Trigger States
A trigger can be in either of two states:
Enabled. An enabled trigger executes its trigger body if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE.
Disabled. A disabled trigger does not execute its trigger body, even if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE.
By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement.
See Also:
CREATE TRIGGER Statement
Data Access for Triggers
When a trigger is fired, the tables referenced in the trigger action might be currently undergoing changes by SQL statements in other users' transactions. In all cases, the SQL statements running within triggers follow the common rules used for standalone SQL statements. In particular, if an uncommitted transaction has modified values that a trigger being fired either must read (query) or write (update), then the SQL statements in the body of the trigger being fired use the following guidelines:
•Queries see the current read-consistent materialized view of referenced tables and any data changed within the same transaction.
•Updates wait for existing data locks to be released before proceeding.
Uses of Triggers
Triggers supplement the standard capabilities of your database to provide a highly customized database management system. For example, you can use triggers to:
•Automatically generate derived column values
•Enforce referential integrity across nodes in a distributed database