Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Posts
    19

    Unanswered: Stored Procedure Call From Trigger - Best Practices?

    Hi,

    I am working with a web based ECommerce system and from that system we replicate orders in real time back to the host ERP system.

    We are contemplating using a trigger to call the stored procedure that is responsible for replicating this order data.
    Lots of people suggest not to call SP's from a trigger but in this case we can discreetly define what columns will cause replication as well as the fact that it is much simpler using a trigger since that integration code only lives in one place instead of having to be called from the many insert/update procs that exist in the Ecommerce app.

    Obviously if someone goes into the DB directly and updates all the orders at once for some reason we would have an issue while everything was replicated but in preliminary testing this method seems to be the simplest and quite fast.

    Anyone had any bad experiences with this or a reason to absolutely not do it?

    thanks in advance.

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Are there any particular reason why you don't want to use transactional replication in this case?
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jun 2008
    Posts
    19
    Quote Originally Posted by roac View Post
    Are there any particular reason why you don't want to use transactional replication in this case?
    Hi Roac,

    Good thought but I think that based on the business scenario/rules transactional replication really isn't a good fit here. We have written Sp's that are called which handle data validations, and various custom rules and are stored directly with the host system so that is why we do it this way in this case.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Do not do this unless your stored procedure can handle set based INSERTs, UPDATEs and DELETEs. The biggest problem here is that people fundamentally misunderstand how triggers work. So let me ask you this question.

    If an update statement updates 10 rows, how many times does your trigger fire?

    If you siad once, you would be right. If you said 10 times and your stored procedure is only setup for single row operations, your whole scheme is brittle and doomed to fail.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2008
    Posts
    19
    Quote Originally Posted by Thrasymachus View Post
    Do not do this unless your stored procedure can handle set based INSERTs, UPDATEs and DELETEs. The biggest problem here is that people fundamentally misunderstand how triggers work. So let me ask you this question.

    If an update statement updates 10 rows, how many times does your trigger fire?

    If you siad once, you would be right. If you said 10 times and your stored procedure is only setup for single row operations, your whole scheme is brittle and doomed to fail.
    Yes, you are correct and yes the trigger is setup to handle set based operations. Additionally, the trigger tests for only certain columns that have been updated because many of the columns involved do not need to trigger any write back to the host system. Because of these potential issues we try to keep its actual work to the minimum amount necessary.

    thanks

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    The other potential problems, and every solution has some, include any ETl operations that might disable triggers.

    Another problem I have seen with things like this, has to do with not having any thresholds enforced in your DML operations. What happens to my performance overhead with said trigger if I update or insert a million rows all at once. The inserted andf deleted tables are going to manifest million row sets in the tempdb. It isn going to have a performance impact, right?

    Threshholding both reporting and DML stored procedures is a database development topic I feel does not get enough attention. By thresholding I mean placing upward bounds in how many rows you will let your application return or manipulate. It is usually one of the things I introduce new employers too. It is crucial for well performing systems.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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