--- In firebird-support@yahoogroups.com, Adomas Urbanavicius
<adomas@i...> wrote:

>
> Hi,
> Is there any posibility to find out inserted ID in the middle of sql
> script ? It has to auto create relationships (One - Many) from :
> Something like sql script:
> insert values into Primary_table (PK,data ....); //// PK - auto
> incremented by trigger.
> insert values into Foreign_table (FK,...) /// ---------> HOW to

find

> out FK, from previous SQL line ?
> insert values into Foreign_table (FK) /// --------->
>
>
>
> Sample :
> TABLE1 ID*, NAME Primary key ID. (with auto inc triger with
> generator. of ID)
> TABLE2 ID,CITY. Id - foreign key from TABLE1.
>
> Now if we want to run sql script run :
> insert into TABLE1(NAMES) values ('John');
> ///// HOW to auto build relationship with fetching last ID from table1
> insert into TABLE2 (ID,CITY) values (/* ID ???? */,'City1');
> insert into TABLE2 (ID,CITY) values (/* ID ???? */,'City2');
> COMMIT WORK;
> ...
>
> As far, as I know:
> 1. Create SP to handle it with local variable.
> 2. Select gen_id(my_gen,0) from rdb$database
> 3. Select max(id)
> But 1 is not reusable, as for other tables SP has to be rewritten. (And
> it has to exist before script is executed.)
> 2 is transaction independant, so might be changed by other concurrent
> users in the middle of action.
> 3 for me is dangerous, if transactions are read_commited, it might be
> different after other user commits.
>

Adomas,

Of your suggestions, the only multi-user "safe" method is the SP, 2
and 3 both have the same problem.


We either use a SP, or do it using queries. You just have to fix up
the order a bit.

Rather than inserting a value, then saying to yourself, "what on earth
did I just insert?", think ahead and say "I need to insert a record,
but I need to know the generated value because I need to insert some
other records with a foreign key link to this record I am inserting"

So simply get the generated value first

Select gen_id(my_gen,1) from rdb$database

Store that number in a variable, and use it in the respective insert
statements.

insert into mastertable (ID, name) values (:MyVariable, :TheName);
insert into detailtable (ID, MasterTableID, SomethingElse) values
(gen_id(my_detailgen,1), :MyVariable, ....);

Pretty simple. Obviously this approach is an additional "round trip"
to the database, but it will be a very quick trip. The insert into ...
returning syntax isn't in 1.5, so the only single trip approach is a SP.

Adam