CREATE TRIGGER

A trigger defines a set of actions that are executed when a delete, insert, or update operation is performed on a table. 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.

Syntax

CREATE TRIGGER trigger-name
{ AFTER | NO CASCADE BEFORE } 
{ INSERT | DELETE | UPDATE [ OF column-name [, column-name]* ] }
ON table-name
[ ReferencingClause ]
[ FOR EACH ROW ] [ MODE DB2SQL ] 
triggered-sql-statement

Description

Note: Statement triggers are not supported. Trigger recursion is not supported.

In addition to providing constraints, triggers can help enforce data integrity rules with actions such as 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. 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.

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.

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 and After Triggers

Triggers can be defined as either Before or After triggers using the NO CASCADE BEFORE or AFTER clauses.

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. After triggers fire after all constraints have been satisfied and after the changes have been applied to the target table.

Trigger Events

You can define a trigger to occur in response to any of these DML events:
  • INSERT
  • UPDATE
  • DELETE

You can define any number of triggers for a given event on a given table. With update events, you can specify columns.

ReferencingClause

The triggered-sql-statement may need to reference data that is being changed by the database event that fired the trigger. The triggered-sql-statement may also need to refer to the new (post-change or "after") values.

Changed data can be referenced in the triggered-sql-statement using transition variables. The referencing clause enables you to provide a correlation name or alias for these transition variables by specifying REFERENCING OLD AS correlation-name or REFERENCING 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 the 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 can use transition variables. INSERT row triggers cannot reference an OLD row, and DELETE row triggers cannot reference a NEW row.

The ReferencingClause can designate only one new correlation or identifier and only one old correlation or identifier.

A row trigger fires once for each row affected by the triggering event. If no rows are affected, the trigger does not fire.

triggered-sql-statement

The action defined by the trigger is called the triggered-sql-statement. This statement 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 triggered-sql-statement can reference database objects other than the table where the trigger is declared. If any of these database objects is dropped, the trigger is invalidated. If the trigger cannot be recompiled on the next execution, the invocation throws an exception and the statement that caused the trigger to fire is rolled back.

Example

This trigger is an after trigger on created dom.flights. For every UPDATE event made on dom.flights, the trigger inserts a row in dom.flights_history:

CREATE TRIGGER trig1 
     AFTER UPDATE ON dom.flights REFERENCING OLD AS UPDATEDROW
     FOR EACH ROW MODE DB2SQL
     INSERT INTO dom.flights_history VALUES(UPDATEDROW.FLIGHT_ID, UPDATEDROW.AIRCRAFT, 'INSERTED FROM trig1');
The following trigger fires after a DELETE event:
CREATE TRIGGER trig2
  AFTER DELETE ON flights
  REFERENCING OLD AS OLD
  FOR EACH ROW 
  DELETE FROM flightavailability WHERE FLIGHT_ID = OLD.FLIGHT_ID;
To avoid recursive calls to a trigger, you can use a table column as a flag to stop triggering further events. For example:
CREATE TRIGGER trig3
AFTER UPDATE OF the_flag ON test_table
REFERENCING NEW AS updatedRow
FOR EACH ROW
UPDATE test_table SET edited_by = 'fromUpdateTrigger', edited_date = CURRENT_DATE WHERE the_data=updatedRow.the_data AND
(edited_by <> 'fromUpdateTrigger' or edited_by IS NULL);