Re: How to increased by exactly 1 ?

adragnes wrote:

> In some European countries gap less sequences for such things as
> invoices is a legal requirement in accounting software for documents
> such as invoices. It was a way to make sure that no such documents have
> been destroyed. Oracle E-Business Suite does offer gap-less sequences,
> but their use is not recommend as performance really suffers.
>
> In databases I should think that the rationale behind the gap-less
> sequences can be achieved in other ways such as audit trails on the
> tables in question. Naïve reimplementation of paper-based procedures
> does not always make sense.
>
> Even on paper pads with numbered sheets you could go wrong. You could
> for example spill coffee over them.

One solution is to disallow updates on the surrogate key and to replace deletes with a flag that marks a record inactive.

In the past I have used exception handlers to insert sequence numbers into an errors logging table so that the disposition of the number is tracked. Triggers could be used as you describe in an auditing function or just to prevent inappropriate updates and deletes.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)