Triggers and replication

Peter Young 1 Reputation point
2022-05-10T17:19:10.977+00:00

I have a table that is replicating . It is set for transactional replication. The replication works . Mostly. Here is where it does not.

I have a trigger on a table that is not one of the replication set. When that trigger fires, it updates a field in the replicated table.

If I update a field on the replicated table using a query, the field in the publisher is updated and the field in the subscriber is updated.

If the same field on the replicated table is updated using the trigger, the publisher is updated, but the subscriber is not.

The subscriber is never updated, even when data changes in other fields and is passed through correctly during subsequent replication actions.

It seems such a fundamental requirement that I am sure I have some setting wrong or some small thing. However searching only keeps leading back to the opposite issue, about replication not firing triggers and setting NOT FOR REPLICATION , which should have no bearing on this issue.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,126 Reputation points
    2022-05-10T21:43:36.463+00:00

    I tested it in your scenario in which the table A with an UPDATE trigger is not in the publication of the transactional replication. The trigger will updates one column (varchar) of another table B which is in the publication. When I update the table A, I can see the change in the table B.

    Is it an AFTER or INSTEAD OF trigger?

    0 comments
  2. LiHong-MSFT 10,046 Reputation points
    2022-05-11T03:11:44.277+00:00

    Hi @Peter Young
    Have you tried set @loopback_detection = 'false' when runing sp_addsubscription ?
    Hope this thread help you: Records not replicated when inserted by custom replication stored procedure

    Best regards,
    LiHong

    0 comments
  3. Tom Phillips 17,716 Reputation points
    2022-05-11T14:29:03.723+00:00

    What you describe should not be possible.

    Transactional replication replays the commands on the subscriber for a transaction. This would include the commands for the trigger. The transactions are applied to the subscriber in the order they are applied on the publisher. Transactions either succeed or on error stop replication from continuing until resolved.

    More likely, your subscriber is changing it back to the old value somehow after the data is changed. Either with a trigger or something else.

    0 comments