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