Tracking changes in a SQL server 2005 database

Question

I have been tasked with developing a solution that tracks changes to a database.

For updates I need to capture:

• date of update
• old value
• new value
• field affected
• person doing change
• record id
• table record is in

For deletes:

• date of delete
• person doing delete
• The title/description/id of record deleted. The tables I am tracking changes on all have a title or description field. I'd like to capture this before the record is deleted.
• table record was in

For inserts:

• date of insert
• person doing change
• record id
• table record is in

I've thought of a few ways to do this:

• I am using stored procedures for any updates/deletes/inserts. I would create a generic "tracking" table. It would have enough fields to capture all the data. I would then add another line in each stored proc to the effect of "Insert record into tracking table".
  • downside: all updates/deletes/inserts are all jumbled in the same table
  • lots of NULLed fields
  • how do I track batch updates/deletes/inserts? <---- this might not be an issue. I don't really do any thing like this in the application.
  • how do I capture the user making the update. The database just sees one account.
  • edit a lot of existing code to edit.
• Lastly, I could create a trigger that is called after updates/deletes/inserts. Many of the same downsides as the first solution except: I would have to edit as much code. I am not sure how I would track updates. It doesn't look like there's a way using triggers to see recently updated records.

I am using asp.net, C#, sql server 2005, iis6, windows 2003. I have no budget so sadly I can't buy anything to help me with this.

Thanks for your answers!

Answer 1

A trigger wouldn't have all the information you need for a bunch of reasons - but no user id is the clincher.

I'd say you're on the right track with a common sp to insert wherever a change is made. If you're standardizing on sp's for your interfaces then you're ahead of the game - it will be hard to sneak in a change that isn't tracked.

Look at this as the equivalent of an audit trail in an accounting application - this is the Journal - a single table with every transaction recorded. They wouldn't implement separate journals for deposits, withdrawals, adjustments, etc. and this is the same principle.

Answer 2

I hate to side-step the issue and I know you have no budget, but the simplest solution will be to upgrade to SQL Server 2008. It has this feature built in. I thought that should at least be mentioned for anyone else who comes across this question, even if you can't use it yourself.

(Among the deployable editions of SQL 2008, this feature is only available in Enterprise.)