| Author |
Topic |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
 Posted - 2014-06-04 : 09:18:54
|
| Hi All,I've two databases on the SAME server. I need to sync the data between these two databases. Suppose Database One is DB_OLD and another is DB_NewCurrently, am having One-Way Triggers and Two-Way Triggers based on the tables requirement. 1) One-Way triggers are for data sync from OLD to NEW Database2) Two-Way Triggers are for handling both OLD-to-NEW and NEW-to-OLD databasesIn the Two-Way Triggers, we were using FlagTable to avoid the Deadlocks... But i would like to know any alternative approach to handle Two-Way Triggers for Data Sync ?--Chandu |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-06-04 : 10:52:23
|
| I assume you use TRIGGER_NESTLEVEL() to keep from recursion loop?djj |
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-06-04 : 16:06:30
|
| SQL Replication |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2014-06-05 : 07:28:36
|
quote:
Originally posted by djj55
I assume you use TRIGGER_NESTLEVEL() to keep from recursion loop?djj
Can you provide some example on this?--Chandu |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2014-06-05 : 07:29:47
|
quote:
Originally posted by ScottPletcher
SQL Replication
In our case, we do not want to use Replication...--Chandu |
|
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-06-05 : 07:46:42
|
| TRIGGER_NESTLEVEL() is used to determine what level of nesting. Say you have a trigger on TableOld and a trigger on TableNew to keep the column FName in sync. If you update TableOld.FName the trigger fires updating TableNew.FName this fires the trigger (level 2) that updates TableOld.FName.This could go on and on (there is a default limit), however, by using IF TRIGGER_NESTLEVEL() > 2 RETURN; you can keep from updating TableOld.FName with the same value.Hope you can understand my rambling explanation as I know what it does but telling someone else is not easy.djj |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-06-05 : 10:30:56
|
| If you can afford a small time delay, you can use Change Tracking.If you really want to use triggers, you'll need to review the code very exactly to make sure it is as efficient as possible. |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2014-06-27 : 01:44:06
|
| Hi Every One,Would it be possible to replace DML Triggers with Updatable Views in SQL Server? Main intention is to sync live data (INSERT/DELETE/UPDATE) across two databases in the same Server Any DML operation of a OLD DB table should reflect in the New DB table and viceversa. We have only 2-5 tables require Two-Way Data Sync.Please help. this is urgent requirement...--Chandu |
|
|
|
adsingh82
Starting Member
20 Posts |
Posted - 2014-06-27 : 11:33:47
|
| Sql mirroring would also help if you do not want to go for replication... but this is bit expensiveRegards,Alwyn.M |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2014-06-30 : 08:40:04
|
| Can we replace two-way-sync triggers with Updatable Views?--Chandu |
|
|
|
|
Data Sync between two Databases on the same server