question

myid avatar image
myid asked

Replication - can I write triggers on the subscriber server?

We have a replication server. Server A is the Publisher and Server B is the Subscriber. We would like to know if there is a way for us to write trigger events on the subscriber side (Server B) in order to catch changes on some of its tables and create historical tables on the same server B. Below is a small example in which EmployeeTable is replicated on the Server B. Triggers on the EmployeeTable on server B are triggered each time we have an insert/delete/update events from server A, and update a historical table on the server B: Server A (EmployeeTable) >>> Replication >>> Server B (EmployeeTable) >>>triggervents >>> Server B (HistoricalEmployeeTable) We are currently using the Transactional Replication. Thanks in advance for your help.
replicationtriggerssubscriber
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
KenJ avatar image
KenJ answered
Yes, you can. Keep the script you use to create the trigger because you'll have to re-apply it when you re-initialize the subscription.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
Adding to @Ken Johnson, you should create the trigger with NOT FOR REPLICATION option if you don't want the trigger to fire when replication agents performs any DML.
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
NOT FOR REPLICATION prevents the Server B trigger from firing when the replication agent replicates the data. this means the trigger wouldn't be able to populate the HistoricalEmployeeTable as the OP outlined
1 Like 1 ·
Cyborg avatar image Cyborg commented ·
+1 Yes that's right, I missed that archive requirement. Thanks!
0 Likes 0 ·
myid avatar image myid commented ·
Thank you very much guys. I am assuming you put the NOT FOR REPLICATION on the triger of subscriber side. I will have this test.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
You only use NOT FOR REPLICATION if you don't want replication to fire the trigger on the subscriber table. Definitely test!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.