--- In firebird-support@yahoogroups.com, "Nick Upson" <nick.upson@...>
wrote:

>
> select gen_id (gen_test, -1 * gen_id(gen_test, 0)) from rdb$database
> into :countid;

I am working on a somewhat similar problem, in which I am trying to
get an ID from a generator, the name of which is dynamically
generated. Using clues from this and one other list entry, I've got
it working. But I would like to understand better why it works.

I have written a trigger that looks something like this:

SET TERM ^ ;
CREATE TRIGGER GET_ID FOR JOBS ACTIVE
BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE NICK VARCHAR(16);
DECLARE VARIABLE B INTEGER;
DECLARE VARIABLE SQL VARCHAR(1024);
BEGIN
/* get a generator name based on design's nickname */
SELECT NICKNAME FROM DESIGNS
WHERE DESIGN.ID = NEW.ID
INTO :NICK;
SQL = 'SELECT GEN_ID(D_' || NICK || '_ID,1) FROM rdb$database';
EXECUTE STATEMENT SQL INTO :B;
NEW.BATCHNO = B;
END^
SET TERM ; ^

Until I added the clause 'FROM rdb$database', I was getting errors
regarding an 'Unexpected end of command'. What role does this clause
play?