Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Stored Procedure/Trigger Performance question

Re: Stored Procedure/Trigger Performance question

From: Sybrand Bakker <>
Date: Tue, 15 May 2001 20:28:08 +0200
Message-ID: <>

"Daniel A. Morgan" <> wrote in message
> Scott Pawluk wrote:
> > Here is my scenario:
> >
> > We are a small shop using Oracle 8i for development. We don't have
> > with professional DBA training and so our most senior programmer has
> > this role. She is currently trying to setup triggers so that we can send
> > INSERT to a query with multiple tables and the trigger will handle all
> > proper INSERTs. She feels this is the best way for security purposes.
> >
> > Now I've done some DBA work on MS-SQL Server before (not certified
> > and from the System Administration course that I took for SQL Server
 6.5, I
> > was told that Stored Procedures are the best way to handle inserting
> > into multiple tables on a regular basis. I'm still new to Oracle so I
> > want to step on any toes unless I have to.
> >
> > My question is, which is faster? A single stored procedure to update the
> > data, or a series of triggers? I have also been told that there are
> > limitations to what triggers can access. Is this true as well?
> >
> > Thank you,
> >
> > Scott Pawluk
> > Programmer/Analyst
> > City of Winnipeg
> >
> > PS-To reply directly, remove the nospam from my email address.
> From a security standpoint they are equivalent in Oracle except with
 respect to
> the fact that procedures can be wrapped. But I strongly favor procedures
> of packages because they provide far greater flexibility, are far more
> and are definitely easier to debug.
> There is little more frustrating in any environment than triggers setting
> triggers setting of triggers when it comes time to test. All of which
> of activities that could easily be written into a single package.
> Daniel A. Morgan

I fully agree. I have been working with such a Gordian Knot system, not build by me, but generated by a third-party tool, for many months. It was driving me completely nuts.


Sybrand Bakker, Oracle DBA Received on Tue May 15 2001 - 13:28:08 CDT

Original text of this message