Subject Re: [firebird-support] Are stored procedures in triggers a good idea?
Author Helen Borrie
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

>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

>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

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.