Data Auditing in NHibernate and SqlServer

Question

I'm using NHibernate on a project and I need to do data auditing. I found this article on codeproject which discusses the IInterceptor interface.

What is your preferred way of auditing data? Do you use database triggers? Do you use something similar to what's dicussed in the article?

Answer 1

For NHibernate 2.0, you should also look at Event Listeners. These are the evolution of the IInterceptor interface and we use them successfully for auditing.

Answer 2

[EDIT]

Post NH2.0 release, please look at the Event Listeners as suggested below. My answer is outdated.

---

The IInterceptor is the recommended way to modify any data in nhibernate in a non-invasive fashion. It's also useful for decryption / encryption of data without your application code needing to know.

Triggers on the database are moving the responsibility of logging (an application concern) in to the DBMS layer which effectively ties your logging solution to your database platform. By encapsulating the auditing mechanics in the persistance layer you retain platform independance and code transportability.

I use Interceptors in production code to provide auditing in a few large systems.

Answer 3

I prefer the CodeProject approach you mentioned.

One problem with database triggers is that it leaves you no choice but to use Integrated Security coupled with ActiveDirectory as access to your SQL Server. The reason for that is that your connection should inherit the identity of the user who triggered the connection; if your application uses a named "sa" account or other user accounts, the "user" field will only reflect "sa".

This can be overriden by creating a named SQL Server account for each and every user of the application, but this will be impractical for non-intranet, public facing web applications, for example.