Hi Adomas!

None of your three suggestions are normally useable. Your trigger
should contain code like

if NEW.FieldName IS NULL then
NEW.FieldName = GEN_ID(MyTriggerName, 1)

and then your stored procedure should do

MyVariable = GENID(MyTriggerName, 1)

Then you should assign MyVariable to both Primary_table and
Foreign_table.

If it is important that there is an unbroken sequence of numbers, take
a look at the document
http://www.ibobjects.com/TechInfo.html#ti_AuditableSeries

HTH,
Set

--- In firebird-support@yahoogroups.com, Adomas Urbanavicius 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.