On 23 Nov 2001, at 11:28, Andreas Pohl wrote:

> Brad,
>
> there are two possibilities to get next invoice number:
> - within transaction control by selecting max(nr)
> - outside transaction control by using generator value
>
> Both ways will fail in case of rollback transaction. Generator value
> cannot be set back safely in a multi user environment.
>
> Max(nr) or next_ nr_procedure will provide different results if more
> then one user is creating invoices and something goes wrong between
> calling trigger/statement for calculating invoice nr and closing
> transaction.

It really depends on how the application works, and what the
business rules are.

Here are two examples, invoices use a special paper form, which
needs to be fed into the old dot-matrix printer, so invoices get
batched and run by the A/R clerk, once per day, since only one
user will ever create invoices, a rollback means that all invoices
created get rolled back. In this case min(nr) works perfectly.

Another situation, the sales manager wants every order started to
be completed, if it can't be completed, then it must be entered with
a "failed" status and a reason put in the comments field. The
customer number must be included. In this case, you create the
order with a failed status, and then update it with real data.

Third situation, the program creates a blank order, it's given a
number, the number is assigned to the order-taker, if they fail to
complete the order, the next order they create gets that same
number, until they have a completed order.

In any of these cases, we can bend the C/S rules a little, and still
maintain our series even using a plain generator.

> Maybe you are a lucky men and there is only a small time frame between
> posting values and closing transaction, but in WANs or slow LANs...

But suppose you spec'd the system, and every machine has a
100baseT ethernet card, and they are all connected to a 100baseT
hub, using Cat-5e cable, and you know this is the situation, then
again you can bend the rules slightly, and it's still not a problem.

The real idea here, is that there are complex methods of
maintaining an auditable series, and there are simple methods, so
if you know a simple one will work with what your doing, why use a
complex method.

Paul
Paul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com