Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Unanswered: Triggers/Stored procedures

    Is it possible for a table to have more than one trigger? Each trigger would call its own stored procedure.

    Does it make sense to have more than one trigger on a table?

    Would this have an impact on performance?

  2. #2
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    Yes; they're fired in the order that they're created.
    Sometimes, but only you can decide.
    Yes, so you'll need to determine if the cost is justified. The impact on performance depends on what the trigger/stored proc is doing.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You also need to look at it from a maintenance point of view. Since you can only influence the order the triggers are fired in by when they are created, you might have a problem if one of them needs to be changed. Either you change the one and the firing order is now different, or you have to recreate all of them is the order you want. It would be much simpler to have just one trigger with the SP calls in the order you want.

    Andy

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If the triggers are not too complex, you would just shift the problem from the trigger-creation to the stored procedure definition.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    And please do your homework before creating multiple triggers on 1 table. I worked at a client that prohibited triggers because their experience convinced them that DB2 triggers were performance killers. Seems some genious created 100, that's right, 100 triggers on a table each calling a stored procedure and then everybody wondered why everything slowed down. How this ever made it to production is a testament to the unstoppable force of idiocracy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •