CREATE TRIGGER

Go Up to Statement and Function Reference (Language Reference Guide)

Creates a trigger, including when it fires, and what actions it performs. Available in DSQL, and isql.

CREATE TRIGGER name FOR table
[ACTIVE | INACTIVE]
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE}
[POSITION number]
AS trigger_body ;

trigger_body = [variable_declaration_list] block
variable_declaration_list =
DECLARE VARIABLE variable data_type;
[DECLARE VARIABLE variable data_type; …]
block =
BEGIN
compound_statement
[compound_statement …]
END
data_type = SMALLINT
| INTEGER
| FLOAT
| DOUBLE PRECISION
| {DECIMAL | NUMERIC} [(precision [, scale])]
| {DATE | TIME | TIMESTAMP)
| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR}
[(int)] [CHARACTER SET charname]
| {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(int)]
| BOOLEAN
compound_statement = block | statement;

Argument	Description
<name>	Name of the trigger; must be unique in the database.
<table>	Name of the table or view that causes the trigger to fire when the specified operation occurs on the table or view.
ACTIVE|INACTIVE	Optional. Specifies trigger action at transaction end: ACTIVE: [Default] Trigger takes effect. INACTIVE: Trigger does not take effect.
BEFORE|AFTER	Required. Specifies whether the trigger fires: BEFORE: Before the associated operation. AFTER: After the associated operation. Associated operations are DELETE, INSERT, or UPDATE.
DELETE|INSERT|UPDATE	Specifies the table operation that causes the trigger to fire.
POSITION <number>	Specifies the firing order for triggers before the same action or after the same action; <number> must be an integer between 0 and 32,767, inclusive. Lower-number triggers fire first. Default: 0 = first trigger to fire. Triggers for a table need not be consecutive; triggers on the same action with the same position number will fire in random order.
DECLARE VARIABLE	Declares local variables used only in the trigger. Each declaration must be preceded by DECLARE VARIABLE and followed by a semicolon (;). : Local variable name, unique in the trigger. <data_type>: The data type of the local variable.
<statement>	Any single statement in InterBase procedure and trigger language; each statement except BEGIN and END must be followed by a semicolon (;).

Description: CREATE TRIGGER defines a new trigger to a database. A trigger is a self-contained program associated with a table or view that automatically performs an action when a row in the table or view is inserted, updated, or deleted.

A trigger is never called directly. Instead, when an application or user attempts to INSERT, UPDATE, or DELETE a row in a table, any triggers associated with that table and operation automatically execute, or fire. Triggers defined for UPDATE on non-updatable views fire even if no update occurs.

A trigger is composed of a header and a body.

The trigger header contains:

• A trigger name, unique within the database, that distinguishes the trigger from all others.
• A table name, identifying the table with which to associate the trigger.
• Statements that determine when the trigger fires.

The trigger body contains:

• An optional list of local variables and their data types.
• A block of statements in InterBase procedure and trigger language, bracketed by BEGIN and END. These statements are performed when the trigger fires. A block can itself include other blocks, so that there may be many levels of nesting.

A trigger is associated with a table. The table owner and any user granted privileges to the table automatically have rights to execute associated triggers.

Triggers can be granted privileges on tables, just as users or procedures can be granted privileges. Use the GRANT statement, but instead of using TO <username>, use TO TRIGGER <trigger_nam>e. Triggers privileges can be revoked similarly using REVOKE.

When a user performs an action that fires a trigger, the trigger will have privileges to perform its actions if one of the following conditions is true:

• The trigger has privileges for the action.
• The user has privileges for the action.

InterBase procedure and trigger language is a complete programming language for stored procedures and triggers. It includes:

• SQL data manipulation statements: INSERT, UPDATE, DELETE, and singleton SELECT.
• SQL operators and expressions, including generators and UDFs that are linked with the calling application.
• Powerful extensions to SQL, including assignment statements, control-flow statements, context variables, event-posting statements, exceptions, and error-handling statements.

The following table summarizes language extensions for triggers. For a complete description of each statement, see Procedures and Triggers.

Language extensions for triggers

