My solution is that I have BeforeInsert trigger defined and there I check
the last invoice number in the year of date of issue, add 1 and set
NEW.INVOICE_NUMBER.

HTH
Marcin

----- Original Message -----
From: <toldy007@...>
To: <firebird-support@yahoogroups.com>
Sent: Friday, December 14, 2007 11:47 AM
Subject: [firebird-support] Generating unique continual integers for invoice
numbering


> 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
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>