Re: Procedure vs. many triggers

Jenny Farnham wrote:
>
> From a performance standpoint... does it make
> any difference if you have 200 triggers all doing
> the same thing (basically) on 200 fields..... or
> is it better to have one procedure defining the same
> query that is in the trigger and passing in some arguments.
> The 200 fields would call the procedure instead.
>
> Presently, I know of a program that has 200 fields written
> in Reports 2.5 with a trigger on every field. The triggers code
> in every one is the same with one exception. This exception
> would be the parameter passed in if the triggers were converted
> to one simple procedure.
>
> Would we gain anything creating a procedure and taking out the
> duplication in all the triggers?
>
> Write now the documentation that comes out of Reports 2.5 is over
> 500 pages long!
>
> --
> =======================================================
> Jennifer Farnham
> email: farnham_at_spot.colorado.edu or
> HomePage: http://spot.Colorado.EDU/~farnham/Home.html

From a performance standpoint, it depends.   If these are stored procedures in the database and triggers on tables, then YES, it should be faster using them as procedures instead of triggers. This is mainly because Oracle also stores the compiled version of the procedure in the database, but has to compile the trigger each time (unless it's still cached in memory from the last call).   If these are Forms/Reports procedures and triggers, then NO, It shouldn't make much of difference whether the code is in a trigger or a procedure.

Either way, from a coding standpoint, I think you would be MUCH better off with the one, central procedure, and having each procedure call it with the appropriate parameter(s). This will make your life much easier down the road when you have to make a change to the trigger/procedure's code.

-- 
-------------------------------------------------
Ken Johnson -  Technical Consultant
Tapestry Computing, Inc. http://www.tapestry.com