Subject Re: [IBO] Help on stored procedures/trigger
Author Geoff Worboys
> Geoff is a much more skilled IBOer than me and there may well
> be reasons for him not mentioning this possibility. If so, I
> trust he will correct me.

Flattery will get you everywhere :-)

If all you want is to directly reject any attempt at simultaneous
creation, the unique index (/primary-key) mechanism will do it. My
option 4, with the insert into a separate table mechansim, is just a
way to avoid giving the user errors unnecessarily. The main advantage
(and disadvantage) of the approach that you describe is that it forces
the synchronised creation of records.

In one of my applications I generate invoices in batch mode. That is;
the data is input that is required to produce the invoices and then,
when the user is ready, a batch process is run which generates the
final invoices - numbering them as it goes. In this instance I
perform a dummy update on the sequence number table to prevent
multiple instances of the batch process from operating at the same
time. This ensures that a single unbroken sequence of numbers are
assigned to the invoices - which keeps the auditors happy :-)

Geoff Worboys
Telesis Computing