Using Generators
Go Up to Working with Generators
Once a generator has been created using the CREATE GENERATOR statement, it exists within the database but no numbers have actually been generated. To invoke the number generator, you must call the InterBase GEN_ID() function. GEN_ID() takes two arguments: the name of the generator to call, which must already be defined for the database, and a step value, indicating the amount by which the current value should be incremented (or decremented, if the value is negative). GEN_ID() can be called from within a trigger, a stored procedure, or an application whenever an INSERT, UPDATE, or DELETE operation occurs. Applications can also use GEN_ID() with SELECT statements to obtain a generator value for inclusion as part of an INSERT statement.
The syntax for GEN_ID() is:
GEN_ID(genname, step);
To generate a number, follow these steps:
- Create the generator.
- Within a trigger, stored procedure, or application, reference the generator with a call to
GEN_ID(). - The generator returns a value when a trigger fires, or when a stored procedure or application executes. It is up to the trigger, stored procedure, or application to use the value. For example, a trigger can insert the value into a column.
To stop inserting a generated number in a database column, delete or modify the trigger, stored procedure, or application so that it no longer invokes GEN_ID().
Generators return a 64-bit value. You should define the column that holds the generated value as an
ISC_INT64 variable with a DECIMAL or NUMERIC data type.Example: The following statement uses GEN_ID()to call the generator G to increment a purchase order number in the SALES table by one:
INSERT INTO SALES (PO_NUMBER) VALUES (GEN_ID(G,1));
For more information on using generators in triggers, see Working with Triggers. For more information on using generators in stored procedures, see Working with Stored Procedures.