Seeing the generator value just inserted.

By: Ben Matterson

Abstract: A breif explanation of how to see the generatro value you just inserted.

Question:

How do I get the generator value I just inserted?

Answer:

Start by creating a stored procedure. Assume you have a generator named MY_GENERATOR.

CREATE PROCEDURE GET_GEN_VAL(INCREMENT INTEGER)
RETURNS (GEN_VAL INTEGER)
AS
 BEGIN
  GEN_VAL=GEN_ID(MY_GENERATOR, INCREMENT);
 END ^

The simplest way to have access to the value is to call this procedure from the client and store the value in a variable. Then you can use the generator value in your INSERT statement, and still have it available for other uses in your code.

If you want to do this on the server, you can use an INSERT trigger to update the record to be inserted with the generator value. The trigger calls GET_GEN_VAL


CREATE TRIGGER SET_GEN_VAL FOR MY_TABLE
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE GEN_VAL INTEGER;
BEGIN
  IF ((NEW.ID IS NULL) OR (NEW.ID = 0)) THEN
    BEGIN
      EXECUTE PROCEDURE GET_GEN_VAL(1) RETURNING_VALUES (:GEN_VAL);
     NEW.ID=GEN_VAL;
    END

If you need the value you just inserted you could modify GET_GEN_VAL to store the generated value in a log table before returning the value. You could store a timestamp along with the value and query for the latest time stamp.

Server Response from: ETNASC04