章立民研究室

問題：

章老師,您好,

想跟您請教一下TRIGGER 是否能跨server去作INSERT,
因為像store procedure可以在裡面寫跨DB insert語法是OK的,
但是我寫在Trigger,就是不行,
我想透過Trigger將某資料在INSERT到另一台server的table中
是否真的不行??

解答：

這樣的交易行為是所謂的「分散式交易」（Distributed Transaction），欲進行分散式交易必須在兩台伺服器上皆啟用 DTC（Distributed Transaction Coordinator） 服務以免發生如圖表 1 所示之錯誤訊息。
 

然後請在欲使用 Trigger 的那台伺服器上，建立「連結伺服器」（Linked Server），接著撰寫您原本的觸發程序即可。

假設欲在 ServerA 上，使用 Trigger 以便於 Insert 資料時，可以將資料 Insert 到另外一台 ServerB，請依照下列步驟進行：

 

1. 在 ServerA 上執行如下所列的指令碼來建立「連結伺服器」與觸發程序：
   
   -- 在 ServerA 上執行這個指令檔
   -- 建立一個名叫 ServerB 的連結伺服器
   -- 產品名稱是 SQL Server
   USE master;
   GO
   EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
   
   -- 建立本機執行個體登入與遠端伺服器安全性帳戶之間的對應
   EXEC sp_addlinkedsrvlogin
     'ServerB', 'FALSE', 'Alex', 'Alex', 'P@ssW0rd'
   GO
   
   -- 準備在本機「北風貿易」資料庫中，建立「範例資料表」
   USE 北風貿易;
   GO
   
   IF OBJECT_ID (N'範例資料表', N'U') IS NOT NULL
     DROP TABLE 範例資料表;
   GO
   
   -- 建立一個資料表
   CREATE TABLE 範例資料表
     (編號 nchar(5) PRIMARY KEY,姓名 nvarchar(12));
   GO
   
   -- 建立一個由新增作業引發執行的 FOR 觸發程序
   CREATE TRIGGER trig新增 ON 範例資料表
   FOR INSERT
   AS
   SET XACT_ABORT ON;
   SET NOCOUNT ON;
   PRINT '新增資料到 Linked Server';
   INSERT INTO ServerB.北風貿易.dbo.範例資料表 SELECT * FROM inserted;
   GO
2. 請在 ServerB 上執行下面的指令碼，以便建立一個資料表：
   
   USE 北風貿易;
   GO
   
   IF OBJECT_ID (N'範例資料表', N'U') IS NOT NULL
     DROP TABLE 範例資料表;
   GO
   
   CREATE TABLE 範例資料表
     (編號 nchar(5) PRIMARY KEY,姓名 nvarchar(12));
   GO
3. 在 ServerA 上執行如下的指令碼來新增資料，並透過 Trigger 將資料寫入被連結的伺服器（亦即 ServerB）中：
   
   INSERT 範例資料表 VALUES(N'A001',N'章立民研究室');
   INSERT 範例資料表 VALUES(N'A002',N'Alex');
   INSERT 範例資料表 VALUES(N'A003',N'Bill');
   GO
   
   -- 檢查資料是否新增成功
   SELECT * FROM 範例資料表;
   SELECT * FROM ServerB.北風貿易.dbo.範例資料表;

請注意：
如圖表 2～4 所示，於啟用 DTC（Distributed Transaction Coordinator） 服務之後，您可能需要開啟「系統管理工具」的「元件服務」進行相關設定。







章立民研究室