Post reply

Merge Replication triggers and application triggers conflict

  • December 13, 2005 at 9:41 am

    #88838

    In our environment, we have an entire process in place where triggers on certain tables take data inserted, updated, and deleted and pass that data to other tables.  The process then checks that data against further data and then makes the appropriate updates to the data where necessary.  Other triggers then fire off after the update and take the 'cleansed' data and update the original data within the original table(s).

    Merge replication (and it's triggers) are taking both the original insert and replcating it (as it should be).  But after the process explained above has completed, merge replication sees the update to the original data (with the 'cleansed' data) as another insert and then makes a duplicate record in the same table on the subscriber. 

    What am I missing, and how can I get this to stop?

    Scottye

  • December 13, 2005 at 5:22 pm

    #609358

    Very confusing.

    Are you absolutely positive that your applicatino triggers are NOT creating new rows?

    SQL Merge replication keeps only references to the records and columns that have changed, based on the ROWGUID column.

    It uses that rowguid to determine if there is a record present on the subscriber and then updates or inserts accordingly.

    Without a new ROWGUID column, I can't see how it would create new records at the subscriber.

    Not much help I know, but I feel for you. This sounds like a doosie.

    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • December 13, 2005 at 6:24 pm

    #609370

    Do the duplication happen on the merge? or is it the actual trigger causing the duplication??

    Regards

    Adam


    ------------------------------
    Life is far too important to be taken seriously

  • December 14, 2005 at 5:31 am

    #609428

    Have you added the not for replication clause when creating your triggers

     

    Regards

     

    William

  • December 14, 2005 at 10:12 am

    #609519

    Hope, You have set the application level trigger to NFR(not for replication)

     

    Thanks,

    -Nirmal.

  • December 14, 2005 at 11:13 am

    #609530

    All,

         Thanks for the responces... I'll try to answer (or give more info) within this one reply.

          ALL of the triggers MUST be replicated.  This environment is an extreme case of 'high availability'.  I have set the environment for merge replication to satisfy my Disaster Recovery compliance (I know, I know... what a way to test DR... set up the replication and let it cause your first scenario of Production DR... sucks don't it?).

          This is a highly transactional environment also.  That is why I opted to go the route of Merge Replication.  Transactional wouldn't do, because if Production failed, I wanted the DR server to pick up (off-site... so clustering was not an option either) and 'Production' could resume.  However, the data replicated to the DR box is not Read-only.  So, transactional wouldn't work.  I couldn't even deal with the whole time latency issues that Log Shipping  incurrs (though it was considered a possible solution), because again this is a DR, highly transactional, 24/7 operation.

          The whole trigger thing has become a nightmare.  But, they are there to support the application and it's processes (oh yeah, which interestingly enough to note... is SAP.  Another German made piece of crap, that ranks right up there with Audi, Daimler, you get the point!).  Can you tell I'm slightly hacked?  The whole thing has basically created a type of 'recursive loop' scenario that I'm probably going to have to generate some pretty outlandish code to handle the duplications and data checks against all the tables (my SQL Programming teacher would be proud and laughing his 'you know what' off and thinking that he knew I should have tried to stay awake a little more in class).

          If any of you out there feel my pain, please feel free to respond (that way, at least, I'm not feeling all alone in this).  Who knows, maybe Andy can write another My Whole System Shot Craps And This Is How I Got Through It:  Using Jack Daniels and Marlboro to fix high availability, Production SNAFU's.

    Scottye McClain

  • December 15, 2005 at 1:57 am

    #609648

    Hi Scottye,

    Using the not for replication option does not mean that the trigger is not replicated, it means this (from BOL)

    NOT FOR REPLICATION

    Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.

    So if you have not set this option on the trigger then you have the same situation as I did a few months ago, ie local database record is inserted, trigger fires and inserts into other table, both inserts are recorded for replication, on replication initial insert occurs, trigger fires inserting the record, and the record is also inserted in that table via replication.

    Kind Regards

     

    William

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply