Sunday, February 9, 2014

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
share|improve this question
 
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

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
 
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
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

6 comments:

oakleyses said...

jordan pas cher, chanel handbags, nike outlet, michael kors pas cher, kate spade outlet, replica watches, longchamp pas cher, nike free, jordan shoes, christian louboutin shoes, nike free run, louis vuitton outlet, oakley sunglasses, ray ban sunglasses, polo ralph lauren, ugg boots, christian louboutin uk, air max, louis vuitton outlet, ugg boots, tiffany jewelry, polo ralph lauren outlet online, burberry pas cher, prada outlet, nike air max, gucci handbags, sac longchamp pas cher, ray ban sunglasses, louboutin pas cher, louis vuitton, uggs on sale, tiffany and co, oakley sunglasses, louis vuitton outlet, ray ban sunglasses, longchamp outlet, louis vuitton, longchamp outlet, replica watches, nike roshe, polo outlet, oakley sunglasses, cheap oakley sunglasses, oakley sunglasses wholesale, christian louboutin, christian louboutin outlet, tory burch outlet

oakleyses said...

lululemon canada, nike air max, burberry outlet, oakley pas cher, burberry handbags, coach outlet store online, kate spade, michael kors outlet, michael kors, nike air force, true religion jeans, true religion outlet, michael kors, polo lacoste, nike tn, new balance, abercrombie and fitch uk, michael kors outlet, uggs outlet, michael kors outlet, ralph lauren uk, michael kors outlet online, replica handbags, coach outlet, true religion outlet, coach purses, nike free uk, sac vanessa bruno, mulberry uk, michael kors outlet online, michael kors outlet online, michael kors outlet online, north face, uggs outlet, converse pas cher, hogan outlet, nike air max uk, hollister pas cher, sac hermes, nike roshe run uk, hollister uk, nike air max uk, true religion outlet, timberland pas cher, vans pas cher, ray ban pas cher, guess pas cher, ray ban uk

oakleyses said...

supra shoes, marc jacobs, canada goose, converse outlet, toms shoes, ugg uk, ugg pas cher, canada goose outlet, canada goose, nike air max, louis vuitton, karen millen uk, doudoune moncler, converse, louis vuitton, vans, gucci, montre pas cher, moncler outlet, canada goose uk, canada goose outlet, moncler, pandora jewelry, barbour uk, moncler outlet, juicy couture outlet, louis vuitton, canada goose, pandora uk, coach outlet, swarovski crystal, ugg,uggs,uggs canada, juicy couture outlet, links of london, louis vuitton, louis vuitton, hollister, swarovski, canada goose outlet, ray ban, moncler, ugg, barbour, ugg,ugg australia,ugg italia, replica watches, pandora charms, moncler uk, pandora jewelry, wedding dresses, canada goose jackets, hollister, lancel

Unknown said...

www0626

oakley sunglasses
nike air max
nike outlet
coach outlet
puma outlet
swarovski outlet
suns jerseys
coach outlet online
chicago blackhawks jerseys
pandora jewelry





jeje said...

Une personne que je n'étais nike air jordan 1 retro high og laser jamais prêt à faire était de prendre des suppléments illégaux ou tout type d'hormone de croissance. À l'heure actuelle, beaucoup de gens ont vu les publicités à la télévision pour ce Jack Jupiter. Les vêtements Bapes sont probablement les premiers modèles japonais Asics Gel Lyte 3 Pour Femme de streetwear. À 50 $, le prix se sent un peu raide, essayez de trouver ce briquet en vente, vous ne serez pas déçu. Scène: Plus de 100 clients attendent impatiemment dans env. Les deux chaussures utilisent un caoutchouc solide à chevrons asics gel fuji pro femme avis sur la semelle extérieure.

Unknown said...

replica bags buy online l52 r2z58h0b92 high replica bags j12 f5r50p3w91 cheap designer bags replica u66 j8m58t4c31