Subject Re: Trigger for updating timestamp field?
Author Adam
--- In, "Nick Upson" <nick.upson@...>
> certainly, I do it all the time. in a before update trigger have code
> that looks something like
> new.last_update_dt = 'now';

Four things of note.

1. 'now' and CURRENT_TIMESTAMP do not necessarily return the same
result. One of them keeps a constant time for a single operation, the
other re-evaluates the current time every time it is used. This may be
significant when updating a lot of records. Use the one you require
(and no, I don't remember which is which).

2. They only have the precision of millisecond, which may not be
enough. See the GetExactTimestamp UDF function in fbudf.

3. Concurrency considerations, if you are trying to maintain some sort
of order of insert/update etc, two concurrent transactions could
potentially update different records at exactly the same time, leaving
some room for unpredictable ordering when selecting those records
later. In this case, using a generator value is useful.

4. This is one of Firebird's weaknesses. There is no 'nowUTC' or
CURRENT_TIMESTAMP_UTC calls, you get the local time in the timezone of
the server, which may or may not be adversely affected when daylight
savings moves clocks backwards etc. That said, you can always write a
UDF to return the current UTC time and use that instead (one of
Firebird's strengths), just make sure it is threadsafe.