Using Generators in the Trigger Body

Go Up to 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 ;

Note: This trigger must be defined to fire before the insert, since it assigns values to NEW.CUST_NO.