From oracle-l-bounce@freelists.org Fri Apr 22 11:52:17 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3MGqH7r003894 for ; Fri, 22 Apr 2005 11:52:17 -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 j3MGqF4Z003887 for ; Fri, 22 Apr 2005 11:52:16 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 57629186867; Fri, 22 Apr 2005 10:14:02 -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 18788-07; Fri, 22 Apr 2005 10:14:02 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D2942186860; Fri, 22 Apr 2005 10:14:01 -0500 (EST) Subject: RE: brutally simple question - number of triggers on a table Date: Fri, 22 Apr 2005 09:12:59 -0600 MIME-Version: 1.0 To: bdbafh@gmail.com, oracle-l@freelists.org Cc: From: William B Ferguson Message-ID: X-MIMETrack: Serialize by Router on gscodenm05/SERVER/USGS/DOI(Release 6.5.2|June 01, 2004) at 04/22/2005 09:12:12, Serialize complete at 04/22/2005 09:12:12 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-archive-position: 18823 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: wbfergus@usgs.gov Precedence: normal Reply-To: wbfergus@usgs.gov X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.63 Well, in my case the answer varies. I cannot rely on external appliactions to enforce data integrity, so I HAVE to do as much as I can within the table and field definitions as I can, including the use of triggers. People can access the data either through my HTML DB interface, Microsoft Access, ARC, etc. I have no control over any of those other applications, so whatever control I can enforce through the database gives me fewer headaches. I've just ended 9 YEARS of data cleanup from a database (this one's predecessor) where NO data validation or integrity contrainst existed, and everything was defined as a LONG datatype, with multiple entries allowed per field and using different delimiters to separate the multiple entries. I really, really wish that someday I could meet the creator of that monstrosity! I will not ever allow that kind of garbage into this database, as I can't even trust the users to correctly spell "Cordillera". There were over 50 different variations of that in the existing data that I had to cleanup and create LOV's for. But, getting back to triggers, my primary table has three fields, holding the child table name, date and type of change to any data related to that record. Each child table has a trigger to update the parent table when a change is made (Insert, Update or Delete), and I'm also real liberal on views, so updates to the views are handled through Instead_Of triggers. Does that NOT answer your question? :^) ------------------------------------------------------------ Bill Ferguson U.S. Geological Survey - Minerals Information Team PO Box 25046, MS-750 Denver, Colorado 80225 Voice (303)236-8747 ext. 321 Fax (303)236-4208 > -----Original Message----- > From: oracle-l-bounce@freelists.org > [mailto:oracle-l-bounce@freelists.org] On Behalf Of bdbafh@gmail.com > Sent: Thursday, April 21, 2005 2:52 PM > To: oracle-l@freelists.org > Subject: brutally simple question - number of triggers on a table > > Just looking for opinions, unsubstantiated accepted, > substantiated - even better. > > Q: How many triggers on a table is too many? > > 1. any (all the business rules and constraints belong in THE > application). > 2. one massive trigger/package for all app owner tables. > 2. two (all that is required is one). > 3. one for each triggering event type. > 4. they're free ... as many as all the developers desire 5. > what's a trigger? > 6. other. > > thanks. have fun. > > Paul > -- > http://www.freelists.org/webpage/oracle-l > -- http://www.freelists.org/webpage/oracle-l