GEN ID( )
Go Up to Statement and Function Reference (Language Reference Guide)
Produces a system-generated integer value. Available in gpre
, DSQL, and isql
.
gen_id (generator, step)
Argument | Description |
---|---|
<generator> |
Name of an existing generator |
<step> |
Integer or expression specifying the increment for increasing or decreasing the current generator value. Values can range from –(263) to 263 – 1 |
Description: The GEN_ID()
function:
- Increments the current value of the specified generator by step.
- Returns the new value of the specified generator.
GEN_ID()
is useful for automatically producing unique values that can be inserted into a UNIQUE
or PRIMARY KEY
column. To insert a generated number in a column, write a trigger, procedure, or SQL statement that calls GEN_ID()
.
- Note: A generator is initially created with CREATE GENERATOR. By default, the value of a generator begins at zero. It can be set to a different value with SET GENERATOR.
Examples: The following isql
trigger definition includes a call to GEN_ID()
:
CREATE TRIGGER CREATE_EMPNO FOR EMPLOYEES
BEFORE INSERT
POSITION 0
AS BEGIN
NEW.EMPNO = GEN_ID (EMPNO_GEN, 1);
END
The first time the trigger fires, NEW.EMPNO
is set to 1. Each subsequent firing increments NEW.EMPNO
by 1.