--- In firebird-support@yahoogroups.com, "m24paul" <paul.mercea@...>
wrote:

>
> 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!

Paul,

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
(
AuditTimeID,
AuditTime timestamp
);

TransactionMap
(
TransactionID
AuditTimeID
);

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.

Adam