[All]
How to reset a generator.
By: Ben Matterson
Abstract: A breif description of how to use the GEN_ID function to reset a generator. Plus a link to a document describing how to use the SET GENERATOR statement to do the same,
There are two ways to reset a generator.
The first method is to use the
SET GENERATOR statement, however this cannot be done from
within a trigger or stored procedure.
The second method, which can be used from within a stored
procedure or trigger, is to use the GEN_ID function.
The GEN_ID function changes the value of the named generator (the
first parameter) by the increment value (the second parameter)
and returns the new value.
For example, assume the existence of a generator named "my_generator".
If the current value of the generator is 100 and the following
call to GEN_ID is made,
GEN_ID(my_generator, 1)
the current value of my_generator, 100, is added to the
increment value, 1, and the result, 101, is both returned
and stored in the generator.
Similarly if the current value of my_generator is 765 and
the following call is made to GEN_ID
GEN_ID(my_generator, -765)
then the current value of the generator and the value
GEN_ID returns are now zero because 765 + (-765) = 0.
The following line of code sets my_generator back to zero,
no matter what its current value, by incrementing the
current value by the negative current value. Like this:
GEN_ID(my_generator, -(GEN_ID(my_generator, 0)))
A generator may be reset to any desired value using this
same technique.
Connect with Us