Tracking data updates

[ookete]

We have an existing database and we want to start tracking updates to the data. We need to store info such as Date of change, who changed the data, original value, etc. It seems to me that this will be a pretty extensive system to be able to track any edits that occur on certain tables in our database.

Does anyone have any tips or "philosophy" on how this should be done? I was thinking of two methods:

1) Have a table that holds the original data along with the table and column name that data originally resided. This would be fairly simple, but would be a pain to "rebuild" the data if anyone wanted to query these changes.

2) Create a "duplicate architecture" and archive the original values of edited data into this mirrored table structure. This would be easy to query, but a real pain to manage... any update to the original structure would have to be made to this archive structure as well.

Any thoughts, links, etc would be greatly appreciated. Are there any data edit tracking tools in existence out there that could save me a lot of time and headache? Thank you.

 

[TheBugSlayer]

Ookete,
a TRIGGER seems to be a solution to your problem.

You create an UPDATE trigger on your original table and inside the trigger you write a statement to store the old information into a tracking or history table, along with who made the changes and any other relevant information.

Example:

CREATE TRIGGER updSomeData
FOR update AS
  INSERT INTO HistTable(F1, F2,...,Fn)
    SELECT del.F1, del.F2,...,del.Fn FROM Deleted del
GO

DELETED and INSERTED are logical tables created by SQL Server that have the same structure as the table for which you are defining the trigger. They contain the data that has been modified (DELETE, UPDATE->DELETED) or inserted (INSERT->INSERTED).

For more information on triggers refer to SQL BOL (creating triggers).

Hope this helps.