RE: brutally simple question - number of triggers on a table
- From: "Mercadante, Thomas F (LABOR)" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'mark.powell@xxxxxxx'" <mark.powell@xxxxxxx>, Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 22 Apr 2005 14:05:10 -0400
Well said Mark. You got it right-on. The correct tool for the correct job.
-----Original Message-----
From: Powell, Mark D [mailto:mark.powell@xxxxxxx]
Sent: Friday, April 22, 2005 1:57 PM
To: 'sandeep dubey'; Oracle-L
Subject: RE: brutally simple question - number of triggers on a table
Your architect may believe that the database cannot handle a high concurrent
insert or update load on a table with a trigger on it, but in point of fact
the database can often perform the work more efficiently than the middleware
design.
Take the example of the history or audit trail for changes to table_a. For
every update to table_a a row is written to table_b. The middleware will
have to send two separate inserts to the database. The total time for the
two round trips to complete will exceed the time of one request that fires
the second insert via a trigger.
The approach you describe generally only works if 1- all access to the
target tables is via this one application and 2- there is very strong code
development and testing in force. The larger the application becomes the
more likely a middleware enforced rule will be overlooked.
Triggers are a tool. There should be used where the trigger is the best
method to guarantee that certain conditions are and remain true. There are
also tables for which it may be undesirable for triggers to be attached.
Good design is all about making the right choice for the situation at hand
and not following one rigid design methodology fits all situations and
handles all conditions approach.
HTH -- Mark D Powell --
-----Original Message-----
From: sandeep dubey [mailto:dubey.sandeep@xxxxxxxxx]
Sent: Friday, April 22, 2005 10:38 AM
To: Powell, Mark D
Cc: Oracle-L
Subject: Re: brutally simple question - number of triggers on a table
The place where I work, doesn't have a single trigger. The application
architect is of strong opinion that when business logic is stored in the
database in form of triggers when 1000+ transactions hitting the same table
at sime time, database cannot perform. Here we have rigid SLA requirements.
All business logic is stored in middleware in application which in turn
clustered and load balanced. Any backdoor data updates (from SQL) passes
through strong QA audit.
Sandeep Dubey
On 4/22/05, Powell, Mark D <mark.powell@xxxxxxx> wrote:
>
> The basic question was to use database table level triggers or not?
>
> Every action that an application takes should leave the data in a
> valid consistent state. The reality is that the only way to guarantee
> enforcement of complex business rules and data relationships is often
> to encode these rules into the database. This is done not just with
> PK, UK, and FK constraints but also using database table level
> triggers especially where an event driven process must take place.
>
> If the requirement exists that for every change made to Table_A a
> record of the change must be kept you do not trust the changing
> application to record the change. You encode this into a trigger and
> the trigger updates an audit or history table.
>
> Triggers are also useful to enforce referential integrity to a remote
> database object.
>
> Every table does not need a trigger. Most in fact probably do not,
> but there are times when database level table triggers are the only
> true reliable solution to enforcing application data integrity.
>
> IMHO -- Mark D Powell --
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
