At 10:07 PM 06-08-02 -0400, you wrote:

>Thanks for the input David, AND for all the work you have already done on
>this project.
>
>I can work around this fairly easily in the particular app I'm doing.
>Certain fields are never empty so I can just insert an empty record and the
>generator puts in a new key. Then I can search for the record with the null
>field, read the key, and update the record with the data. Then display the
>key as a confirmation number for the user for future reference.
>
>A little kludgy but it will work, and the speed shouldn't be too bad.
>Fortunately this server insn't heavily loaded. I just have to insure that
>no blank records get inserted if something breaks in mid-process, but that's
>easy enough to scan for periodically.

It's not just a kludge, it is totally dangerous if you have more than one
user inserting rows to the same table concurrently.

The only way to get the value of a generated key into your app in a way
that will allow you to subsequently locate the row you just inserted is to
generate the number FIRST and pass it into your insertsql. If you have an
"autoinc trigger" on this PK, then you must modify the trigger so that it
does a null test before firing, viz.

create trigger...
active before insert
as
begin
if (new.YourPk is null) then
new.YourPk = gen_id(YourGenerator, 1);
end

Trigger generation is outside of transaction control (the only thing that
is!) so it is guaranteed to be 100% atomic.

Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
______________________________________________________________________