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.

Server Response from: ETNASC04