Alternate Approach for Triggers

Posted by Bandi under Sql Server on 6/30/2014 | Points: 10 | Views : 398 | Status : [Member] [MVP] | Replies : 2
Hi all,

We have number of triggers for synchronizing data between two databases in the same server. Due to concurrent users along with Two-way Sync, we faced deadlocks and missing INSERTS.

How can we overcome this issue? I meant to say any alternate approach for synchronizing tables data across databases in the same server using SQL Server..

I know that we have Replication mechanism, DB Mirroring and SSIS packages to try out this... But i would like to know what is better/best approach?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif



Responses

Posted by: Bandi on: 6/30/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
am trying to resolve the deadlock issues by using Views (updatable)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/1/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Decided to use Updatable Views instead of Triggers for Two-way data sync.

Suppose we have old and new applications for real-time and having two separate databases for two applications. There is a need to have real-time synced data for both applications. ( OLD-to-NEW and NEW-to_OLD databases). For this type of requirement, we can have a view by referencing new table in the OLD database and no more use of old table.

Suppose OLD table is vNewTabView and we are creating view with same table name as follows:
CREATE VIEW OldDB.dbo.vNewTabView

AS
SELECT * FROM NewDB.dbo.NewTable


So, any DML operation performed on the old table will be affected to new table in new database... Hence, there is no need of Two-Way sync....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response