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

up vote7down votefavorite 6

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 database design hibernate audit spring-aop share|improve this question asked Apr 20 '09 at 16:38 community wiki RN.

There is another option: At least some databases have their same audit feature. Pro: Very reliant, probably high performance; Con: highly vendor specific –  Jens Schauder Aug 12 '09 at 14:30 add comment

6 Answers

activeoldestvotes

up vote3down voteaccepted

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. share|improve this answer edited Sep 26 '12 at 6:58 community wiki 2 revsStefan Steinegger

Can we log the username who changed the data using DB triggers? –  Samurai Sep 25 '12 at 12:13 How should I know? You can only do this if you know the user in the db. Typically you keep the user session in memory of the server, and the DB doesn't know about it. –  Stefan Steinegger Sep 26 '12 at 6:50 add comment

up vote2down vote

I understand this is not 100% related to the question but it does add value with new options. There are two more ways you can audit what’s going on. Reading transaction log: If database is in full recovery mode then all details about INSERT, UPDATE, DELETE and DDL statements are logged into transaction log. Problem is that it’s very complex to read because it’s not natively supported and that you’ll need a third party transaction log reader such as ApexSQL Log or SQL Log Rescue (the latter one is free but only supports sql 2000). Advantage of this method is that you literally don’t have to make any changes except to put your database in full recovery mode. SQL Server traces: Traces will capture everything in trace files including select statements which also may be needed for some compliance scenarios. The downside is that traces are text files that need to be parsed and organized. share|improve this answer