Dear All,

I need your help with an issue. I'm writing a program using Firebird that will be able to print
invoices.

Invoices in my country (and probably in other countries too) have to have continual numbering.
2007/1, 2007/2, 2007/3 etc.
There can't be any gaps between numbers!

The question is, if there are multiple people creating new invoinces, how can I guarantee that
there won't be any gaps in the numbers?

There are two issues I'm facing:
1, I can't use generators, because a potential rollback operation would cause gaps in invoice
numbering. Also, I'm planning to store the invoices in the same table for different years, so there
will be an invoice with the same ID for year 2007, 2008, 2009, 2010 etc.

2, If I don't use generators I run into another problem. I'm storing the invoice data in multiple
tables like

INSERT INTO Invoices (Year, InvoiceID, ...) VALUES(2007, :id,...)
and then
INSERT INTO InvoiceItems(InvoiceID, ItemID, ...) VALUES ...
Transaction.Commit;

If I use InvoiceID = (SELECT MAX(InvoiceID) + 1 FROM Invoices WHERE Year = 2007) to get the
new invoice's ID, then if there are multiple users creating invoices, a potential
Transaction.Rollback could cause gaps in the number!

-> User 1 calls the inserts, gets ID = 1
-> User 2 calls the inserts, gets ID = 2
-> User 1 rolls back
-> User 2 commits

The result is a gap! ID = 1 remains unused!

In such situations, User 2 should get the ID = 1!
Any ideas on how to solve this problem?

Thanks in advance!

ZD