title: Tracking changes in a SQL server 2005 database
body: <p>I have been tasked with developing a solution that tracks changes to a database.</p>
  
  <p>For updates I need to capture:</p>
  
  <ul>
  <li>date of update</li>
  <li>old value</li>
  <li>new value</li>
  <li>field affected</li>
  <li>person doing change</li>
  <li>record id</li>
  <li>table record is in</li>
  </ul>
  
  <p>For deletes:</p>
  
  <ul>
  <li>date of delete</li>
  <li>person doing delete</li>
  <li>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.</li>
  <li>table record was in</li>
  </ul>
  
  <p>For inserts:</p>
  
  <ul>
  <li>date of insert</li>
  <li>person doing change</li>
  <li>record id</li>
  <li>table record is in</li>
  </ul>
  
  <p>I've thought of a few ways to do this:</p>
  
  <ul>
  <li>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".
  
  <ul>
  <li>downside: all updates/deletes/inserts are all jumbled in the same table</li>
  <li>lots of NULLed fields</li>
  <li>how do I track batch updates/deletes/inserts? &lt;---- this might not be an issue.  I don't really do any thing like this in the application.</li>
  <li>how do I capture the user making the update.  The database just sees one account.</li>
  <li>edit a lot of existing code to edit.</li>
  </ul></li>
  <li>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.</li>
  </ul>
  
  <p>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.</p>
  
  <p>Thanks for your answers!</p>