CREATE TRIGGER

---

The CREATE TRIGGER statement creates a new trigger in a database. Triggers are a type of stored procedure that are automatically executed when data in a table 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

This function is an extension to SQL grammar as documented in the Microsoft ODBC Programmer's Reference and implements a subset of the SQL 3/PSM (Persistent Stored Modules) specification.

---

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 used inside triggers, not in a regular stored procedure.

---

In a DELETE or UPDATE trigger, "OLD" or a 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, "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 non-positional SQL statements. If the ODBC 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. An SQL statement cannot be constructed to alter one record but leave another duplicate record unaltered.

See Also

DROP TRIGGER