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, or have I missed
something with the IB_Transaction links?

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.

Setup: IBO 4.3.Aa, Delphi 5, Firebird 1.5, Win2k

thanks
Mark