Your trigger might be executed 100 times before you run the next query to
get the generator value with your gen_id(generator,0).
Get the value first if you need to keep 'Insert into blah(blah) value blah'
in your package.

select gen_id(generator,1) from <whatever>
insert into blah(id, name, notes, etc) values (<my new generator
value>,'name','notes');

Don't rely on the trigger. It'll result in heartache if/when it starts being
used heavily.

Even this method won't scale very well - it involves two queries.
Put the time in, and build a stored procedure that gets the generator value,
and inserts the record. Only one query, fully transactional, and you're
guaranteed to get the right id back.

Nige

-----Original Message-----
From: hay77772000 [mailto:david.hay@...]
Sent: Tuesday, 29 April 2003 8:58
To: ib-support@yahoogroups.com
Subject: [ib-support] Re: Getting id of last inserted record


Hi Luc,

Yep, understand about generators being outside transactions...but if
I can guarantee the only place the generator is called is in the
insert table trigger, was thinking that this would still work? The
generator will only get incremented in my insert statement within my
transaction?

Or am I missing something?! (quite likely!! ;-) )

Thanks,

David

--- In ib-support@yahoogroups.com, Lucas Franzen <luc@r...> wrote:
>
> David,
>
> > Just wanted to clarify - I'm thinking of creating a transaction
that
> > locks the table for writing, issues the same insert statement
that
> > the programmer passed in (as above) and therefore calls the
trigger
> > to add the id - IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID
> > (GEN_ACCOUNTS_ID, 1).
> >
> > Now, if in the SAME transaction with the table still locked, I
call
> > SELECT GEN_ID(GEN_ATABLE, 0) FROM RDB$DATABASE; to get the id of
the
> > row just inserted, will this be safe?
>
> NO!
> Generators run outside transactions (otherwise they wouldn't be
useful
> in a multiclient environment).
> So every time you tell tzhe database to get a generator value this
will
> run outside any transaction you do.
>
> Using a construct like:
> SELECT GEN_ID ( GEN_NAME, 1 ) FROM RDB$DATABASE
> will get you the next generator value and no commit or rollback
will
> ever set this value forth or back!
>
> It is set by calling it. Period. ;-)
>
> the "FROM RDB$DATABASE" is just a helpful construct, since this
system
> always holds one and just one record.
>
> You can also do sth. like:
> SELECT GEN_ID ( GEN_NAME, 1 ) FROM MYTABLE
>
> which will would be the same like as the PSQL-statement:
> GEN_ID ( GEN_NAME, CT )
>
> where CT would be the number of records within MYTABLE.
>
> But still the newly set generator value will be set and no
rollback on
> any transaction will undo it.
>
>
>
> > This way, I can preserve all the insert calls that already exist,
> > use a trigger to insert the id, and then make the call to be
able to
> > return the id. I realise I can do it the other way - get the id
> > before I add the row and add it to the insert statement - but
this
> > will be a heck of a lot easier in terms of the current code base!
>
> > What do you think?
>
>
> See above.
> You can't do.
> You can get the id just by the described way or by using a stored
proc
> (which is much more coding to do).
>
> Or use IBObjects - there are nice things as GeneratorLinks ... ;-)
>
>
> Preserving all the insert calls are a matter of your transaction
> handling.
> But this handling won't touch the generator (see above) at all.
>
> Never worry about generator values.
>
> Take them as qualifiers, never as quantifiers!
>
>
>
> Luc.



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/