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
database owner
can also create triggers on any table in any schema.
A trigger operates with the privileges of the owner of the trigger. See
"Using SQL standard authorization" and "Privileges on views, triggers, and
constraints" in the Derby Developer's Guide for
details.
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.
Before or after: when triggers fire
Triggers are
defined as either
Before or
After triggers.
- Before triggers fire before the statement's changes are applied
and before any constraints have been applied. Before triggers can be either
row or statement triggers (see Statement versus row triggers).
- After triggers fire after all constraints have been satisfied and
after the changes have been applied to the target table. After triggers
can be either row or statement triggers (see Statement versus row triggers).
Insert, delete, or update: what causes the trigger to fire
A
trigger is fired by one of the following database events, depending on how
you define it (see
Syntax above):
You can define any number of triggers for a given event on a
given table. For update, you can specify columns.
Referencing old and new values: the referencing
clause
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.
Derby provides
you with a number of ways to refer to data that is currently being changed
by the database event that caused the trigger to fire. Changed data can be
referred to in the triggered-SQL-statement using transition variables or transition
tables. The referencing clause allows you to provide a correlation name
or alias for these transition variables by specifying OLD/NEW AS correlation-Name .
For
example, if you add the following clause to the trigger definition:
REFERENCING OLD AS DELETEDROW
you
can then refer to this correlation name in the triggered-SQL-statement:
DELETE FROM HotelAvailability WHERE hotel_id = DELETEDROW.hotel_id
The OLD and NEW transition variables map to a
java.sql.ResultSet with
a single row.
Note: Only row triggers (see
Statement versus row triggers)
can use the transition variables. INSERT row triggers cannot reference an
OLD row. DELETE row triggers cannot reference a NEW 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:
REFERENCING OLD_TABLE AS DeletedHotels
allows
you to use that new identifier (
DeletedHotels) in the triggered-SQL-statement:
DELETE FROM HotelAvailability WHERE hotel_id IN
(SELECT hotel_id FROM DeletedHotels)
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.
Note: Only
statement triggers (see
Statement versus row triggers)
can use the transition tables. INSERT statement triggers cannot reference
an OLD table. DELETE statement triggers cannot reference a NEW table.
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.
Statement versus row triggers
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.
- statement triggers
A statement trigger fires once per triggering
event and regardless of whether any rows are modified by the insert, update,
or delete event.
- row triggers
A row trigger fires once for each row affected
by the triggering event. If no rows are affected, the trigger does not fire.
Note: An update that sets a column value to the value that it originally
contained (for example, UPDATE T SET C = C) causes a row trigger to fire,
even though the value of the column is the same as it was prior to the triggering
event.
Triggered-SQL-statement
The action
defined by the trigger is called the triggered-SQL-statement (in
Syntax above,
see the last line). It has the following limitations:
- It must not contain any dynamic parameters (?).
- It must not create, alter, or drop the table upon which the trigger is
defined.
- It must not add an index to or remove an index from the table on which
the trigger is defined.
- It must not add a trigger to or drop a trigger from the table upon which
the trigger is defined.
- It must not commit or roll back the current transaction or change the
isolation level.
- Before triggers cannot have INSERT, UPDATE or DELETE statements as their
action.
- Before triggers cannot call procedures that modify SQL data as their action.
- The NEW variable of a Before trigger cannot reference a generated column.
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 Derby Developer's Guide.
Order of execution
When a database event occurs
that fires a trigger,
Derby performs
actions in this order:
- It fires No Cascade Before triggers.
- It performs constraint checking (primary key, unique key, foreign key,
check).
- It performs the insert, update, or delete.
- It fires After triggers.
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.
-- Statements and triggers:
CREATE TRIGGER t1 NO CASCADE BEFORE UPDATE ON x
FOR EACH ROW MODE DB2SQL
values app.notifyEmail('Jerry', 'Table x is about to be updated');
CREATE TRIGGER FLIGHTSDELETE
AFTER DELETE ON FLIGHTS
REFERENCING OLD_TABLE AS DELETEDFLIGHTS
FOR EACH STATEMENT
DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN
(SELECT FLIGHT_ID FROM DELETEDFLIGHTS);
CREATE TRIGGER FLIGHTSDELETE3
AFTER DELETE ON FLIGHTS
REFERENCING OLD AS OLD
FOR EACH ROW
DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;
Note: You can find more examples in the Derby Developer's Guide.
Trigger recursion
The maximum trigger recursion
depth is 16.
Related information
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:
