title: Structuring a database to track certain changes to data
body: <p>I have a SQL Server 2008 R2 database in which I need to track certain changes made to the data. So far all of my research has brought up ways to do quite complex tracking of every change made, but my case is somewhat more specific and, I think, simpler as I only need to track a change until it is approved (by an administrator).  </p>
  
  <p>So far the only way I can think of to do this is, for each affected table, to create a <code>pending_changes</code> table which tracks the type of change (i.e. <code>INSERT</code>, <code>UPDATE</code> or <code>DELETE</code>), the primary key, the column name, and, where applicable, the value or values to be changed.  </p>
  
  <p>Then when a change is approved, a SQL statement can be generated from this to write the change to the main table. This feels like a very clumsy way of doing it though, and it will be difficult to query this (e.g. to produce a list of old vs new values).</p>
  
  <p>This is a very small database (&lt; 1000000 rows) with only about 5 users, so performance is not an issue.  </p>
  
  <p><strong>EDIT:</strong>
  The purpose of tracking changes is that they need to be approved before they can be written to the database.  The data is mostly scientific with legal implications, so changes proposed by a user (in this case a client) need to be checked by an expert before they can be approved.  Once they have been approved, we don't need to know the old values any more.  All changes, including <code>DELETE</code> statements need this approval.  <code>INSERT</code> and <code>UPDATE</code> could be dealt with by creating a similar table which holds the changed data, but I'm not sure how to deal with deletions.  I also want to avoid too many extra tables because there are several base tables I need to track, and I am worried about the added complexity.</p>