I am working on a financial web application in which i need to store each transaction performed on a particular user account with the old data(before executing the transaction) and the new data(after executing the transaction) and possibly need to reverse it if user wants to do so.
We are using JAVA with iBATIS as a persistence framework.
I don't know how i am going to implement this. initially i thought of storing xml of old and new data but dont have any clue on that.
at least the triggers I know share your session (if you want them run outside of your session you have to use pragma autonomous_transaction in oracle, e.g.), so all changes will be rolled back, if you perform a rollback in your session.
This primarely covers SQL Server techniques (e.g. Change Data Capture (CDC), but the trigger examples should work for other DBs, too.. Of course they will have to be adapted to the different syntax of your DB.