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

8 comments:

oakleyses said...

louis vuitton handbags, oakley sunglasses, louboutin, longchamp outlet, nike shoes, louis vuitton outlet stores, chanel handbags, burberry outlet, prada outlet, jordan shoes, tiffany and co, michael kors outlet, tory burch outlet, louis vuitton outlet, longchamp handbags, nike free, true religion jeans, michael kors outlet, kate spade outlet, polo ralph lauren outlet, tiffany and co, prada handbags, polo ralph lauren outlet, michael kors outlet, michael kors outlet, longchamp handbags, oakley sunglasses, ray ban sunglasses, kate spade handbags, burberry outlet, louis vuitton outlet, louboutin outlet, louboutin, coach factory outlet, air max, air max, coach outlet, gucci outlet, christian louboutin shoes, michael kors outlet, coach purses, ray ban sunglasses, michael kors outlet, louis vuitton, coach outlet store online, true religion jeans, oakley sunglasses cheap

oakleyses said...

ralph lauren, lululemon, air max, hollister, north face, nike air max, polo lacoste, vanessa bruno, timberland, vans pas cher, louboutin, louis vuitton, oakley pas cher, air max pas cher, nike roshe run, air max, true religion outlet, barbour, sac longchamp, air force, hollister, sac louis vuitton, nike free, polo ralph lauren, nike trainers, louis vuitton uk, nike roshe, sac hermes, longchamp, michael kors, sac burberry, sac guess, mulberry, new balance pas cher, converse pas cher, sac louis vuitton, hogan outlet, nike tn, north face, true religion outlet, ray ban pas cher, michael kors, air jordan, nike blazer, nike free pas cher, michael kors pas cher, abercrombie and fitch, ray ban sunglasses

oakleyses said...

mac cosmetics, mont blanc, marc jacobs, canada goose outlet, nike huarache, vans shoes, soccer jerseys, hollister, giuseppe zanotti, beats by dre, abercrombie and fitch, longchamp, insanity workout, celine handbags, bottega veneta, ghd, nfl jerseys, north face outlet, chi flat iron, ugg boots, birkin bag, ugg australia, canada goose, herve leger, ugg pas cher, rolex watches, valentino shoes, canada goose uk, canada goose, ferragamo shoes, canada goose, ugg boots, uggs outlet, north face jackets, soccer shoes, asics running shoes, new balance shoes, p90x, lululemon outlet, canada goose jackets, mcm handbags, instyler, babyliss pro, ugg, wedding dresses, jimmy choo outlet, reebok outlet, nike roshe run

oakleyses said...

parajumpers, karen millen, air max, converse, pandora charms, moncler, louboutin, moncler, links of london, lancel, juicy couture outlet, oakley, hollister, pandora charms, supra shoes, thomas sabo, canada goose, gucci, wedding dresses, timberland boots, swarovski crystal, air max, coach outlet store online, moncler, ray ban, canada goose, moncler, ugg, louis vuitton, swarovski, hollister, montre homme, moncler, hollister clothing store, ralph lauren, rolex watches, moncler outlet, moncler, iphone 6 cases, baseball bats, juicy couture outlet, toms shoes, vans, pandora jewelry, ugg, converse shoes

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

ipad cases, beats by dre, iphone 6 cases, ferragamo shoes, wedding dresses, hollister clothing, p90x workout, instyler, asics running shoes, nike huaraches, hermes belt, north face outlet, s6 case, babyliss, nfl jerseys, hollister, iphone 6 plus cases, insanity workout, ralph lauren, iphone cases, iphone 6s plus cases, bottega veneta, new balance shoes, longchamp uk, iphone 6s cases, giuseppe zanotti outlet, nike roshe run, mont blanc pens, herve leger, oakley, lululemon, timberland boots, nike air max, iphone 5s cases, ghd hair, valentino shoes, louboutin, nike trainers uk, celine handbags, north face outlet, baseball bats, vans outlet, abercrombie and fitch, chi flat iron, soccer jerseys, mac cosmetics, mcm handbags, soccer shoes, jimmy choo outlet, reebok outlet

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