I have set up a transactional replication from multiple Databases (6 Different Server) to 1 consolidated database. The schema is identical. Segregation is done by the column SiteName . My problem is :
Every time when a table is altered , the MSIns,MSUpd and MSDel stored procedures at subscriber get altered and MSUpd and MSDel SPs where condition get changed based on only ID (PK) , and it overwrites the SPs where my condition was based on ID and Sitename. Thus , the destination table data gets updated for all the rows based on First database ID.
Example : If we alter table employee and add/alter column with update command for all 6 sites , when 1st site is applying the changes it's taking where condition based on ID only and if ID is 2 all the records will updated in destination DB with ID 2.
Is there any way to disable the replication as soon as table get altered so that i will have time to change the stored procedure at subscriber to add Sitename in where condition .