Re: Stored Procedure/Trigger Performance question

Scott,
  From a security perspective. Either will work. Insert access to a table is granted to role/user unless the insert is done from a stored procedure, the stored procedure is then granted execute access to a user/role.

 The trigger that runs after the insert to populate could call a stored procedure or have the plsql imbedded in the trigger depending on complexity. Trigger code cannot select from table being inserted/updated (mutation will occur), this also applies to stored procedure being called from it. (inserts from table1 - sp - insert table2)

The implementation style is dependant on how granular the process needs to be. Would you want some triggers to always fire an insert? Is their a need to turn on/off portions at times?

Both plsql trigger/sp are stored code in the database and can be equally fast. Stored procedures can be wrapped to hide the code if need be.

hth,
Dave Fowler
Oracle DBA/Developer

"Scott Pawluk" <spawluk_at_nospam.city.winnipeg.mb.ca> wrote in message news:SLRL6.982$U61.16728_at_news2.mts.net...
> 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.
>
>
Received on Mon May 14 2001 - 14:32:43 CDT