NHibernate: Audit logging using Interceptor or Triggers?

Question

Triggers seems like a simple solution for Audit logging. Why should I use Interceptors?

1. Database portability is one con of trigger...

what are others?

Answer 1

Another small issue is with triggers doing any DML. nHibernate uses the affected row count to determine success on a lot of its operations. If you are doing any inserts/updates/etc. inside your triggers, then you'll need to turn NOCOUNT on inside the triggers to prevent those false rowcounts from bubbling up.

Not that this, in any way, prevents you from making triggers work, but I've spent enough time refactoring away from this problem I thought it was worth mentioning. Interceptors, or EventListeners, are a simple, portable way to satisfy auditing requirements.

Plus, no more icky T-SQL code...

Answer 2

Con of using anything except a trigger is that not all data changes may take place through the GUI and therefore might not get logged. You have to consider that databases are changed from many sources including data imports and set-based queries from the query window (for instance when someone is asked to update all prices by 10%). If you use another method, you had better make sure that it captures any way data can be changed. If you use dynamic sql at all, then all your tables are open to the users to make changes directly in the database including fradulaent changes designed to steal from the company. Users committing fraud are one of the key things audit triggers are designed to catch. If you think your audit solution is ok becasue it captures evreything from the user interface and that it all it needs to capture, you are very, very wrong. I don't know how interceptors work, but you had better test with SSIS (or DTS) imports and queries from the query window before you think the solution will work. Also if it works just from the GUI, remember there might be more than one GUI connecting to a database.

Answer 3

I think that the reason for using interceptors is two fold:

1. So that you don't tie yourself to a particular database. the porting to different DBMS is significantly easier.

2. So that your domain model doesn't bleed into other areas of your code. ie the database needing to know about if a record has changed.

But all this depends on the context. If it is vital that all changes to particular records is neccessary then i think than HLGEM is correct. triggers are the best for handling that type of senario.

Answer 4

I agree with HLGEM. A good alternative to having the advantages of having both triggers and portability of DBMS, is to use some auditing tool that: Given an audit plan: generate the triggers for the appropriate DBMS

Pablo Javier

Answer 5

Triggers aren't easily testable and they're actually quite difficult to write properly. And if your audit data is to be consumed by business users, it's often difficult to translate from database row-level operations back into the domain model.

I'm of the opinion that a database is really just the persistence area of an application. Of a single application. In other words, I don't think that other systems should use my database directly and so I think auditing to be done outside of the database (i.e. not with triggers).