|
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 )) 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. |
Hard to tell without more details, methinks...
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.
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, ...
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:)