Replication - can I write triggers on the subscriber server?

Question

Question by myid · Jan 29, 2013 at 08:58 PM · replication triggers subscriber

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.

3

10 |1200 characters needed characters left characters exceeded

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

Answer 1 · 2013-01-29T21:03:44Z

Best Answer

Answer by KenJ · Jan 29, 2013 at 09:03 PM

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.

3 · Share

10 |1200 characters needed characters left characters exceeded

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

Answer 2 · 2013-01-30T10:12:53Z

Answer by Cyborg · Jan 30, 2013 at 10:12 AM

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.

1 Show 4 · Share

10 |1200 characters needed characters left characters exceeded

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

KenJ · Jan 30, 2013 at 02:48 PM 1

Share

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

Cyborg · Jan 30, 2013 at 03:38 PM 0

Share

+1 Yes that's right, I missed that archive requirement. Thanks!

myid · Jan 30, 2013 at 05:33 PM 0

Share

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.

KenJ · Jan 30, 2013 at 05:44 PM 0

Share

You only use NOT FOR REPLICATION if you don't want replication to fire the trigger on the subscriber table. Definitely test!

Replication - can I write triggers on the subscriber server?

2 Replies

Your answer

Follow this Question

Related Questions