From oracle-l-bounce@freelists.org Fri Apr 22 03:15:09 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3M8F9YS022681 for ; Fri, 22 Apr 2005 03:15:09 -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 j3M8F94Z022677 for ; Fri, 22 Apr 2005 03:15:09 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2AA6E185E15; Fri, 22 Apr 2005 02:12:48 -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 14102-04; Fri, 22 Apr 2005 02:12:48 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9C18C185E2B; Fri, 22 Apr 2005 02:12:47 -0500 (EST) X-Amazon-Corporate-Relay: smtp-out-1002.vdc.amazon.com X-AMAZON-TRACK: oracle-l@freelists.org X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: RE: brutally simple question - number of triggers on a table Date: Fri, 22 Apr 2005 00:10:55 -0700 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: brutally simple question - number of triggers on a table Thread-Index: AcVGwzOjo/vWEDChSESHgV/QiPJuUwARkLXA From: "Parker, Matthew" To: , , "Oracle-L" X-archive-position: 18785 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: matthewp@amazon.com Precedence: normal Reply-To: matthewp@amazon.com 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.1 required=5.0 tests=AWL autolearn=ham version=2.63 You do not need to take an outage for mass updates. It just requires you = to put session environment code in to ignore the triggers when you are = going to perform mass updates.=20 The real key to triggers is there is a base performance hit for every = trigger fire. I normally try not to exceed 3 triggers per any table.=20 The issue of same place to store your code, is an argument many use to = not store any business logic in the database. It is totally up to your = application designers as to where the code should lie based on the needs = of the application, system performance, availability, and reliability. = If there is not complete control of the application, (which is the case = at many sites), then triggers are the way to reign in disparate = applications. As in all areas, every choice has an upside and a downside. -----Original Message----- From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org] On Behalf Of Looney, Jason Sent: Thursday, April 21, 2005 3:40 PM To: 'bdbafh@gmail.com'; Oracle-L Subject: RE: brutally simple question - number of triggers on a table Paul, It depends on your requirements. I have always felt that if you have = an environment, where you need to have high availability, then triggers are = not for you. The fact is that when you modify a trigger or need to disable = the trigger for mass updates it requires a downtime for the entire = application. On the other hand, if you don't have real-time applications and you = can afford to be down hours a night whenever there is a substantial data = fix, go ahead. Put some triggers out there. It's easier, because you don't = have to fix all the places in the code that perform that particular task. I have always felt that there were too many risks with using triggers. Not only is there the possibility of having unintended consequences when doing an update (can you tell I've been burned by this more than once?) = but if you are "designing" an application then shouldn't you have the code, = no matter what language (PL/SQL, Java, C#), broken down into logical compartments for reuse? If that's the case then what is the difficulty = with maintaining all your business driving code in one logical place? = Triggers are not that place. =20 Let me continue by saying that if your application uses multiple languages, shouldn't your business logic code be accessible by the = lowest common denominator language, PL/SQL? That way all your code, and any scripts that need to run to fix the data, can be used by a common code = base. What is the argument for placing the business components in middleware? It's the same! Place all your code in a location where it can be = reused, but developers many times forget that data conversions and much of the = fixes for the data are done through PLSQL, negating the benefit of code unification. You then need to have two code bases, which will = undoubtedly get out of sync.=20 Hope that helps. If it doesn't, that's ok. It was good therapy for me anyway. Jason. 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 -- http://www.freelists.org/webpage/oracle-l