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, andFORSELECT…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. NEWandOLDcontext 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.