From: "Lester Caine" <lester@...>

> I had a bit of trouble on a site yesterday which I'm not sure I understand
> ...
>
> The raw data is basically two tables, one with an entry using a generator
> value
> key, and the second 'detail table' has a primary key of the same generator
> value
> + a transact_no. The detail table is populated by a trigger on the master
> table,
> when one field changes, the current state is stored in detail, and the
> master
> entries updated. The trigger is just using a MAX+1 for the transact_no,
> and has
> the primary key unique index, but in theory a user will be take several
> seconds
> between each action on an individual ticket, and the times confirm that.
>

Generally, the second one to post and commit should get an error if the
primary key is duplicated. However, barring that, when I need something like
this I write a specific routine to insert a new record in an isolated
transaction so it can be posted and committed right away. It would then
return the transact_no, in your case, to the calling routine which would
just position itself to the new item. If an error occurs, it could return a
negative error code indicating what caused it or a generic -1 to indicate
failure.

I do the same thing on a larger scale for table ID's where they need to be
in sequence. I usually create an ID table with 2 fields (ID_Type, Next_ID).
I use a separate transaction to read and set the next ID for whatever I
need. Mainly this is needed when an ID is required when creating a new
record for display, like a new work order, as opposed to an underlying,
non-useful ID where a generator can be used.

I think you've been lucky up until now not running into any problems with
the way you're doing it now. I have dealt with people who leave for lunch or
even for the day without closing a form and saving information right away so
I try to make it as non-interfering as possible so it doesn't cause others
to have problems. It doesn't always work but it gets close. :-)

HTH
Woody