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: Fri, 22 Apr 2005 11:09:58 -0600
Message-ID: <>

While not particularly fond of triggers, foreign keys are definitely mandatory. Always. No exceptions.

I've worked in many environments where FK's weren't being enforced. At one they were actually created but disabled. This was for documentation, they said. The reason you spend tons of money on Oracle, or even SQL Server for that matter, is for a robust relational database. If you don't have relationships in it why spend the money, why not just use flat files?

There's is great section in Tom Kyte's book, "Effective Oracle by Design" about this.


-----Original Message-----
From: [] On Behalf Of jungwolf
Sent: Friday, April 22, 2005 10:39 AM
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.=20 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 - 13:14:24 CDT

Original text of this message