From oracle-l-bounce@freelists.org Wed Sep 21 18:58:25 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j8LNwPCA019138 for ; Wed, 21 Sep 2005 18:58:25 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j8LNwC6H019114 for ; Wed, 21 Sep 2005 18:58:12 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 576601EEA85; Wed, 21 Sep 2005 18:57:58 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23114-02; Wed, 21 Sep 2005 18:57:58 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BD10D1EEA17; Wed, 21 Sep 2005 18:57:57 -0500 (EST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5BF07.E095E402" Subject: RE: Data auditing: triggers vs application code X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Date: Wed, 21 Sep 2005 17:54:51 -0600 Message-ID: <87E9F113CEF1D211A4C3009027301874A1053F@ddbcinc.ddbc.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Data auditing: triggers vs application code Thread-Index: AcW+5hqb9QMqe7+ASQykDFwIgnhnGwAIDjKQ From: "Justin Cave (DDBC)" To: , "oracle-l" X-archive-position: 25783 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jcave@ddbcinc.com Precedence: normal Reply-To: jcave@ddbcinc.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-2.1 required=5.0 tests=AWL,BAYES_00,HTML_60_70, HTML_FONTCOLOR_UNKNOWN,HTML_MESSAGE,RISK_FREE autolearn=no version=2.63 ------_=_NextPart_001_01C5BF07.E095E402 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I would suggest looking into Oracle Workspace Manager as Option #3. =20 - 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.=20 =20 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. =20 Application Developer's Guide - Workspace Manager http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10824/toc. htm =20 Justin Cave =20 Distributed Database Consulting, Inc. ________________________________ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Sandeep Dubey Sent: Wednesday, September 21, 2005 3:52 PM To: oracle-l Subject: Data auditing: triggers vs application code =20 Hi, =20 I need to keep track of history of data change in the live tables. Two methods are being compared. =20 1. Create triggers on the live table. For each insert/update/delete insert a row in the audit table.=20 2. Let the application take care of inserting the data in the audit table itself. =20 The cons against using triggers in the order of severity are: =20 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. =20 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).=20 =20 I would like to hear how you guys handle data auditing in your system. =20 Thanks =20 Sandeep =20 ------_=_NextPart_001_01C5BF07.E095E402 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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@freelists.org [mailto:oracle-l-bounce@freelists.org] = 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

 

------_=_NextPart_001_01C5BF07.E095E402-- -- http://www.freelists.org/webpage/oracle-l