Re: How to don't update sequence on rollback of a transaction

On Fri, Aug 3, 2012 at 6:59 AM, Julian <tempura(at)internode(dot)on(dot)net> wrote:
> Hi,
> If you want guaranteed "consecutive" sequential numbering you have to
> implement your own solution. I was brought to task by a number of people
> about this (accountants). So its not a good idea to use a sequence for
> things like invoice, receipt and other such accounting objects (not only
> monetary), unless its somehow acceptable in your region. You can pretty
> much duplicate the functionality of sequences as normal tables with the
> benefit of them being transaction safe.
> Be sure you are using it for reasons where its absolutely required.
> For everything else a sequence does what its intended to do.
>
> Regards,
> Julian
>
> P.S I have heard of people using a sequence in an AFTER trigger to generate
> consecutive numbering to some success. But anything could happen.

Yes what you basically do is use a sequence internally, where the user
can't see it, and then either use an after trigger or lock the table
for a very quick update to the column used for the row number after
all your processing is done.