--- In firebird-support@yahoogroups.com, Maurice Ling <beldin79@y...>
wrote:

>
> Hi,
>
> In my database, I am using a generator to generate a unique ID for

one

> of my tables and I need to get this ID for input into other tables,

is

> there a way to do it? That is, getting the last generated ID.
>
> A sample of my DDL is here,
>
> create table microarray_main(
> microarrayid numeric (5,0) not null primary key,
> description varchar(1024) not null,
> submitter char(50) default 'system' not null);
>
> create generator microarray_id_gen;
> set generator microarray_id_gen to 10000;
>
> set term !! ; ;
> create trigger create_microarray_id for microarray_main
> before insert position 0
> as begin
> new.microarrayid = gen_id(microarray_id_gen, 1);
> end !!
> set term ; !! !!
>
> When I do an insert into microarray_main table, I need to get the
> generated ID (microarrayid) for subsequent work, is there any

simple way

> to do that?
>
> Thanks
> Maurice

In FB 2 yes.

In FB 1.5 and earlier you need to reverse the logic.

Firstly, change your trigger to this.

set term !! ;
create trigger create_microarray_id for microarray_main
before insert position 0
as begin
if (new.microarrayid is null) then
begin
new.microarrayid = gen_id(microarray_id_gen, 1);
end
end !!
set term ; !!

Then you are only assigning a number if one is not provided.

Secondly run this query

SELECT gen_id(microarray_id_gen, 1)
from RDB$DATABASE;

The number you get back store in your variable and use it in your
insert statement.

INSERT INTO MICROARRAY_MAIN (MICROARRAYID, BLAH) VALUES
(:ThatNumberWeJustQueried, 'blah');

etc

Alternatively you could do a similar thing using a stored procedure.

Instead of allocating a new record number then looking up what it is,
get the number that will be allocated and use it.

Adam