February 10, 2012 at 4:54 am
I've a below scenario where i'm suppose to do transactional replication for a master_table (say EMP). The master_table (say EMP) makes entry into its history table (say EMP_HS) for any INS/UPD/DEL through a trigger. EMP and EMP_HS has Identity(1,1).
Now when i replicate EMP and EMP_HS both at subscriber, it works fine but if i replicate only Master table it does not replicate. Is there a way out where we can just replicate the master table (EMP) and history table automatically gets updated on subscriber.
Tried few options like setting Identity properties 'NOT FOR REPLICATION' as 'Yes' for EMP_HS Identity(1,1) at publisher but still unable to replicate. Other option was taking system procedure of CDC and replicating that stored procedure but that is considered as last option by client.
Also If i'm not wrong, the Trigger property ''NOT FOR REPLICATION' is set to 'NO' by default so it has to fire on replication.
Server 1 Server 2
EMP Identity(1,1) --------> EMP_tbl Identity(1,1)
UPD/INS/DEL Trigger UPD/INS/DEL Trigger
EMP_HS Identity(1,1) EMP_HS Identity(1,1)
Thanks much,
PM
February 10, 2012 at 8:35 am
Can you provide the full DDL for the two tables?
February 10, 2012 at 8:52 am
Nicholas Cain (2/10/2012)
Can you provide the full DDL for the two tables?
and trigger
February 10, 2012 at 8:57 am
I'm not 100% I understand this.
NirvanASQL (2/10/2012)
Now when i replicate EMP and EMP_HS both at subscriber, it works fine but if i replicate only Master table it does not replicate. Is there a way out where we can just replicate the master table (EMP) and history table automatically gets updated on subscriber.
Is your problem that you are expecting a trigger to fire on the subscriber, I/U/D a log into EMP_HS?
If you've got a trigger on the subscriber table that duplicates the behaviour you shouldnt have a problem unless you have added NOT FOR REPLICATION. If this is completely left out it should fire.
February 14, 2012 at 10:55 am
What error do you get when you run the snapshot agent? Does it mention that it is expecting the history table to be also present on the subscriber?
If you are using the option 'Drop existing object and create a new one' in the article properties then change that to 'Truncate all data in the existing object'. This means that you will have to manage the objects on the subscriber yourself.
So create the master table on the subscriber without the triggers and with the 'NOT for Replication' property.
If you want the history table to be populated using a trigger on the subscriber then create the master table with the trigger.
I haven't worked with triggers a lot so can not say how the insert triggers will work with replication.
Blog
http://saveadba.blogspot.com/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply