locked
Not to replicate user triggers in merge replication RRS feed

  • Question

  • Hi All

    On: SQL server 2008 r2 std -> sql server 2008 r2 express

    We have a trigger on a replicated tables that uses merge replication. We do not want that trigger to be replicated to the subscriber.

    But even though we set "replicate user triggers" to false for that article, every time the publication needs to be re-initialized, the trigger is copied to the subscriber and we have to delete it there.

    In normal merge-replication, when no snapshot is involved (reinitialization), the trigger is not replicated again.

    On a test server with a test merge replication I dont have that problem - option "replicate user triggers" behaves as expected.

    Has anyone a clue how we can fix this? Or what can cause this?

    thx in advance

    Sunday, July 1, 2012 10:56 PM

Answers

All replies

  • I have run into this before and never got to the bottom of why this was.

    I normally create my triggers like this

    Create Trigger TriggerName on TriggerTable

    After Insert,update, delete

    as

    begin

    if @@ServerName='ProductionServerName'

    Begin

    --I place the code I only want executed at the publisher


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Proposed as answer by amber zhang Tuesday, July 3, 2012 3:38 AM
    • Marked as answer by DevonOne Thursday, July 5, 2012 9:58 PM
    Monday, July 2, 2012 1:54 PM
    Answerer
  • Another workaround would be to have a post snapshot script that checks and removes the trigger if needed.

    Brandon Williams (blog | linkedin)

    • Proposed as answer by amber zhang Tuesday, July 3, 2012 3:38 AM
    Monday, July 2, 2012 4:06 PM
  • Thanks for your answers. In our case, using  ... if @@ServerName='ProductionServerName' ... should be a good workaround.

    But can you confirm that it happens only to some installations of SQL server? As I said, in a test system, I didn't get the problem.

    Thursday, July 5, 2012 9:51 PM