question

PeterYoung-7011 avatar image
0 Votes"
PeterYoung-7011 asked TomPhillips-1744 answered

Triggers and replication

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.

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered

Hi @PeterYoung-7011
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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.