Getting new Primary key to use in second table

I am writing an asp.net form that inserts data into two firebird tables. The form lists the infomation to be inserted into both tables The problem is I need the prmary key from the first table which is an auto gnerated by a generator and trigger to use in the second table.

The first table is a table that list incident details.
field include
incident_id primary key (auto generated )
date
remaining fields

The second table is a table that that list persons involved
fields include
incident_id
person_id


The form list all the information So what I'm trying to do is have the query insert the incident details in the first table, then take the primary key from the new record and insert it into the 2nd table along with the person id.

Any ideas?

Thanks,

Fr. Robert

I don't use triggers in my db for getting an autonumber field, what I do is create a generator for my table...

CREATE TABLE REGIONS(
REGION_ID INTEGER DEFAULT 0 NOT NULL,
REGION_NAME VARCHAR(60) DEFAULT '' NOT NULL,
primary key(REGION_ID));
CREATE INDEX iREGION ON REGIONS (REGION_NAME);
CREATE GENERATOR REGION_ID_GEN;

Then in my app I execute the follow SQL to get the next available REGION_ID and store it in a variable:

SELECT gen_id(REGION_ID, 1) AS NEXT_ID FROM RDB$DATABASE;

I will then use "NEXT_ID" and insert it into my REGIONS table and it is also available for other tables as well.

Not the answer you are looking for, but another way to accomplish the same thing.

Paul

Yes, that is the way

--
Best regards,
Fikret Hasovic http://fikret.fbtalk.net
USAID TAMP Senior Programmer
* FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation
* JEDI VCS contributor
http://jedivcs.sourceforge.net/
* Firebird and Fyracle news
http://www.fyracle.org/shownews.php

Thanks,

It worked

I'm getting the same result by doing a commit retaining in the first table and then asking for the ID when I need to do a insert in the second one.