Developer Reference : Data Access Methods : SQL Engine Reference : SQL Syntax Reference : CREATE TRIGGER
 
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.
See Also
DROP TRIGGER