Re: last time a table was modified

Well, it kind of depends also on what you want to achieve.

Adding a timestamp column in the table with a trigger is the most basic method.
But if you want to keep timestamp for individual updates (as opposed to just the most recent update, then you typically create another table to log all the transactions.
This would also be required to log timestamps for deletes as deleted rows are typically not accessible past the commit point (unless you only do logical deletes, but never actually delete the row).

You can also use auditing on the table, which can add a level of security.
Note that triggers can easily be temporarily disabled, and log tables can easily have rows updated or deleted.

Did you want to capture anything else then the time, such as the user, or what was modified ?
Also, are you only interested in capturing the information at the table level, or at the row level, and I am not clear if you need historical information, or just the last update.

Lastly, the information could also be retrieve from logminer, if it only needed to be done on rare occasion, and you only needed to investigate a short time period after the update.

HTH Received on Mon Aug 14 2006 - 12:29:04 CDT