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.
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).
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 ))
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.
answered 26 Sep '12, 09:54