Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sanjayadsure
Starting Member
3 Posts |
 Posted - 2009-04-22 : 10:47:32
|
| Hi All, I have two different server machines & both are having SQL Server 2005 installed on them. To make my question easier to understand let me give them some name. Suppose Server names are S1 & S2. I have one table on S1 lets say T1 & table on S2 is T2. Table T1 & T2 are having some columns common. Now what I want is whenever some certain column's value of table T1 will be updated; I want to update the same values in T2 in respective columns. So is it ok to use trigger in this scenario? Because what I have read is "The scope of a trigger should ideally be limited to its table, but should never extend beyond its database. This is one of the most common ways that triggers are misused and is one of the reasons for their bad reputation." I would appreciate if anybody please help me in this.Thanks,Sanjay. Sanjay |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-22 : 10:53:08
|
| >> So is it ok to use trigger in this scenario? Because what I have read is No - it will hold a transaction open for the duration of the trigger and if the remote update fails it will error the local one.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sanjayadsure
Starting Member
3 Posts |
Posted - 2009-04-22 : 11:21:52
|
| Hi nr, Thanks for your reply. So according to you, what should I use here? Please suggest.Thanks,Sanjay.Sanjay |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-04-22 : 11:49:44
|
| Well since I'm pretty sure you are quoting me, Sanjay, I feel obligated to respond.SQL Server has some built-in features for handling this, such as replication.You could also use a trigger on your table to populate staging tables, but then use a scheduled job to process the data in the staging table and send it to the opposite database. This is more robust, as if for some reason the communication between the two servers fails then each database can continue operating independently, and the staging tables can be processed when communication is resumed.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
|
|
|
|
Use of Update Trigger in SQL Server 2005.