Implementing Audit Trail- Spring AOP vs.Hibernate Interceptor vs DB Trigger

Question

I found couple of discussion threads on this- but nothing which brought a comparison of all three mechanism under one thread.

So here is my question...

I need to audit DB changes- insert\updates\deletes to business objects.

I can think of three ways to do this

1) DB Triggers

2) Hibernate interceptors

3) Spring AOP

(This question is specific to a Spring\Hibernate\RDBMS- I guess this is neutral to java\c# or hibernate\nhibernate- but if your answer is dependent upon C++ or Java or specific implementation of hibernate- please specify)

What are the pros and cons of selecting one of these strategies ?

I am not asking for implementation details.-This is a design discussion.

I am hoping we can make this as a part of community wiki

Answer 1

I only can talk about Triggers and NHibernate, because I don't know enought abou tSpring AOP.

It depends on, as always, what is most important for you.

DB triggers

• are fast
• are always called, even from native SQL, Scripts, external apps.
• write data in the DB of which NH doesn't know about. It will be missing in the current session. (Which could lead to unexpected results)
• do usually not know anything about your session (say: login name).

NHibernate interceptors / events

• are not DBMS specific.
• allow you easy access to you business information, like the user session, client machine name, certain calculations or interpretations, localization, etc.
• allow you declarative configuration, like attributes on the entity, which define if the entity needs to be logged and how.
• allow you turning off logging, this could be important for upgrades, imports, special actions that are not triggered by the user.
• allow you an entity view to the business model. You are probably closer to the users point of view.