Tracking changes in a SQL server 2005 database

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!

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.