At 01:16 AM 21/12/2004 +0000, you wrote:

>DB: Firebird 1.5
>
>I'm looking for a way to mark a record as being updated. For
>example, I read a record with a timestamp or other mark, then when I
>update it (after the user enters some changes etc.), I find that the
>mark has changed somehow (another user updating the record
>underneath me).
>
>What would be the best way to mark a record as updated or added?
>
>One potential solution would be to create a timestamp column and
>have an update/insert trigger update the timestamp column each time.
>Is there a performance issue with this (since getting the current
>time may not be overly quick, and it must be done once per
>update/insert).

No problem; the server time is always available as context variables
CURRENT_TIMESTAMP and CURRENT_DATE.

>Or, add a 64bit integer column and have the triggers do a +1 on this
>column... would this be 'atomic' enough given that multiple users
>may be editing a given record...

No, not "atomic enough". In any case, a timestamp + 1 adds a whole day.

Note that CURRENT_TIMESTAMP will be the time when the statement started
executing, which may or may not be exact enough for you with a statement
operating on a large number of rows. A timestamp has the potential
(however slight) to record the same timestamp twice (although that might
not matter for your requirements); but CAST('NOW' AS TIMESTAMP) is also
available, if you want the timestamp to be up-to-the moment for each
row. In both cases, you will get timestamping to the end of the previous
whole second, i.e. the subsecond part is always zero.

"Another user updating the mark underneath me" won't apply usually, since
two of you can't normally update the same row simultaneously, i.e. if one
transaction manages to write a new version of the row to disk, then another
transaction will be unable to do so. The trap would come if the second
user's transaction were in ReadCommitted isolation with WAIT lock
resolution: depending on requirements, the second transaction's update
might eventually be permitted. If your trigger were checking whether the
new.value were actually greater than the old.value, then the old.value
would in fact be the value committed by the first transaction.

>Any advice here on best practices would be great!

I can't make any particular recommendation about the usefulness of
timestamping for versioning changes, not knowing the exact requirements or
conditions. Other things are available which might help get closer to
determining precedence; for example, a generator, or the
CURRENT_TRANSACTION, might be more dependable at the moment of truth. OTOH,
they are "just numbers" - able to establish precedence but not to tell you
anything else that's meaningful.

./hb