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
A trigger operates with the privileges of the owner of the trigger. See
"Configuring fine-grained user authorization" and "Privileges on views,
triggers, constraints, and generated columns" in 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 triggeredSQLStatements need to refer to data that is currently being changed by the database event that caused them to fire. The triggeredSQLStatement 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 triggeredSQLStatement:
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 triggeredSQLStatement 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 correlationName
For example:
allows
you to use that new identifier (DeletedHotels) in the triggeredSQLStatement:
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.
The transition tables or transition variables defined in the REFERENCING clause can be referenced from the WHEN clause.
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 triggeredSQLStatement (in
For more information on triggeredSQLStatements, see "Programming
trigger actions" in 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: