--- In firebird-support@yahoogroups.com, Almond <almond@...> wrote:

>
> Sorry if this is an old question.
>
> If I only have the file name, how can I get the last inserted
> record's primary key which is using generator in a trigger.
>
> I currently read the system table to get the primary key name. Then
> use max() to get the value. But I think this is not good on a
> multi-user system.

I imagine this to be slow and unreliable and I am glad I don't need to
explain why it is a bad idea on a multi-user system.

There are two ways to go about it, one which works on any version and
one that only works on Firebird 2 or higher.

Any version:

Run a query to get the value from the generator before inserting your
record, then explicitly use the returned value in your insert statement.

Your before insert trigger may need to be fixed if it always generates
a value. It should rather look something like.

IF (NEW.ID IS NULL) THEN
BEGIN
NEW.ID = GEN_ID(....);
END

This will prevent it from overriding the ID that you previously
fetched from the generator that you now want to use.

You could even write a stored procedure that returns does something like

CREATE OR ALTER PROCEDURE SP_ADDNAME
(
NAME VARCHAR(10)
)
RETURNS
(
ID BIGINT
)
AS
BEGIN
ID = GEN_ID(GEN_MYID, 1);
INSERT INTO MYTABLE (ID, NAME) VALUES (:ID, :NAME);
END

The second option requires Firebird 2, but is superior because it
removes a round trip for the double query approach, but does not
require a dedicated stored procedure to work.

See page 39 (printed page numbers) in Firebird 2 release notes
regarding the RETURNING clause for insert statements.

Adam