CODEWITHSUNDEEP
sql-server
Patrick McElhaney
Patrick McElhaney

Reputation: 59271

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

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).

Upvotes: 11

Views: 2883

Answers (5)

Matt
Matt

Reputation: 5142

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

Upvotes: 1

Chris Miller
Chris Miller

Reputation: 4899

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.

Upvotes: 1

GateKiller
GateKiller

Reputation: 75869

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.

Upvotes: 0

Mufaka
Mufaka

Reputation: 72

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.

Upvotes: 5

huseyint
huseyint

Reputation: 15081

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

Upvotes: 0

Related Questions