Re: Stored Procedure/Trigger Performance question

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 Received on Tue May 15 2001 - 00:15:44 CDT