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 BEGIN
and 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
IF
…THEN
,WHILE
…DO
, andFOR
SELECT
…DO
, to perform conditional or looping tasks. EXECUTE PROCEDURE
statements to invoke stored procedures.- Exception statements, to return error messages, and
WHEN
statements, to handle specific error conditions. NEW
andOLD
context 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.
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
, and NEW
. The 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 INSERT
or 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:
NEW.column
OLD.column
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 UPDATE
or INSERT
.
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 SALARY_HISTORY
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 ;
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.
Use the 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:
GEN_ID(genname, step)
<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 CUSTOMER
table:
CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
BEFORE INSERT AS
BEGIN
NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
END ;
This trigger must be defined to fire before the insert, since it assigns values to
NEW
.CUST_NO
.