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

>
>
> >
> > 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');
>
> So essentially, what happens is this:
>
> 1. forcing the generator (microarray_id_gen) to throw out

incremental

> number by using SELECT gen_id(microarray_id_gen, 1) from

RDB$DATABASE;

> statement.
> 2. use the number (from the select) in the insert SQL statement for

my

> database.
>
> This also means that the trigger, create_microarray_id for
> microarray_main, is a fall back position?
>

Precisely.

It still acts as an autoinc field would normally act when you do not
provide an id, so there should be no backwards compatibility issue.
Of course it is two (albiet quick) round trips to the database
server, so the FB 2 syntax will prove very useful, but you have to
wait for that.

Adam