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

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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Looney, Jason Sent: Thursday, April 21, 2005 3:40 PM
To: 'bdbafh_at_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
<ON SOAPBOX/>
  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
</OFF SOAPBOX>

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.
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.

Paul
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Fri Apr 22 2005 - 03:15:09 CDT