> 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

my table in this situation is only one field or one plus the nullable
fields. One such field would be a state field, set to an initial state on
insert, then updated to another state when the invoice proper is completed.
Yet a different state if the invoice is cancelled. If the other table
transaction is rolled back, then the state could be something else. The rest
of the information for this item is in another table using the PK of this
invoice table as it's FK.
You always lock the primary table with a dummy update when you have to
update a field in it. You pay particular attention to the transaction used
for insert/update of this table. You also create a trigger before delete to
raise an exception to force no deletes on this table ever.
Alan

>
>
> ----- 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]



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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