Monday, August 20, 2007

Auditing a SQL Server database

I do not believe that I have covered this before, but the Active Record Code Generator can also generate audit records for your database tables. The template that I wrote (SqlAuditTrigger.vm) adds records to an AuditChanges table, with a related AuditFields table containing the table and field name. I think that this is a fairly compact solution, but I also added code to the trigger to ignore SA (system administrator) changes. This may not be appropriate for your application, but it is easily removed. I also have a "TODO" item to detect related lookup tables (really ENUMs) and translate IDs to Names, so that changes to the FavoriteColor_ID field would look at the FavoriteColor table, and instead of recording FavoriteColor_ID changed from 2 to 5, it would say changed from "Ruby Red" to "Passionate Purple". The reason that I did not actually implement it is that I do not have enough information. My code generation schema does not list all of the fields in related tables, so I can not tell a MasterCustomerList from a FavoriteColor or ServiceType table.

Today on SqlServerCentral.com, there was an article describing using XML and XSLT to generate triggers and his implementation used one audit table for each real table.

As with any problem, there are many solutions, and the best solution is the one that meets your needs and is maintainable. Don't forget to adjust the security and access level for your audit tables (read-only or deny access to regular users).

Labels: ,

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home