Helen Borrie wrote:

>At 05:46 PM 7/01/2005 -0800, you wrote:
>
>
>
>>I seem to remember reading somewhere that calling stored procedures from
>>triggers wasn't a good idea.
>>
>>
>
>It's news to me.
>
>
>
>>Other than (what I would think to be minimal) increase in overhead,
>>
>>
>
>Hard to tell what you mean by "minimal increase in overhead". Server-side
>processing - compiled code invoking other compiled code - reduces most of
>the things we tend to consider "overheads" - client/server traffic in
>particular.
>
>

The overhead I was referring to was primarily increased memory/resource
usage on the server. Again - I am ASSUMING that calls to nested
procedures/triggers one or two levels deep shouldn't be a concern, as
opposed to recursion cycles hundreds of levels deep.

>
>
>>why would I NOT want to use stored procedures in triggers?
>>
>>
>
>Well, you would not want to use "any old stored procedure" in "any old
>trigger".
>
>
>
>>As an example -
>>if I implement my handy-dandy auto-increment with built-in generator
>>protection, I want that same before-insert trigger in each of my umpteen
>>tables. Then, if I later want to change that behaviour - system wide -
>>I just want to have to change it once. So having a generic before
>>insert trigger procedure - called with parameters from the before insert
>>triggers, SEEMS reasonable.
>>
>>What am I missing?
>>
>>
>
>As a vague answer to a vaguely-stated hypothesis, there are some things you
>might be *able* to do in PSQL that you shouldn't do; and there are things
>you apparently think you could do in PSQL, or in triggers, that you simply
>can't.
>
>For example, this term "generic" and the idea of using the same BI trigger,
>or some generic "system-wide" trigger isn't viable. PSQL can't pass
>database objects or inherit pieces of things from here and there. A trigger
>is totally contextual to a single row, event and operation in a single
>table. A trigger belonging to one table can't be used by another table.
>
>A stored procedure is just a hunk of compiled PSQL code. A trigger is
>another hunk of compiled PSQL code. A trigger can invoke an SP, but an SP
>can't invoke a trigger.
>
>But if you're thinking along the lines of encapsulating a chunk of
>operations on specific sets that your requirements dictate should be
>performed whenever "X" operation occurs on either table A or table B or
>table C or table D or.... then it very much makes sense to put this chunk
>into an executable SP that gets called by triggers on each of these
>tables...certain kinds of global logging operations could well fall into
>this category.
>
>Under those (appropriate) conditions, a single change to the called EP
>would impact all of the triggers and other SPs that invoke it. PSQL
>modules calling other PSQL modules entails inter-dependencies, so it
>wouldn't be something you'd do on a whim, or would make part of some
>everyday end-user task.
>
>./heLen
>
>

Thank you very much - that's got to be one of the most complete "vague"
answers I've ever received from any forum post. I sincerely appreciate
your effort.

--
Daniel