Subject Re: [firebird-support] Last_inserted_id, auto creating relationships
Author Ann W. Harrison
Adomas Urbanavicius wrote:
> Hi,
> Is there any posibility to find out inserted ID in the middle of sql
> script ?

Not conveniently in V1.x, but V2 includes a new feature - an extension
to the insert verb that returns values from the inserted row. The
syntax will be something like this:

INSERT INTO <table> (<field list>) VALUES (<value list>) RETURNING
(<field list>)

> As far, as I know:
> 1. Create SP to handle it with local variable.


> 2. Select gen_id(my_gen,0) from rdb$database

As you noticed, this doesn't work in the multi-user case.

> 3. Select max(id)

That's expensive unless you have a descending index, and unreliable for
read committed transactions.