SQL Server trigger on replication

Question

I need to create a SQL Server database that will recieve updates by some replication mechanism from another database. I need to write insert, update and delete triggers that will execute when this replilcation occurs.

I have experience with triggers but not with replication.

Should I use Transactional or Merge replication, or does it matter?

Will a trigger designed to run when a simple SQL insert statement is executed also run when replication occurs?

Answer 1

The CREATE TRIGGER syntax on MSDN:

CREATE TRIGGER
...
[ NOT FOR REPLICATION ]

This indicates that executing on replication is the default behaviour for triggers, and can be disabled by specifying NOT FOR REPLICATION.

Answer 2

Hi,

Well it depends.

If the updates that you intend to apply are to isolated tables i.e. all the data for a given table comes from the publisher only, then you can use transactional replication.

If on the other hand you are looking to combine table content i.e. an orders table, with orders being placed at both sites, then you would want to look into using merge replication.

With regard to triggers, there is a "not for replication" configuration that you can apply to control their behaviour. See the following article for reference.

http://msdn.microsoft.com/en-us/library/ms152529.aspx

Cheers, John

Answer 3

It's hard to answer your question with the information you've provided. I added a few comments to your question asking for clarifying information.

Here is an article on MSDN that should help: http://msdn.microsoft.com/en-us/library/ms152529.aspx

By default, triggers will fire during replication unless "NOT FOR REPLICATION" is specified. They work the same way as they do for simple insert statements.

Transactional and Merge replication are very different, but triggers behave similarly for both options.

Answer 4

There are a few alternative options open to you instead of triggers.

1. You could modify the replication procedures on the subscriber (destination) database.
2. If using 2008 you can use Change Tracking on the subscriber for tables you want to "do something with" and then create a batch process to deal with "set based" data instead of invididual rows. E.g. an SSIS package that runs every X.