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.
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.
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.
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
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.
You only use NOT FOR REPLICATION if you don't want replication to fire the trigger on the subscriber table. Definitely test!
13 People are following this question.
