Subject Re: problem with insert/commit moment
Author Adam
--- In, "m24paul" <paul.mercea@...>
> Hi df_nina
> The simplest solution is to leave blank parameter `NOW' and define
> into your log table the default value as `NOW' for "ora" field.This
> way you get the time when transaction is commited!


The default value for a record is calculated at the moment the record
is inserted, *not* when the transaction is committed.

Firebird 1.x/2.0.x has no way of doing things 'on commit'. There may
be something in the upcoming 2.1 to achieve this, but I wouldn't do it
this way.

I would create other tables.

AuditTime timestamp


When you start your transaction, create a record in AuditTime, and
store this AuditTimeID along with CURRENT_TRANSACTION in TransactionMap.

Add AuditTimeID to your tables of interest.

Create a trigger (before insert or update) on these tables that
automatically populates the AuditTimeID by querying the TransactionMap
table 'where TransactionID=CURRENT_TRANSACTION'

Immediately before commit, simply update AuditTime to the correct
value and delete the record from TransactionMap.

The only gotcha with this approach is that the transaction id will
restart after a backup-restore. To work around this, simply run delete
from TransactionMap where TransactionID=CURRENT_TRANSACTION before you
try to insert this record.