Statement	Description
BEGIN…END	Defines a block of statements that executes as one. The BEGIN keyword starts the block; the END keyword terminates it. Neither should it be followed by a semicolon.
<variable> = <expression>	Assignment statement that assigns the value of <expression> to <variable>, a local variable, input parameter, or output parameter.
/* <comment_text> */	Programmer’s comment, where <comment_text> can be any number of lines of text.
EXCEPTION <exception_name>	Raises the named exception; an exception is a user-defined error that returns an error message to the calling application unless handled by a WHEN statement.
EXECUTE PROCEDURE < proc_name> [ [, …]] [RETURNING_VALUES [, …]]	Executes the stored procedure, <proc_name>, with the listed input arguments. Returns values in the listed output arguments following RETURNING_VALUES. Input and output arguments must be local variables.
EXIT	Jumps to the final END statement in the procedure.
FOR <select_statement> DO <compound_statement>	Repeats the statement or block following DO for every qualifying row retrieved by <select_statement>.
<select_statement>	A normal SELECT statement.
<compound_statement>	Either a single statement in procedure and trigger language or a block of statements bracketed by BEGIN and END.
IF (condition) THEN compound_statement [ELSE compound_statement]	Tests <condition>, and if it is TRUE, performs the statement or block following THEN; otherwise, performs the statement or block following ELSE, if present.
<condition>	A Boolean expression (TRUE, FALSE, or UNKNOWN), generally two expressions as operands of a comparison operator.
NEW.<column>	New context variable that indicates a new column value in an INSERT or UPDATE operation.
OLD.<column>	Old context variable that indicates a column value before an UPDATE or DELETE operation.
POST_EVENT <event_name> | <col>	Posts the event, <event_name>, or uses the value in <col> as an event name.
WHILE (<condition>) DO <compound_statement>	While condition is TRUE, keep performing <compound_statement>. Tests <condition>, and performs <compound_statement> if <condition> is TRUE. Repeats this sequence until <condition> is no longer TRUE.
WHEN {<error> [, <error> …] | ANY} DO <compound_statement>	Error-handling statement. When one of the specified errors occurs, performs <compound_statement>. WHEN statements, if present, must come at the end of a block, just before END. ANY: Handles any errors
<error>	EXCEPTION <exception_name>, SQLCODE <errcode> or GDSCODE errcode

The stored procedure and trigger language does not include many of the statement types available in DSQL or gpre. The following statement types are not supported in triggers or stored procedures:

• Data definition language statements: CREATE, ALTER, DROP, DECLARE EXTERNAL FUNCTION, and DECLARE FILTER
• Transaction control statements: SET TRANSACTION, COMMIT, ROLLBACK
• Dynamic SQL statements: PREPARE, DESCRIBE, EXECUTE
• CONNECT/DISCONNECT, and sending SQL statements to another database
• GRANT/REVOKE
• SET GENERATOR
• EVENT INIT/WAIT
• BEGIN/END DECLARE SECTION
• BASED ON
• WHENEVER
• DECLARE CURSOR
• OPEN
• FETCH

Examples: The following trigger, SAVE_SALARY_CHANGE, makes correlated updates to the SALARY_HISTORY table when a change is made to an employee’s salary in the EMPLOYEE table:

CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
AFTER UPDATE AS
BEGIN
IF (OLD.SALARY <> NEW.SALARY) THEN
INSERT INTO SALARY_HISTORY
(EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY, PERCENT_CHANGE)
VALUES (OLD.EMP_NO, 'now', USER, OLD.SALARY,
(NEW.SALARY - OLD.SALARY) * 100 / OLD.SALARY);
END ;

The following trigger, SET_CUST_NO, uses a generator to create unique customer numbers when a new customer record is inserted in the CUSTOMER table.

CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
BEFORE INSERT AS
BEGIN
NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
END ;

The following trigger, POST_NEW_ORDER, posts an event named “new_order” whenever a new record is inserted in the SALES table.

CREATE TRIGGER POST_NEW_ORDER FOR SALES
AFTER INSERT AS
BEGIN
POST_EVENT 'new_order';
END ;

The following four fragments of trigger headers demonstrate how the ­POSITION option determines trigger firing order:

CREATE TRIGGER A FOR accounts
BEFORE UPDATE
POSITION 5 … /*Trigger body follows*/
CREATE TRIGGER B FOR accounts
BEFORE UPDATE
POSITION 0 … /*Trigger body follows*/
CREATE TRIGGER C FOR accounts
AFTER UPDATE
POSITION 5 … /*Trigger body follows*/
CREATE TRIGGER D FOR accounts
AFTER UPDATE
POSITION 3 … /*Trigger body follows*/

When this update takes place:

UPDATE accounts SET account_status = 'on_hold'
WHERE account_balance < 0;

The triggers fire in this order:

1. Trigger B fires.
2. Trigger A fires.
3. The update occurs.
4. Trigger D fires.
5. Trigger C fires.

See Also

• ALTER EXCEPTION
• ALTER TRIGGER
• CREATE EXCEPTION
• CREATE PROCEDURE
• DROP EXCEPTION
• DROP TRIGGER
• EXECUTE PROCEDURE
• Data Definition Guide
• Procedures and Triggers

Advance To:

• CREATE USER