469,631 Members | 1,152 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

SQL 2005 : AFTER INSERT - Trigger

Is it possible to create a trigger in one database, that after an
insert, will update a database on a different server?

If so, how would I do this?

Thanks.

Bill

Jun 7 '07 #1
1 6380
Stout (st*****@gmail.com) writes:
Is it possible to create a trigger in one database, that after an
insert, will update a database on a different server?
Yes, this is possible. Whether it is really a good idea is another
matter. Triggers execute in the context of a transaction, and an access
to a linked server may not be that fast. Thus, this can have impact on
concurrency, which depending on the application may matter a lot, or not
at all.
If so, how would I do this?
The trigger code as such, is not difficult:

CREATE TRIGGER remote_tri FOR INSERT ON tbl AS
UPDATE SERVER.db.dbo.remotedbl
SET col = i.someval
FROM SERVER.db.dbo.remotedbl r
JOIN inserted i ON r.keycol = i.keycol

The inserted table is a virtual table that holds the newly inserted rows.
(Note plural: a trigger fires once per statement.)

What may be a hassle, is to get everything to work with the distributed
transaction. Keep in mind here that if the update on the remote server
fails, the INSERT must be rolled back. And if local server goes belly-up
before the INSERT is committed, then the operation on the remote server
must also be rolled back. MS DTC handles these low-level parts, but
still some stars have to be aligned. DTC must be running on both servers.
And if the machines run different versions of the operating system
there can be more problems.

One thing you need to ask yourself is how tightly connected the operation
on the remote server has to be on the local server. Maybe something
more loosely coupled like replication is to prefer? Or if you are on
SQL 2005, could Service Broker be a better solution.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 7 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Zeljko Bilandzija | last post: by
2 posts views Thread by gimme_this_gimme_that | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.