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