At 07:36 PM 15/02/2008, you wrote:

>Hi again,
>
>I have created a single user POS just to show the client how it is
>going to work, and the requirements for it are the following:
>
>1) The Invoice numbers have to be sequential, and no gaps are allowed
>2) There shall not exist a case where invoice2.timesaved >
>invoice1.timesaved and invoice1.no > invoice2.no
>
>In the demo I have created, this is no problem. If the transaction
>fails, then I can always move the sequence back a number using
>ALTER SEQUENCE SEQ_NAME RESTART WITH GEN_ID(SEQ_NAME, 0)-1. Now, on
>the concurrent version, that will, obviously, not work. I could use
>locking, but I don't think that is the best approach.
>
>Can someone suggest a good approach to solve this?

The first thing you're going to have to decide is what you mean by "saved". The whole idea looks too vague and undefined yet to advise without wasting a lot of energy on IFs and BUTs.

The second thing you'll need to face is that here is a model case where the PK of the table should NOT be this non-atomic "thing", this invoice number, that has to be unique and not just sequential, but CONsequential. You'll need at least one non-atomic column to use as the PK and you'll need a bomb-proof management system for those invoice numbers...

./heLen