|
|
Posted by Erland Sommarskog on 06/07/07 21:25
Stout (stout27@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|