This post describes an approach for an ASP.Net MVC application with a database-first EF Model. I was using MVC 4 and EF 4.3. The project this was for had some requirements and standards that have to be met about the degree of auditing on at the DB level. Simply overriding the EF SaveChanges() method to catch all actions will not suffice as the DB would likely be accessed by other means outside of the MVC/EF application. So table based triggers was the way to go but how to pass the username through to the DB?
The approach described below builds of off concepts outlined in the following articles:
- http://lgsong.blogspot.ca/2012/01/contextinfo-and-entity-framework.html This article gives the general idea of what I'm doing here.
- http://jmdority.wordpress.com/2011/07/20/using-entity-framework-4-1-dbcontext-change-tracking-for-audit-logging/ See this article if you're using a Code-First approach (this was not an option for us).
- http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx The code in this article was full of syntax errors and was missing spaces and required a little clean up in order to work with it but the ideas are good. I adapted his approach of dynamically adding triggers to all the tables to work with the Audit table schema we were using.
The high level description of the approach is as follows:
- Create Audit_Log and Audit_Comment tables in your DB
- Add a stored procedure that takes a username and a comment string and inserts them into the comment table saving the generated comment_id and username in the database CONTEXT_INFO.
- Add insert, update and delete triggers to each table that will first extract the comment_id and username from the ContextInfo() and then use those when inserting the audit details into the Audit_Log table.
Other Suggestions
Use an XML column to reduce the number of inserts.Instead of storing before and after values in each audit entry row, the before values can be retrieved from the previous entry (inserts are also audited) although this makes queries on the audit data slightly complicated. Could also store only the columns that changed in the XML....
