Subject Re: Are stored procedures in triggers a good idea?
Author Adam
Yes it is a good thing to do.

Forgetting the generator protection specifics that a lot of people
disagree with, calling a SP from a trigger is actually quite helpful.

What you will find is that in many cases, the before insert and before
update triggers contain a significant amount of common code. If you
duplicate the code in both triggers, then find a bug you will have to
remember to fix it twice. If however you called a SP that was the
common code, you could simply fix the SP. Taken further still, often
changes to multiple tables need to execute the same code.

Now the caveat.

SPs will make you stop thinking about what you are doing. If you
update tableA, the before update trigger(s) will be run on it. Lets
say this trigger calls SP_a. You may not remember that SP_a actually
updates tableB which in turn will force the before update trigger(s)
to run on tableB. If you are not careful, the update trigger on tableB
might try and update tableA and that is not good.

--- In, "Daniel L. Miller"
<dmiller@a...> wrote:
> I seem to remember reading somewhere that calling stored procedures
> triggers wasn't a good idea. But now I'm not Googling a reference to
> that. So I'll ask from scratch:
> Other than (what I would think to be minimal) increase in overhead, why
> would I NOT want to use stored procedures in triggers? 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
> 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
> triggers, SEEMS reasonable.
> What am I missing?
> --
> Daniel