RE: Data auditing: triggers vs application code
- From: "Justin Cave (DDBC)" <jcave@xxxxxxxxxxx>
- To: <dubey.sandeep@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Sep 2005 17:54:51 -0600
I would suggest looking into Oracle Workspace Manager as Option #3.
- It's integrated in the database, so you'll get auditing
regardless of what application is modifying the data
- You don't have to write any trigger code, so you can't
accidentally forget to update a trigger when you modify a table.
- There is no risk that a trigger will go invalid since you
don't maintain triggers (though Oracle does)
- You can use the dbms_wm.gotoDate function to have Oracle
behave as if version-enabled tables were as of a particular point in
time. Unlike flashback query, your UNDO tablespace doesn't restrict
your ability to go back in time, you can go back to the instant you
version-enabled the table.
There are some annoyances particularly in 9i about different things that
don't quite work with version-enabled tables (you can't have non-primary
unique constraints until 10.1 for example) but if it works for you, it's
quite slick.
Application Developer's Guide - Workspace Manager
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10824/toc.
htm
Justin Cave
Distributed Database Consulting, Inc.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sandeep Dubey
Sent: Wednesday, September 21, 2005 3:52 PM
To: oracle-l
Subject: Data auditing: triggers vs application code
Hi,
I need to keep track of history of data change in the live tables. Two
methods are being compared.
1. Create triggers on the live table. For each insert/update/delete
insert a row in the audit table.
2. Let the application take care of inserting the data in the audit
table itself.
The cons against using triggers in the order of severity are:
1. If somehow the triggers are disabled in production, the application
goes through without noticing it and no audit data will be captured.
2. In a high transaction environment triggers have overhead.
As a database person, I am inclined to use triggers. But I fail to
guarantee that trigger will never get disabled. If it is disabled
somehow application SHOULD stop. (It is impractical to check the status
of all underlying triggers before each transaction).
I would like to hear how you guys handle data auditing in your system.
Thanks
Sandeep
Other related posts:
