Adding a Trigger to a replicated Table

Question

Adding a Trigger to a replicated Table

checkai

Hall of Fame

Points: 3041
More actions
March 18, 2008 at 1:37 pm

#95836

We have Transactional Replication pushing data to our Reporting Database once a day.
I am thinking of putting a trigger on one of the tables that gets hit by the replication. WHat the trigger would do would be to insert the ID of the table into another table. Basically, showing me all the IDs that were affected by the replication that evening.
If my table is getting 50,000 records inserted an evening, is that trigger a bad idea?
Thanks for any input on the topic!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29387 More actions · Answer 1

This sounds like a reasonable use of a trigger to me. 50,000 records should be OK (subject to all the usually caveats about other workload on the machine etc, of course)

checkai Hall of Fame Points: 3041 More actions · Answer 2

This trigger would be on our reporting server, so at that time of day (3AM) there shouldn't be much else going on.

Would this slowdown replication at all?

happycat59 One Orange Chip Points: 29387 More actions · Answer 3

The short answer is yes - any extra work (e.g. whatever the trigger does) will slow replication down. If you write efficient code for your trigger, the impact will not be significant.

checkai Hall of Fame Points: 3041 More actions · Answer 4

In essence I just want the updated IDs into a holding Table.

Insert into ssrsWIPTemp (wipID, insertTime)

Select wipID, getDate()

From Inserted

Could you see another way to get any ID that was updated into a table?

Thanks!