Re: Stored Procedure/Trigger Performance question

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 15 May 2001 20:28:08 +0200
Message-ID: <tg2t5odgd9d288_at_beta-news.demon.nl>


"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3B00BB80.30D82956_at_exesolutions.com...
> Scott Pawluk wrote:
>
> > Here is my scenario:
> >
> > We are a small shop using Oracle 8i for development. We don't have
 anyone
> > with professional DBA training and so our most senior programmer has
 taken
> > this role. She is currently trying to setup triggers so that we can send
 an
> > INSERT to a query with multiple tables and the trigger will handle all
 the
> > 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
 however)
> > 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
 data
> > into multiple tables on a regular basis. I'm still new to Oracle so I
 don't
> > 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
 inside
> of packages because they provide far greater flexibility, are far more
 robust,
> and are definitely easier to debug.
>
> There is little more frustrating in any environment than triggers setting
 of
> triggers setting of triggers when it comes time to test. All of which
 consist
> 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.

Regards,

Sybrand Bakker, Oracle DBA Received on Tue May 15 2001 - 20:28:08 CEST

Original text of this message