title: How to audit all changes in a .net application with SQL Server
body: <p>I have a .net application that uses EF along with mainly SQL Server 2008 but a couple of customers use 2005.  In the database there are a few hundred tables.  I need to audit a selection of these tables to record all data inserts/updates/deletes.  I was thinking of having copies of these tables to record all the changes along with the datetime and username, then have a trigger on the actual table to insert the data changes to the relevant audit table.  Is this the best way of going about this?  Is a trigger the best way or should I use the EF instead?  I was thinking of having just the one audit table but I reckon it could get out of hand very quickly.</p>