Hi Helen,

Thanks for the input some questions for you (or anyone else who knows the
answer) are below.

> >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.

Yes, it's a web app, though, and only one piece of code will access the
table. I can synchronize that code so that it avoids the problem. That
creates a huge bottleneck but it's a very lightly loaded app for a small
office.

I'd rather do it right, though, but I don't follow what you are getting at
below. Going back to Roman's original reply I understand that I can get an
auto-incremented value from the generator by using a command like:

SELECT gen_id(my_generator, 1) FROM RDB$DATABASE

But in the code below wouldn't I want the trigger active AFTER insert so
that if I insert the key I get from the code above, the trigger generated
number won't be overwritten by the number being manually inserted?

Thanks for the insights, this has been very helpful and my apologies to the
group for this firebird-java question morphing into a an ib-support
question.

Rick

>
> 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