Subject Re: [firebird-support] Duplicate entries ...
Author Woody
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

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