Replication of triggers

Hello !

I have set up some transactional replication on one of my database but I can't see where I can specify that we should replicate the triggers. What I have seen is that the triggers are automatically replicated when the table is replicated except id they are marked "Not for replication"

But it does not seem to work for me. Do you have any idea ?

Thanks,

Hi Jarod,

I am not sure if my following suggestion applies to your scenario or if you have already tried it already, but you can enable the 'Copy user Triggers' schema option on the article properties page to have your triggers replicated with the initial snapshot. You can access the article properties page as follows: right-click publication->Properties->Select Articles on the right plane->Highlight articles you want to change->Press Article Properties button. I would recommend that you mark all your triggers as 'NOT FOR REPLICATION' beforehand as having replicated changes firing user-defined triggers at the subscriber will likely produce unexpected behavior since changes caused by triggers at the publisher will also be replicated.

Hope that helps,

-Raymond

|||

Hello Raymond

I have checked the options and you were right the "Copy User triggers" was not checked. So I modified it, reinitialize my subscriptions, create a new snapshot and launch the synchronization but my triggers are still not replicated.

I tried also to mark my triggers as NOT FOR REPLICATION but nothing change.

Do you have another idea ?

Thanks,

|||

Hi Jarod,

The snapshot agent does bypass scripting of replication generated triggers, encrypted triggers, and triggers that are marked as system object but I doubt all of your triggers fall into one of these categories (the snapshot agent should print out messages for the last two). That said, it certainly doesn't hurt to check whether that was the case. You may also want to check whether your trigger definitions have been removed from sys.comments as many application vendors try to protect their intellectual property that way. Marking a trigger as NOT FOR REPLICATION shouldn't affect whether a trigger is replicated or not once the 'Copy user triggers' option is enabled. And just to make sure that the 'Copy user triggers' option was indeed saved and working properly, can you check whether the snapshot agent had generated any .trg scripts and report back here? If the situation gets really desparate, you can replicate a small table with a simple trigger on the side as a reference point to isolate what the problem might be in your original publication. And to eliminate some obvious cases, are the publisher and the subscriber SQL2000 or SQL2005 servers?

-Raymond

|||

Hello Raymond

my triggers do not fall in these categories.|||

Jarod, this looks like a rather strange bug. Can you check the following for me:

1) Do any of the .trg scripts generated by the snapshot agent contain anything?

2) Did the subscriber database start off empty?

3) Try calling sp_changepublication <yourpublicationname>, 'sync_method', 'native', 1, 1, regenerate and reapply the snapshot and see if the triggers are still not replicated properly?

4) Do any of your missing triggers reference full-text functions such as CONTAINS() or FREETEXT()? If so, you might be running into a known issue with concurrent snapshot processing, and the only workarounds are to use a post-snapshot script or changing the sync_method to non-concurrent as described in 3) above.

-Raymond

|||

Jarod, it would be great if you can post the definitions of the problematic triggers (please ofuscate any sensitive information). If you don't feel comfortable doing that, you can also send them directly to me (rmak@.microsoft.com).

-Raymond

|||

Hello Rymond,

I have just sent you the definition of triggers.

Jarod

|||

Hello All,

we have continued this discussion offline with Raymon Mak.

To resolve the thread, here is the conclusion :

I think I found out what the problem is, and it has something to do with
[the id column] being an
identity column marked not for replication. What happened was that when
the distribution agent created the trigger, the server thinks that the
'insert into select' statement in the trigger needed to supply explicit
values for [the id] column
(compilation check) since the current connection is a replication agent
connection while failing to account for the fact that the trigger itself
was marked not for replication and so it would never be executed from a
replication agent connection. Creating the trigger in the post-snapshot
script works because the connection for applying the post-snapshot
script is not marked as a replication connection.

Now, I would say that you have set up everything according to our
recommendations and it is simply unfortunate that the subtle
interactions between all these NFR settings are causing you trouble. As
such, I will drive the necessary changes to make your scenario work out
of the box but in the meantime, creating the trigger in the
post-snapshot script is the best possible workaround.

Raymon also said that this issue will be corrected in the Sql Server 2005 SP2.