Geek Answers Handbook

SQL Server Trigger During Replication

I need to create a SQL Server database that will receive updates using some replication mechanism from another database. I need to write insert, update, and delete triggers that will be executed when this replication occurs.

I have experience with triggers, but not with replication.

Should I use transactional replication or merge, or does it matter?

Will a trigger fire when a simple SQL insert statement is executed and fire when it is replicated?

+4
source share
4 answers

Well, it depends.

If the updates you intend to apply relate to isolated tables, that is, all data for this table comes only from the publisher, then you can use transactional replication.

If, on the other hand, you want to combine the contents of the table, that is, the order table, with orders placed on both sites, then you will want to explore the use of merge replication.

As for triggers, there is a non-replication configuration that you can use to control their behavior. See the following article for reference.

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

Cheers, John

+4
source

CREATE TRIGGER syntax on MSDN :

CREATE TRIGGER ... [ NOT FOR REPLICATION ]

This means that replication is the default behavior for triggers and can be disabled by specifying NOT FOR REPLICATION .

+4
source

It is not possible to answer your question with the information you provided. I added a few comments to your question asking for clarification.

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

By default, triggers will be triggered during replication if "NOT FOR REPLICATION" is specified. They work the same way as for simple insert instructions.

Transactional and merge replication is very different, but triggers behave the same for both parameters.

+2
source

There are several alternatives available to you instead of triggers.

  • You can change the replication procedures in the database of the subscriber (recipient).
  • If you use 2008, you can use Subscriber Change Tracking for tables that want to "do something" and then create a batch process to process the "installed databases" instead of invisible rows. For instance. SSIS package that runs every X.
0
source

More articles:


All Articles