Trigger in one database updates another database

Question

Trigger in one database updates another database

igngua

SSCrazy

Points: 2510
More actions
October 22, 2009 at 1:32 pm

#205903

Hi!
i have two separated servers with sql 2005.
I want to make a trigger in server A that updates/insert/deletes on server B.
How do i do this ?

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

Try to do it with linked server, simple link server B to the A and try to execute trigger referring the link server

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jan Van der Eecken SSCrazy Eights Points: 9013 More actions · Answer 2

What are you trying to copy from server A to server B? Just a few columns of a few rows whenever table X gets updated on server A? Or entire tables/databases or their changes on a regular scheduled basis? If it is the former, Dugi's suggestion may be OK (although I hate triggers more than cursors, hehe, not applicable here), but otherwise I would be looking at some form of replication if I were you.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

I really must caution you on this method. If server b is not available, or that database on server b is not available, or there is a network problem the trigger will fail and the change will rollback.

If you need to keep server B updated choose another method like log shipping or transactional replication instead. The method you are looking at it rife with downsides and potential issues..

CEWII

George M Parker SSCertifiable Points: 5412 More actions · Answer 4

I have to jump on the bandwagon for this one as well. In the past, I've seen situations where triggers writing to linked servers caused severe production problems to the point that the application eventually became unusable. We re-architected the solution to do more of a dump and sweep mechanism where the trigger wrote the data locally, and some other process (a scheduled job in our situation) then picked it up and processed it downstream.

Piotr.Rodak SSCrazy Eights Points: 9296 More actions · Answer 5

Agreed,

Triggers updating data on linked servers promote transactions to distributed and this opens a whole lotta new possibilities of failure, mainly related to availability of the servers, network stability, firewals, and MSDTC. An asynchronous process is a much safer option. Not sure, but perhaps you could also set up replication on this table.

Regards

Piotr

...and your only reply is slàinte mhath

igngua SSCrazy Points: 2510 More actions · Answer 6

Thanks a lot. So i guess i have to consider a lot of facts first.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 7

I'm going to pile on here as well. A trigger should NOT be used to update data on another server or really do any action outside the database (cross database on the same server is probably okay). In your case you want to look into Service Broker or roll your own queuing process.

I wrote an introduction to triggers article[/url] that I would recommend you read as it discusses this type of issue.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

igngua SSCrazy Points: 2510 More actions · Answer 8

transactional replication it for large amount of data???

I need to move a really small amount of data from server A to B. that´s what i thought in a trigger.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 9

Elliott Whitlow

SSC Guru

Points: 102296

October 23, 2009 at 8:55 am

#1070093

Then service broker sounds pretty good..

CEWII

igngua SSCrazy Points: 2510 More actions · Answer 10

i will stay with the trigger idea and see how it works.

My question now is how should the transact look to insert in one db from another.

insert into databaseengine.db.schema.table.??

Jack Corbett SSC Guru Points: 184388 More actions · Answer 11

I really highly recommend AGAINST doing this in a trigger. It will not scale and it is rife with problems. Use Service Broker or use a trigger to insert the data into a local table, then have a job or a windows service to process that data and insert it into the linked server.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

igngua SSCrazy Points: 2510 More actions · Answer 12

Ok. i´ll evuate the idea of using a aditional local table and a job.

thanks for the advice.

igngua SSCrazy Points: 2510 More actions · Answer 13

so i know how to do what i´ve to do.

But i really want to know how do i connect to diferent DB engines from diferent servers.

thanks.

igngua SSCrazy Points: 2510 More actions · Answer 14

igngua (10/23/2009)
so i know how to do what i´ve to do.
But i really want to know how do i connect to diferent DB engines from diferent servers.
thanks.

so...how can i do it ??

¿ just registering the engine in both servers ?