none
Replication 2005/2008 Keep Subscriber Table Triggeri

    Frage

  • Hi,

    I have a SQL 2005 DB that has snapshot replication running on it against a DB that is on a 2008 SQL server. 

    My issue is this When the snapshot runs, it wipes out a trigger that is on the 2008 DB only. So it has to be manually recreated each time replication runs (which is only about once a month). How do I prevent replication from removing the trigger on the 2008 SQL table? Note I cannot change the publisher subscription, or modify the 2005 DB

    Any help would be greatly appropriated. 

    Thanks,

    Ian


    Dienstag, 26. Februar 2013 20:12

Antworten

  • Please correct me if I am wrong, let's say You have snapshot publication on SQL Server 2005 database dbtest on table testtbl. The subscriber is SQL 2008 database which has a table testtbl which has a trigger defined. When the snapshot is applied to subscriber the trigger gets deleted correct? and this trigger does not exist in SQL 2005 database table?

    When the snapshot is applied, by default  the table gets dropped and recreated with the schema taken from publisher database table which is why the trigger gets deleted when snapshot is applied.

    You may add the create trigger script to the "Run additional scripts" in snapshot tab of publication properties, so that the trigger gets created every time the snapshot is applied.

    • Als Antwort markiert Ian.Keogh Mittwoch, 27. Februar 2013 13:34
    Dienstag, 26. Februar 2013 20:59

Alle Antworten

  • Ensure that the article property Copy user triggers is set to True.  This should prevent you from having to manually recreate the trigger(s) each time the snapshot is applied.

    If this does not work for you, then you could deploy the triggers using a pre or post snapshot script.  Have a look at Execute Scripts Before and After the Snapshot Is Applied.

    If you want to prevent replication from removing the trigger when the snapshot is applied then you will need to modify the article property Action if name is in use to something other than Drop existing object and create a new one.


    Brandon Williams (blog | linkedin)

    Dienstag, 26. Februar 2013 20:53
    Moderator
  • Please correct me if I am wrong, let's say You have snapshot publication on SQL Server 2005 database dbtest on table testtbl. The subscriber is SQL 2008 database which has a table testtbl which has a trigger defined. When the snapshot is applied to subscriber the trigger gets deleted correct? and this trigger does not exist in SQL 2005 database table?

    When the snapshot is applied, by default  the table gets dropped and recreated with the schema taken from publisher database table which is why the trigger gets deleted when snapshot is applied.

    You may add the create trigger script to the "Run additional scripts" in snapshot tab of publication properties, so that the trigger gets created every time the snapshot is applied.

    • Als Antwort markiert Ian.Keogh Mittwoch, 27. Februar 2013 13:34
    Dienstag, 26. Februar 2013 20:59
  • yes, you are right.  you can apply the trigger script after the snapshot is applied.
    Additionally, As Brandon mentioned, you can change the "Action if OBJECT exits" in the article properties and set it to 
    Truncate data or delete data, this is will leave the trigger in place on the subscriber and you still get the new data...

    But the usual disadvantage with second method is that, it will not propagate any changes made to the definition of objects..like if you choose to copy your Non clustered index and  later, changed the NC index and added 2 more columns  on the publisher, the changed index will not be replicated because index already exists(with old defintion)...


    Hope it Helps!!



    • Bearbeitet Stan210 Dienstag, 26. Februar 2013 21:35
    Dienstag, 26. Februar 2013 21:20
  • Thanks to all 3 of you guys. this has helped me out a lot.
    Mittwoch, 27. Februar 2013 13:35