Hi,

I need a 100% reliable way to detect the last change to a row in a sql
server 2000 table.

So far I have created table A with a timestamp column. I need to know
if there has been any change  to the rows in table A. Therefore after
each row modification in this table I encrypt and save the timestamp
value to a column in a second table B.

Table B holds the encrypted timestamp values for each row in A. I can
then compare the timestamp values in tables A and B to determine if
anyone has changed the data. If someone modifies a row in A the
timestamps will be out of sync.

The problem is someone can still change the data back to an old value
by inserting directly into the timestamp column using SELECT INTO, or
by restoring the two database tables from an earlier backup. Therefore
timestamp columns are not quite good enough for my requirements, does
anyone know another better way of detecting row changes in sql server?

Thanks.

Are you referring to the timestamp (rowversion) column/data type?

Look up ROWVERSION in Books Online: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_6fn4.asp

Columns of data type rowversion (or timestamp) cannot be updated, their
value changes every time a column in the row is updated. The values are
guaranteed to be globally unique across the database.

What exactly are you trying to achieve?


ML

---
http://milambda.blogspot.com/

This is not really the true in my case. Timestamps can be "updated" by using
Select Into. They can also be changed back to an older version by restoring
the database.

I'm trying to track some values and need to detect when someone rolls back
the data to an earlier state.

Have you tried this?  Can you show a repro that would fool your optimistic
concurrency checker into believing that the data hasn't changed when it
really has?

If someone restores your entire database to an earlier version, then what is
the problem?  You're about to commit an update to a row that, as far as
you're concerned, looks exactly the same as it did before you read the
value.  Maybe you should be more careful about who has the right to restore
your database and when they are allowed to do so.

A

It is not just concurreny I am checking for (that works fine). The data
I am monitoring is an incrementing value that should never be set back
to a lower value, and I am trying to prevent someone doing this by
restoring the database to an earlier state. I guess I am looking for a
system value that will notify me if a database has been restored
recently, some type of system change number. I think I can catch the
SELECT INTO case by checking the table created datetime.

I do not have any control over who has rights to the database.

But it sounds like what you are looking for is not a way to prevent it...
storing a timestamp value somewhere else (e.g. in a database) will only tell
you, after the fact, that it has happened.  And if that's all you need then
just store the last updated date in a table in another database.

A

Parameterize table name without constructing dynamic query?
use trigger
Is subquery bad?
Max date in a row
Left trimming little squares/spaces