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: William B Ferguson <>
Date: Fri, 22 Apr 2005 09:12:59 -0600
Message-ID: <>

Well, in my case the answer varies. I cannot rely on external appliactions to enforce data integrity, so I HAVE to do as much as I can within the table and field definitions as I can, including the use of triggers.

People can access the data either through my HTML DB interface, Microsoft Access, ARC, etc. I have no control over any of those other applications, so whatever control I can enforce through the database gives me fewer headaches. I've just ended 9 YEARS of data cleanup from a database (this one's predecessor) where NO data validation or integrity contrainst existed, and everything was defined as a LONG datatype, with multiple entries allowed per field and using different delimiters to separate the multiple entries. I really, really wish that someday I could meet the creator of that monstrosity!

I will not ever allow that kind of garbage into this database, as I can't even trust the users to correctly spell "Cordillera". There were over 50 different variations of that in the existing data that I had to cleanup and create LOV's for.

But, getting back to triggers, my primary table has three fields, holding the child table name, date and type of change to any data related to that record. Each child table has a trigger to update the parent table when a change is made (Insert, Update or Delete), and I'm also real liberal on views, so updates to the views are handled through Instead_Of triggers.

Does that NOT answer your question? :^)

Bill Ferguson
U.S. Geological Survey - Minerals Information Team PO Box 25046, MS-750
Denver, Colorado 80225
Voice (303)236-8747 ext. 321 Fax (303)236-4208  

> -----Original Message-----
> From:
> [] On Behalf Of
> Sent: Thursday, April 21, 2005 2:52 PM
> To:
> 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?
> 1. any (all the business rules and constraints belong in THE
> application).
> 2. one massive trigger/package for all app owner tables.
> 2. two (all that is required is one).
> 3. one for each triggering event type.
> 4. they're free ... as many as all the developers desire 5.
> what's a trigger?
> 6. other.
> thanks. have fun.
> Paul
> --

Received on Fri Apr 22 2005 - 11:52:17 CDT

Original text of this message