SQL 2005 : AFTER INSERT - Trigger

[Stout]

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

[Erland Sommarskog]

Re: SQL 2005 : AFTER INSERT - Trigger

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.r emotedbl
SET col = i.someval
FROM SERVER.db.dbo.r emotedbl 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@sommarsk og.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