> In the paradigm I was thinking of, the record is a transient side effect
> of the application until it is committed. Under theses circumstances
> the applications layer needs to know the value of the token before
> attempting an insert. The application is then responsible to roll back
> the failed transaction, populate and insert the "void" record, and
> commit.
>
> In a the paradigm of a pure database transaction, where the record is
> the major repository of information rather than a transient side effect,
> your sequence is absolutely correct.
>
> There are subtle differences between the end results of the two
> approaches, and the implications of each mechanism should be understood
> in the context of the business model before settling on one mechanism
> over the other.

agreed - the paradigm I use (if I use invoices as an example), is that a
transient record is created in the application, call it a draft or unposted
invoice. This requires it's own PK where broken sequences are not a part of
anything visible to auditors or users. It remains an unnumbered invoice
until the user wishes to post it (using accounting terms). It is not
difficult to make this single action exceptionless - i.e. you are getting
the next generator value and updating this one field of the invoice.
If the user then needs to reverse this document, then that's what must be
done. The user can void it, in which case a void description is overwritten,
the credit/debits are reversed but the invoice is never lost (deleted_ as a
real document - it's never deleted, merely reversed. Therefore no breaks in
sequence and an auditor can be happy about the state of accounts.
Alan

>
> On Wed, 2004-10-27 at 07:31, Alan McDonald wrote:
> >
> > > When a transaction is rolled back, its token must be inserted into a
> > > placeholder "void" record. Instead of a simple rollback, the rollback
> > > operation must consist of rollback, start trans, insert "void" record,
> > > commit.
> > >
> >
> > if it's rolled back there wont be a record to re-insert. I
> think you'd need to commit (not rollback), then start another
> transaction, insert the record to be deleted into a void record
> table holder, delete the record to be voided and then commit again.
> > Alan