CREATE TRIGGER statement

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.

Syntax

CREATE TRIGGER triggerName
{ AFTER | NO CASCADE BEFORE } 
{ INSERT | DELETE | UPDATE [ OF columnName [ , columnName ]* ] }
ON tableName
[ referencingClause ]
[ FOR EACH { ROW | STATEMENT } ] [ MODE DB2SQL ] 
triggeredSQLStatement

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):

INSERT
UPDATE
DELETE

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 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.

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 triggeredSQLStatement 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 correlationName .

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 triggeredSQLStatement:

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 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:

REFERENCING OLD_TABLE AS DeletedHotels

allows you to use that new identifier (DeletedHotels) in the triggeredSQLStatement:

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.

triggeredSQLStatement

The action defined by the trigger is called the triggeredSQLStatement (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 triggeredSQLStatement 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 triggeredSQLStatements, 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.

Examples

-- 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: