RE: brutally simple question - number of triggers on a table
- From: "Looney, Jason" <Jason.Looney@xxxxxxxxxxxx>
- To: "'spatenau@xxxxxxxxx'" <spatenau@xxxxxxxxx>, Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 22 Apr 2005 11:09:58 -0600
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.
Jason.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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 <dubey.sandeep@xxxxxxxxx> 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.
>=20
> 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.
Steven
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
