The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.

asked 23 Feb '11, 14:22

Siger%20Matt's gravatar image

Siger Matt
accept rate: 13%

edited 23 Feb '11, 17:39

Hard to tell without more details, methinks...

(23 Feb '11, 15:43) Volker Barth

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.

(23 Feb '11, 17:41) Siger Matt

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, ...

(24 Feb '11, 16:59) Volker Barth
Comment Text Removed

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:)

(24 Feb '11, 17:05) Volker Barth

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:

before insert,delete,update order 1 on
referencing old as o new as n
for each row
  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;

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).

permanent link

answered 26 Sep '12, 09:54

Markus%20D%C3%BCtting's gravatar image

Markus Dütting
accept rate: 30%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 23 Feb '11, 14:22

question was seen: 1,487 times

last updated: 26 Sep '12, 09:54