Procedure vs Trigger Performance Decision

[former_SQLA_member1694868]

It seems to me that instead of having two triggers (one on insert/delete, one on update of column) with the same sql statements to recalculate values in another table based on what just changed, it would be better to have those two triggers both call one stored procedure that contained the sql statement.

I think it would be easier to administer if there were two triggers calling one procedure, as the actual statements would only need to be changed in one place.

What are the performance considerations one way or the other?

What other factors should I consider?

Edit: More Details

The basic structure is setup like items on a receipt, that are then discounted by a coupon either individually or for the total bill. An insert or update to the cost of an item or to the discount rate needs to force a recalculation of the total amount. The trigger/procedure in question basically uses criteria to find the best "discount" for the item.
Every now and then I run into a situation where this needs to be done manually. Without a way to manually trigger the trigger, I moved the trigger statements and logic into a procedure. Once I looked at it I realized I would have to keep track of the changes twice, and wondered what the implications would be if I simply changed the trigger to refer to my new (seemingly identical) procedure.

[VolkerBarth]

Hard to tell without more details, methinks...

[former_SQLA_member1694868]

The real situation is a bit more complicated but that is the best I can boil it down to explain it.

The fact that you did not recoil with shock and horror: "What?!? That will take twice as long in a trigger calling a procedure than a trigger by itself!" probably means for my situation it will not matter much either way.

[VolkerBarth]

As always, performance implications might arise when your trigger is called very frequently, and then the additional procedure call might slow down your database (and thereby might increase the chance of locking conflicts). However, I'd usually prefer more maintainable code, and when your procedure does reach that, I'd think of it as an useful approach. But as said before, without knowing more details, ...

[VolkerBarth]

On the other hand, I'd generally try to get rid of the need to modify rows in another table. Could the summed up/calculated value in the other table be replaced by a simple view that does the calculation on the child table "during runtime"? Or if this is to expensive, might a materialized view be useful here (possibly in the immediate form)? - Both variants (and there might be more) might lead to a "never have to maintain that anymore" situation... Just wild guesses, of course:)

[Former Member · Accepted solution]

There should be next to no performance difference if you call a procedure in a trigger against copying the "content" of it directly into the trigger, imho. It's executed/interpreted in the same way, me thinks, if that's the question you asked. Only the call overhead and that should be, and to my personal experience is, quite low (if you dont' have lots and lots of "big" parameters).
It has the added benefit of being able to make changes to the procedure even if the table is locked. We sometimes use this "technique" for triggers in busy tables just for this added flexibility.

BTW you know that you can "carry out actions depending on the kind of action that caused the trigger to fire"? Try to look for Trigger operation conditions in the help ( INSERTING | DELETING | UPDATING [ ( column-name-string ) ] | UPDATE ( column-name ))

Stupid Example:

ALTER TRIGGER "InsUpdDel"
before insert,delete,update order 1 on
DBA.Table1
referencing old as o new as n
for each row
begin
  declare LfNr integer;
  if deleting then
    set LfNr = o.LfNr
  elseif inserting then
    set LfNr = n.LfNr
  elseif updating then
    set LfNr = n.LfNr //o.LfNr
  end if;
  update dba.table2 set Column2 = LfNr where Column1 = 42;
end;

Doesn't help you when you want to call it manually, but you could use only one trigger.
I would personally prefer "the trigger calls the procedure" way in this situation (Don't Repeat Yourself).