Sundar -- Thanks for the question regarding "Use of Triggers for auditing changes", version 10.2.0.2.0

You Asked

We are developing an OLTP system on the J2EE framework.
System expected to be up for at least 17 hours.The DB is ORACLE ver 10.2.0.2.0.There is a functionality(technical) expected to audit 'certain' transactional data.

We intend to use ORACLE DB Triggers.These triggers would be
1. generated per table,to be auto generated by a package.
2. Any change to the table structure will necessitate the generation of trigger code. This will be autogenerated, using the package mentioned above.
3. The shadow tables will be flat tables.Data retreival will be by the IT team and not through the online system.These tables will have no keys/indexes.
4. This auditing will be independent of the business logic.No matter where/how the updates happen, the triggers will take a snap shot of the old and new values.
5. The audit data is for use by the audit teams and IT department only.

On change to a field/row, the old and new value would be inserted into a shadow table for each transaction table to be audited.We are not using triggers for business functionality;Triggers are being used purely for auditing changes to certain transactional data.

Under such circumstances
A. Do you recommend the use of DB triggers
B. Do you see any performance drop with the use of DB Triggers

and we said...

you wrote:

There is a functionality(technical) expected to audit 'certain' transactional data.

but doing this at the table level will necessarily audit ALL modifications. Not just certain transactional bits.

You can certainly audit data changes by using triggers, but you will likely find you met the word of the requirement but missed the intent.


Auditing row level changes is virtually meaningless. Auditing those changes and coupling it with some business transaction (so you can look at a complete transaction worth of change and state "this was the HIRE_EMPLOYEE transaction done by the account X on this date and time") is probably more what you need.

Meaning - the J2EE application - it needs to seed this entire process. It needs to begin the transaction by creating the master audit trail record for this transaction - recording relevant information and the start time. It then can process the transaction - and whether you use triggers or do it at the application level is up to you (I'm not a fan of triggers personally), but the J2EE application would want to update the master audit trail record with an end timestamp as well - at the end.


In that fashion, you'll know many things...

How many of each transaction type you do in a span of time
Their average, min, max response times
Who does them
What data they touched


Without this "top level" bit of data - all you have are meaningless row level changes - nothing.


Database triggers will likely be slower than code written specifically to audit precisely what you want - but, database triggers will capture everything (except for that master record YOU need to create).

Reviews

Possible alternative   September 21, 2007 - 5am Central time zone Bookmark | Bottom | Top Reviewer: Nige from London, UK You may be interested in Oracle's Change Data Capture functionality. We implemented an audit system almost identical to your description, but we're considering re-writing it using CDC. Very powerful, efficient and maintainable. CDC   November 5, 2007 - 1pm Central time zone Bookmark | Bottom | Top Reviewer: RAJ from bristol, USA Hi Tom, With respect auditing, since oracle has provided CDC.. can you explain with an example with respect to CDC.. I have similar kind of requirement what was explained on this post. Would appreciate your reply.

---

Write a Review