Replication and User defined Trigger

• rockdale

  Newbie

  Points: 5

  More actions

  March 25, 2006 at 4:39 pm

  #93733

  Hi, all:

  We have 5  databases which contains invoice and invoiceHistory tables.

  We have an update and insert trigger on invoice table to record every

  modification into InvoiceHistory table. The invoiceHistory table is not

  read only, it has some fields that the end user can edit (comments,

  date ...etc).  At the same time, we using scheduled snapshots every 5

  minutes to 'refresh' the table at the suscribers. The problem is when

  the replicate server replicate table invoice to a subscribe, the

  trigger on invoice table will be triggered and update/insert

  InvoiceHistory table in the subscribe. Then when the replicate server

  replicate table InvoiceHistory, we will get duplicate record in

  InvoiceTable, one is from Invoice table trigger and another is from

  InvoiceHistory replicate.

  If we do not replicate InvoiceHistory table, then those fields that end

  user updated can not be sync to subscribes.

  Does anybody there have a better idea how to do something like this?

  Thanks

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  March 26, 2006 at 4:42 am

  #628792

  Are you sure that it is Snapshot replication you are using?  My understanding of the snapshot replication is that it is essentially a backup on server A and a restore on server B?  In this case, triggers wouldn't fire?

  Anyhow, you can mark triggers (and other things like DRI constraints) as Not for Replication - this means that the trigger (or DRI constraint, etc) is not executed during the data insert/update process of replication.  This should fix your problem - the trigger will fire only on your first server and the results of the trigger are replicated to the 2nd server, but the trigger itself will not execute on the 2nd server.

  Good luck

• Puneet Malhotra-314029

  SSC Enthusiast

  Points: 104

  More actions

  March 27, 2006 at 5:17 am

  #628852

  Why do you require the triggers to be turned on at the subscriber's end? you can simply turn off the triggers at subscriber's end and have this issue resolved or

  As Ian stated above, mark the trigger at the subscriber as "Not for Replication", then it will not fire during replication process and the results will be updated by Distribution Agent only.

  Cheers,

  Puneet