Is there a safe way to get the last id given by a generator? How do you use
generators when you have to insert data into 1 table and then in the same
transaction insert further data using the id from the first?

For example say I have a table for inventory items like this:

create table item (
item_id integer not null primary key,
number varchar(14) not null,
description varchar(30)
);

And a table for taxes like this:

create table tax (
tax_id integer not null primary key,
name varchar(10) not null,
percent numeric(10,2)
);

And you link items with taxes in a table like this:

create table item_tax (
item_id integer not null,
tax_id integer not null,
foreign key (item_id) references item (item_id) on delete cascade,
foreign key (tax_id) references tax (tax_id) on delete cascade
);

Now that I have that I want to create an item and link its taxes to it all
atomically. In the code I want to do one insert to create the item and then
a set of inserts in item_tax using the item_id from the insert. It has to be
all in one transaction so that if the item_tax insert fails, the item without
taxes isn't left hanging around. So something like this:

insert into item values (gen_id(item_gen,1), '1000', 'Test');
insert into item_tax values (last_id(item_gen), 123);
insert into item_tax values (last_id(item_gen), 456);

In this example 123 and 456 are ids assigned to taxes which were created
sometime in the past. I need the last_id function to return the value I got
in this transaction regardless of whether some other user happens to also
create an item just after the first insert thus bumping up the last_id. Is
there a function like last_id to go along with gen_id?

Any ideas on how to best accomplish this using Interbase would be
appreciated! Perhaps I've designed things poorly?

As an added thing to consider, I would like to think about how to assign the
ids automatically using triggers. I would like to create a trigger on the
item table that gets a new generator id on every insert and a trigger on
item_tax that uses the last item generator for any insert. Is this possible?
The reason is I would like to be able to import and export data as SQL
commands but to do so, I've got to deal with ids. If the trigger handled
everything then to import the above item would just be:

insert into item values (null, '1000', 'Test');
insert into item_tax values (null, 123);
insert into item_tax values (null, 456);

This still has the problem of referencing the tax ids 123 and 456 where I
would rather for exported data reference everything by something other than
the internal ids. Surrogate keys ids are great for things internally but I'm
finding them a bit of a pain for import/export type functions! Perhaps I
just have to give up using SQL for import/export and instead write comma
delimited files and then handle the logic of ids internally...

--
Brad Pepers
brad@...