CREATE TRIGGER

The CREATE TRIGGER statement creates a new trigger in a database. Triggers are a type of stored procedure that is automatically executed when table data is modified with an INSERT, UPDATE, or DELETE.

Unlike a regular stored procedure, a trigger cannot be executed directly, nor can it have parameters. Triggers do not return a result set, nor can they be defined on views.

Syntax

CREATE TRIGGER trigger-name before-or-after ins-upd-del ON table-name

[ ORDER number ]

[ REFERENCING referencing-alias ] FOR EACH ROW

[ WHEN proc-search-condition ] proc-stmt

trigger-name ::= user-defined-name

before-or-after ::= BEFORE | AFTER

ins-upd-del ::= INSERT | UPDATE | DELETE

referencing-alias ::= OLD [ AS ] correlation-name [ NEW [ AS ] correlation-name ]

| NEW [ AS ] correlation-name [ OLD [ AS ] correlation-name ]

correlation-name ::= user-defined-name

Remarks

Note In a trigger, the name of a variable must begin with a colon (:).

OLD (OLD correlation-name) and NEW (NEW correlation-name) can be used inside triggers, not in a regular stored procedure.

In a DELETE or UPDATE trigger, the letters “OLD” or an OLD correlation-name must be prepended to a column name to reference a column in the row of data prior to the update or delete operation.

In an INSERT or UPDATE trigger, the letters “NEW” or a NEW correlation-name must be prepended to a column name to reference a column in the row about to be inserted or updated.

Trigger names must be unique in the dictionary.

Triggers are executed either before or after an UPDATE, INSERT, or DELETE statement is executed, depending on the type of trigger.

Note CREATE TRIGGER statements are subject to the same length and other limitations as CREATE PROCEDURE. For more information, see Limits and Data Type Restrictions.

Examples

The following example creates a trigger that records any new values inserted into the Tuition table into TuitionIDTable.

CREATE TABLE Tuitionidtable (primary key(id), id ubigint);

CREATE TRIGGER InsTrig

BEFORE INSERT ON Tuition

REFERENCING NEW AS Indata

FOR EACH ROW

INSERT INTO Tuitionidtable VALUES(Indata.ID);

An INSERT on Tuition calls the trigger.

============

The following example shows how to keep two tables, A and B, synchronized with triggers. Both tables have the same structure.

CREATE TABLE A (col1 INTEGER, col2 CHAR(10));

CREATE TABLE B (col1 INTEGER, col2 CHAR(10));

CREATE TRIGGER MyInsert

AFTER INSERT ON A FOR EACH ROW

INSERT INTO B VALUES (NEW.col1, NEW.col2);

CREATE TRIGGER MyDelete

AFTER DELETE ON A FOR EACH ROW

DELETE FROM B WHERE B.col1 = OLD.col1 AND B.col2 = OLD.col2;

CREATE TRIGGER MyUpdate

AFTER UPDATE ON A FOR EACH ROW

UPDATE B SET col1 = NEW.col1, col2 = NEW.col2 WHERE B.col1 = OLD.col1 AND B.col2 = OLD.col2;

Note that OLD and NEW in the example keep the tables synchronized only if table A is altered with nonpositional SQL statements. If the SQLSetPOS API or a positioned update or delete is used, then the tables stay synchronized only if table A does not contain any duplicate records. A SQL statement cannot be constructed to alter one record but leave another duplicate record unaltered.