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: Guerra, Abraham J <>
Date: Fri, 22 Apr 2005 14:22:31 -0500
Message-ID: <>

Whatever happened to the good old;

select max(num_pk) from table; to populate the PK? ;-)

It's a joke, get it? It's a joke!

Abraham Guerra
-----Original Message-----
[] On Behalf Of Mercadante, Thomas F (LABOR)
Sent: Friday, April 22, 2005 12:31 PM
To: ''; Oracle-L
Subject: RE: brutally simple question - number of triggers on a table

The app will *never* keep the business rules in force. It will always fail.
It always fails because it depends on humans to correctly code the app. And
we all know what happens with the contracts that are let out to contractors
- never enough time for proper code testing.

The only place to do it is in the database. At a minimum, foreign and primary keys are required wherever I go.

Triggers come into play when needed - but they are not always needed.

-----Original Message-----
From: jungwolf []=20 Sent: Friday, April 22, 2005 12:39 PM
To: Oracle-L
Subject: Re: brutally simple question - number of triggers on a table

On 4/22/05, sandeep dubey <> wrote:
> 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

I worked on a project (as a physical DBA) where the application was built on a java framework. Instead of calling sql directly, all developers were required to use the objects that contained the business logic enforcement. QA processes, stated policies, no rogue SQL, etc.

The contracting company (developing the app) swore up and down these measures would keep everything consistent and that triggers (or FKs) were unnecessary. Would in fact make scaling the app impossible. You know where this is going.

A skeptical employee finally did some validation SQL (hard to do since the dev company kept the code and design close to their chest (badly written contract...)). FK violations everywhere. History records missing. An amazing array of business rules violated. Somehow, statements breaking the business rules still made it into the application.

After first denying and then grudgingly admitting the problem, FKs sprung up everywhere and triggers were in the works. I left during this time, but the last I heard the dev company lost the contract.=3D20 They might have been right about the scalability issue but the data corruption just wasn't acceptable.

Everywhere I've worked, eventually _someone_ bulls their way into having naked access to the database. I know triggers have their issues (scalability, hidden logic, unintended consequences, etc.). At this point I don't know a better way to enforce essential business rules. Maybe verification and correction scripts run periodically, if temporary data discrepancy is okay.

That's just an anecdote but it is one reason why I am skeptical when someone tells me the app will keep the data consistent.


Received on Fri Apr 22 2005 - 15:27:02 CDT

Original text of this message