Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: brutally simple question - number of triggers on a table

RE: brutally simple question - number of triggers on a table

From: Looney, Jason <>
Date: Thu, 21 Apr 2005 16:40:01 -0600
Message-ID: <>


  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.  

  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.

Hope that helps. If it doesn't, that's ok. It was good therapy for me anyway.


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.
  3. two (all that is required is one).
  4. one for each triggering event type.
  5. they're free ... as many as all the developers desire
  6. what's a trigger?
  7. other.

thanks. have fun.

-- Received on Thu Apr 21 2005 - 18:44:24 CDT

Original text of this message