Tracking Changed Data

Question

Tracking Changed Data

erdem1973

SSCommitted

Points: 1843
More actions
August 9, 2006 at 5:11 am

#92354

Hi Folks,
I have a database table named CustomerOrders.I want to track data changes on a specific column named "OrderDate". How can I do this?
A trigger?sp?

Please help..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi,

Unless you are already controlling all data manipulation with the use of stored procedures your best bet would be to use a trigger.

Now you have to decide what you want to track and how you want to track it. For example, you might only want to track the last user to have updated a record and possibly the time of the last update. In this case you could easily do this using last_updated_by and last_updated_date columns in the same table.

If you're looking to actually track the changes themselves and maintain a history of changes then you're looking at creating another table, e.g. CustomerOrder_History.

You'd then create a trigger on the CustomerOrders table and check for updates on the "OrderDate" column. You might also want to check for deletes in the same table but the same prinicple applies.

If you determine that an update on the "OrderDate" column has occurred:

Query the deleted table and insert the necessary records, including the current datetime, and user into the history table.

Hope that helps get you started,

Karl
source control for SQL Server

erdem1973 SSCommitted Points: 1843 More actions · Answer 2

Thank you Karl....You gave me a real starting point

erdem1973 SSCommitted Points: 1843 More actions · Answer 3

How can I determine the original record value by using a database trigger on SQL Server?

Any idea?

SQLZ SSChampion Points: 12872 More actions · Answer 4

The original record(s) will be in the deleted table.

When an update is performed SQL Server deletes the original record and inserts a new record so you'll find the original record(s) in the deleted table and the updated record(s) in the inserted table.

To account for multiple records being updated you'll need to join the two tables so you know which deleted record relates to which inserted record.

Hope that helps,

Karl
source control for SQL Server

Cindy Hallum SSCommitted Points: 1808 More actions · Answer 5

Go to the home page for this wonderful website and search for 'audit trigger'. I have seen code examples for doing what you're looking to do. I'm sure you'll find something that will help you.