In 2009, EmpireCLS needed the ability to audit all table changes to see which fields were changed by which user or system at any point in time.
We analyzed the out-of-box SQL Server change tracking framework but it fell short in several areas, the most important of which was not being easy to maintain when adding new columns to a table that was being tracked.
We designed and implemented a custom generic solution for EmpireCLS that easily handled what was needed in this environment. We used a services of triggers in SQL, a back-end C# service, and a separate reporting database with a common set of SSRS reports to view the changes.
The SQL triggers and temporary tables could be generated via a single stored procedure that would interrogate the system tables for column names and types. This stored procedure would also do full diffs of the source tables so that column additions were automatically handled. Bringing a new table or an additional column could be done in seconds with no risk or change to the backend service or reporting infrastructure.
The backend service was generic and would infer the list of tables via a separate schema in SQL Server. The service would then handle batches of before and after data records, do diffs, and create the appropriate header / detail history tables in the reporting database. Only changed columns would end up being saved, which each column change equating to a detail row with before and after values, all details of the change where bound to a single header containing the change time and user.
Technologies… SQL Server, SSRS, C#, ado.net