Thanks Alan.
I kind of like your 1st idea.
But question is you are creating an almost empty record in invoice table in your 1st step (only invoice number being populated).
What about other non-nullable fields in the invoice table?

CM


----- Original Message ----
From: Alan McDonald <alan@...>
To: firebird-support@yahoogroups.com
Sent: Tuesday, November 28, 2006 2:15:00 PM
Subject: RE: [firebird-support] Auditable Invoice Number

> Hi All,
>
> I am thinking to use a generator for invoice number in my application.
> Any idea/code on how to generate auditable invoice number,
> meaning we CANNOT have a break in invoice number in invoice table.
> Thanks.
>
> regards,
> CM

If I want no gaps in a sequence, I always generate the ID and insert the
value into a table in a separate transaction and commit it. That's the first
step. An insert for this purpose will never deadlock and never raise an
exception (other than for hardware failure) between the ID acquisition and
the commit which should happen within 2-3 lines of code.
Then you are left to update this record with either a valid invoice OR you
update it with an "invoice operation cancelled" operation with annotation to
satisfy your auditor. Such a cancel operation may include the original
transaction amount and a mirrored creation of a credit - depends on your
auditor.

There is another way which I don't like, it involves management/restorat ion
of unused numbers. You need to constatnly interogate wht table for gaps and
use the gap numbers. My auditors don't like this method mainly because you
end up with a lower number aquiring a transaction date greater than a higher
number. I like the number sequence marrying the date sequence.
Alan




Send instant messages to your online friends http://uk.messenger.yahoo.com

[Non-text portions of this message have been removed]