At 12:36 PM 14/06/2004 +1000, you wrote:

>Hello,
> I've implemented the Auditable series in my program as outlined
> in the TI sheet, however, I'm a little confused by the resulting
> behaviour when rolling back a transaction.
>
> It seems that if I have the following sequence:
> con.DefaultTransaction.StartTransaction;
> try
> sql1.Append;
> // set some field values
> sql.Post;
> raise Exception.Create('Just for testing');
> con.DefaultTransaction.Commit;
> except
> con.DefaultTransaction.Rollback;
> end;
>
> where con (TIB_Connection) has its DefaultTransaction property set to a
> TIB_Transaction and sql1 is a TIB_Query component with its
> IB_Connection property set to con and the IB_Transaction
> property left as <default>.
>
> The result of the AI_INSERT_INV_NUM trigger (which sets the
> invoice number to 'USED') doesn't seem to get rolled back. Hence
> the invoice number remains in the USED state.
>
> However, if the Post is removed from the above code sequence,
> then the INV_LOG table doesn't contain an entry for the next invoice
> number...which is expected.
>
> My two questions are, should the result of the trigger be
> rolled back if the Transaction is rolled back,

Yes.

> or have I missed
> something with the IB_Transaction links?

Basically, if you find that a trigger has changed *any* data, then the
commit happened without any exception.

My guess is that the Post is independently posting and committing the work
in a different (internally-generated, default) transaction. Set the
ib_transaction property of the query to the actual transaction that you
want it to be in, not <Default>.

> The second question is when should the SP_FREE_INV_NUMBER stored
> procedrue be called, and if it needs to be called for the above
> scenario, then how do I ensure it is called and successfully
> frees up the invoice number?
> ie Even if you did the following, you could still end up with a
> hole in the numbering sequence:
> try
> sql1.Append;
> // set some field values
> sql.Post;
> raise Exception.Create('Just for testing');
> con.DefaultTransaction.Commit;
> except
> try
> // Call stored procedure to cancel
> except
> // If it fails then the invoice number would not be freed up
> // (based on my results from the current scenario)
> con.DefaultTransaction.Rollback;
> raise;
> end;
> con.DefaultTransaction.Rollback;
> end;
>
> Any insight would be greatly appreciated.

I suggest that you use the TI sheet as a guideline, rather than as a
framework that is portable to any situation. That said, your main hangup
currently seems to be that you're not using the transactions you think you
are. Having control over what gets committed and what gets rolled back,
by what, and when, requires you having control over the transactions
themselves.

Helen