Alternate Approach for Triggers

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts