SQL Server 2000: Is there a way to tell when a record was last modified?


Translate

The table doesn't have a last updated field and I need to know when existing data was updated. So adding a last updated field won't help (as far as I know).


Toutes les réponses
  • Translate

    SQL Server 2000 does not keep track of this information for you.

    There may be creative / fuzzy ways to guess what this date was depending on your database model. But, if you are talking about 1 table with no relation to other data, then you are out of luck.


  • Translate

    You can't check for changes without some sort of audit mechanism. You are looking to extract information that ha not been collected. If you just need to know when a record was added or edited, adding a datetime field that gets updated via a trigger when the record is updated would be the simplest choice.

    If you also need to track when a record has been deleted, then you'll want to use an audit table and populate it from triggers with a row when a record has been added, edited, or deleted.


  • Translate

    You might try a log viewer; this basically just lets you look at the transactions in the transaction log, so you should be able to find the statement that updated the row in question. I wouldn't recommend this as a production-level auditing strategy, but I've found it to be useful in a pinch.

    Here's one I've used; it's free and (only) works w/ SQL Server 2000.

    http://www.red-gate.com/products/SQL_Log_Rescue/index.htm


  • Translate

    You can add a timestamp field to that table and update that timestamp value with an update trigger.


  • Translate

    OmniAudit is a commercial package which implments auditng across an entire database.

    A free method would be to write a trigger for each table which addes entries to an audit table when fired.