A trigger defines a set of actions that are executed when a database event occurs on a specified table. A database event is a delete, insert, or update operation. For example, if you define a trigger for a delete on a particular table, the trigger's action occurs whenever someone deletes a row or rows from the table.
Along with constraints, triggers can help enforce data integrity rules with actions such as cascading deletes or updates. Triggers can also perform a variety of functions such as issuing alerts, updating other tables, sending e-mail, and other useful actions.
You can define any number of triggers for a single table, including multiple triggers on the same table for the same event.
You can create a trigger in any schema
where you are the schema owner. To create a trigger on a table that you do
not own, you must be granted the TRIGGER privilege on that table. The
The trigger does not need to reside in the same schema as the table on which the trigger is defined.
If a qualified trigger name is specified, the schema name cannot begin with SYS.
Triggers are defined as either Before or After triggers.
A
trigger is fired by one of the following database events, depending on how
you define it (see
You can define any number of triggers for a given event on a given table. For update, you can specify columns.
Many triggered-SQL-statements need to refer to data that is currently being changed by the database event that caused them to fire. The triggered-SQL-statement might need to refer to the new (post-change or "after") values.
For
example, if you add the following clause to the trigger definition:
you
can then refer to this correlation name in the triggered-SQL-statement:
The OLD and NEW transition variables map to a java.sql.ResultSet with
a single row.
For statement triggers, transition tables serve as a table identifier for the triggered-SQL-statement or the trigger qualification. The referencing clause allows you to provide a correlation name or alias for these transition tables by specifying OLD_TABLE/NEW_TABLE AS correlation-Name
For example:
allows
you to use that new identifier (DeletedHotels) in the triggered-SQL-statement:
The old and
new transition tables map to a java.sql.ResultSet with cardinality
equivalent to the number of rows affected by the triggering event.
The referencing clause can designate only one new correlation or identifier and only one old correlation or identifier. Row triggers cannot designate an identifier for a transition table and statement triggers cannot designate a correlation for transition variables.
You have the option to specify whether a trigger is a statement trigger or a row trigger. If it is not specified in the CREATE TRIGGER statement via FOR EACH clause, then the trigger is a statement trigger by default.
A statement trigger fires once per triggering event and regardless of whether any rows are modified by the insert, update, or delete event.
A row trigger fires once for each row affected by the triggering event. If no rows are affected, the trigger does not fire.
The action
defined by the trigger is called the triggered-SQL-statement (in
The triggered-SQL-statement can reference database objects other than the table upon which the trigger is declared. If any of these database objects is dropped, the trigger is invalidated. If the trigger cannot be successfully recompiled upon the next execution, the invocation throws an exception and the statement that caused it to fire will be rolled back.
For more information
on triggered-SQL-statements, see the
When a database event occurs
that fires a trigger,
When multiple triggers are defined for the same database event for the same table for the same trigger time (before or after), triggers are fired in the order in which they were created.
The maximum trigger recursion depth is 16.
Special system functions that return information about the current time or current user are evaluated when the trigger fires, not when it is created. Such functions include: