The Trigger Body
Go Up to Creating Triggers
Everything following the AS keyword in the
CREATE TRIGGER statement forms the procedure body. The body consists of an optional list of local variable declarations followed by a block of statements.
A block is composed of statements in the InterBase procedure and trigger language, bracketed by
END. A block can itself include other blocks, so that there may be many levels of nesting.
InterBase procedure and trigger language includes all standard InterBase SQL statements except data definition and transaction statements, plus statements unique to procedure and trigger language.
Statements unique to InterBase procedure and trigger language include:
- Assignment statements, to set values of local variables.
- Control-flow statements, such as
DO, to perform conditional or looping tasks.
EXECUTE PROCEDUREstatements to invoke stored procedures.
- Exception statements, to return error messages, and
WHENstatements, to handle specific error conditions.
OLDcontext variables, to temporarily hold previous (old) column values and to insert or update (new) values.
- Generators, to generate unique numeric values for use in expressions. Generators can be used in procedures and applications as well as triggers, but they are particularly useful in triggers for inserting unique column values. In read-only databases, generators can return their current value but cannot increment.
- Note: All of these statements (except context variables) can be used in both triggers and stored procedures. For a full description of these statements, see Working with Stored Procedures.
NEW and OLD Context Variables
Triggers can use two context variables,
OLD context variable refers to the current or previous values in a row being updated or deleted.
OLD is not used for inserts.
NEW refers to a new set of
UPDATE values for a row.
NEW is not used for deletes. Context variables are often used to compare the values of a column before and after it is modified.
The syntax for context variables is as follows:
where <column> is any column in the affected row. Context variables can be used anywhere a regular variable can be used.
New values for a row can only be altered before actions. A trigger that fires after
INSERT and tries to assign a value to
NEW.<column> will have no effect. The actual column values are not altered until after the action, so triggers that reference values from their target tables will not see a newly inserted or updated value unless they fire after
For example, the following trigger fires after the
EMPLOYEE table is updated, and compares an employee’s old and new salary. If there is a change in salary, the trigger inserts an entry in the
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 ;
- Note: Context variables are never preceded by a colon, even in SQL statements.
Using Generators in the Trigger Body
In a read-write database, a generator is a database object that automatically increments each time the special function,
GEN_ID(), is called.
- Important: Generators cannot be used in read-only databases.
GEN_ID() can be used in a statement anywhere that a variable can be used. Generators are typically used to ensure that a number inserted into a column is unique, or in sequential order. Generators can be used in procedures and applications as well as in triggers, but they are particularly useful in triggers for inserting unique column values.
CREATE GENERATOR statement the create a generator and
SET GENERATOR to initialize it. If not otherwise initialized, a generator starts with a value of one. For more information about creating and initializing a generator, see
CREATE GENERATOR and
SET GENERATOR in the Language Reference.
A generator must be created with
CREATE GENERATOR before it can be called by
GEN_ID(). The syntax for using
GEN_ID() in a SQL statement is:
<genname> must be the name of an existing generator, and <step> is the amount by which the current value of the generator is incremented. <step> can be an integer or an expression that evaluates to an integer.
The following trigger uses
GEN_ID() to increment a new customer number before values are inserted into the
CREATE TRIGGER SET_CUST_NO FOR CUSTOMER BEFORE INSERT AS BEGIN NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1); END ;
- Note: This trigger must be defined to fire before the insert, since it assigns values to