Need help in Transactional Replication

• praveen2288

  SSC Journeyman

  Points: 89

  More actions

  August 27, 2010 at 5:06 am

  #225322

  Please if anyone of you can help me in the below issue

  There are two publishing databases (DB1,DB2) and a subscriber db (ReplicatedDB) which acts as central one getting the data from publishers.

  The table structure is common in all the three (columns: AppID,NoOfCalls)

  These are the steps I followed

  ------------------------------------------------------------------------------

  1. Created a publication for DB1 (I created a snapshot to set up the initial sync)

  2. Created a subscribtion for DB1 to point to ReplicatedDB.

  3. Created another publication for DB2 (I did not create a snap shot for this as i have already done that)

  4. Created another subscription without initializing for DB2 to point to ReplicatedDB

  -------------------------------------------------------------------------------

  Everything is working fine... and ReplicatedDB gets the data everytime the data is inserted in db1 and db2....

  The issue now is .. if AppId is same in db1 and db2 then the NoOfCalls should be added and updated in replicateddb which is not happening...

  I tried a trigger for Insertion in ReplicatedDb which is not working.. find the trigger below...

  DECLARE @AppId VarChar(10)

  DECLARE @NoOfCalls int

  IF EXISTS(SELECT AppId from app_summary as a WHERE EXISTS (SELECT AppId FROM INSERTED AS b WHERE b.AppId=a.AppId))

  begin

  update app_summary set NoOfCalls=NoOfCalls+inserted.NoOfCalls where AppId=(select AppId from inserted)

  commit tran

  end

  else

  commit tran

  Will the way of using triggers work in this situation?? Or do we have any other way to implement this...

  This update statement is wrong and any help will be greatly appreciated...