title: Possible ways to generate audit trail for every save of business entities
body: <p>We currently have a requirement to implement audit trail generation for some of our main business entities, as usual in these cases we need to persist old and new value of every field which was changed and also some header data like time stamp, entity ID and user who did the save.</p>
  
  <p>I understand there are different ways to do this, such as:</p>
  
  <ol>
  <li>.NET code side, using Reflection</li>
  <li>SQL Server side triggers</li>
  <li>SQL Server CDC (Change Data Capture)</li>
  </ol>
  
  <p>A .NET Reflection based method could possibly take a bit longer to be written but if properly done will be smart enough to include new properties added in the future without any code change, and it can also expand and compare all children entities (like collections of other sub-entities added to our main .NET entity).</p>
  
  <p>We actually have a legacy application which uses such .NET based audit trail generation, we save the whole audit trail as XML field in a SQL database and over the years that audit table is now something like 35GB of data.</p>
  
  <p>I am thinking how easy it could be a trigger based solution in terms of:</p>
  
  <ul>
  <li>first implementation</li>
  <li>every change required in future modification of the entity to audit (add/change/remove a field etc...)</li>
  <li>how readable is the audit data? can we simply have a query which is showing old and new values for a specific saving operation?</li>
  </ul>
  
  <p>... and how about performances?</p>
  
  <p>Does anybody have experience with both approaches and can suggest or point out some pros and cons?</p>