Triggers
-
Hi Guys,
I have a table on my one of my servers. I want to create a trigger, everytime a update or an insert is done on the table then a table on another server will also be updated. The tables on these servers are identical and I want to keep them both up to date without me having to update the other table too. Can anyone advise me what to do in this situation.
Regards
IC
-
You may wish to look at SQL Server Replication rather than triggers. The only reason is that if the link between the servers disappears briefly then you trigger will fail causing your transactions to roll back. Additionally, the extra time required to begin a distributed transaction to coordinate the updates in real time on two servers could cause bottlenecks.
Having said all of that, if you wish to experiment with the trigger approach then you need to create what's called a "linked server". Then you can refer to the table on the partner server using notation such as
[OtherServer].[DatabaseName].[owner-typicallyDBO].[tableName]
Thus your trigger could look like
insert into [OtherServer].[DatabaseName].dbo.[TableName]
select * from inserted
And you would have similar triggers for update and deleted - or roll it all into one trigger
The other consideration is that if you have triggers on the other server's table then it will also fire!
Replication handles all of that for you (mostly). There are three different types, depending on how you want the servers to interact. You could also investigate Database Mirroring if you are using an expensive version of SQL 2005.
-
indeed, a trigger that reaches across to another server will KILL your perf, as well as cause many other problems already mentioned.
---------------------------------------
elsasoft.org -
Pig Pile!
Seriously though, everyone got it right. Replication has the type of behavior you're looking for. It won't seriously impact your performance (configured correctly) and it's a lot more fault tolerant than a trigger will be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
if you want your tables to update from both sides use merge repl or else use trnasactional replication
My Blog:
http://dineshasanka.spaces.live.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply