From: "Lester Caine" <[email protected]> > 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
