Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Bruce Williams <bwilliams@jbs1.com>, pgsql-sql@postgresql.org
Subject: Re: Invoice Numbers
Date: Wed, 23 Apr 2003 19:30:32 +0100
User-Agent: KMail/1.4.3
References: <3EA6B4F3.8D78F9F4@jbs1.com>
In-Reply-To: <3EA6B4F3.8D78F9F4@jbs1.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200304231930.32093.dev@archonet.com>

On Wednesday 23 Apr 2003 4:44 pm, Bruce Williams wrote:
> Hi,
>
> I'm creating an invoice in an Access 2000 report.  I would like to know
> how can I establish an invoice number that will generate automatically
> in a table?  Do I have to create my own numbers or do you have a better
> suggestion.  Thanks

If you want sequential invoice numbers with no gaps, then you need to make=
=20
sure that only one process allocates numbers at any time.

This means you want to either lock the table or use the serializable=20
transaction isolation level while allocating invoice numbers.

Allocating the next invoice number is as simple as max(invoice_num)+1 altho=
ugh=20
many invoice applications use a "next invoice value" in a system table (whi=
ch=20
lets you set up an initial invoice number per company).

If you have many clients, you may wish to insert invoices without a number=
=20
then create the number in a second transaction to reduce lock contention. T=
ry=20
a search on the mailing-list archives for other discussions on this.

--=20
  Richard Huxton