請問SQL大神
我要從A主機的Atable
同步到
B主機的Btable
A.Btable 格式一模一樣
已經做好同步功能,手動執行很正常
後來在做TRIGGER
DBLINK的語法無法寫入CREATE TRIGGER內
語法如下
EXEC sp_addlinkedserver 'SerName','','SQLOLEDB','BDName'
GO
EXEC sp_addlinkedsrvlogin 'SerName','false',null,'SqlID','SqlPW'
GO
想問一下,上方兩段語法想寫入TRIGGER,可以寫在哪?
USE MASTER
GO
EXEC sp_addlinkedserver 'SerName','','SQLOLEDB','BDName'
GO
EXEC sp_addlinkedsrvlogin 'SerName','false',null,'SqlID','SqlPW'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [A]
GO
CREATE TRIGGER tr_Atable
ON [dbo].[Atable]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [B].[dbo].[Btable]
.
.
.
END
GO
EXEC sp_dropserver 'SerName','droplogins'
非常感謝~
CREATE TRIGGER tr_Name
ON [B主機].[DBName]..[Btable]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
.
.
.
END
請問B主機帳密要寫在哪?
抱歉
我目前的問題就是dblink不知道該寫在TRIGGER內的哪裡?
USE MASTER
GO
EXEC sp_addlinkedserver 'SerName','','SQLOLEDB','BDName'
GO
EXEC sp_addlinkedsrvlogin 'SerName','false',null,'SqlID','SqlPW'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [A]
GO
CREATE TRIGGER tr_Atable
ON [dbo].[Atable]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [B].[dbo].[Btable]
.
.
.
END
GO
EXEC sp_dropserver 'SerName','droplogins'
這樣寫TRIGGER建立好後,不會連線B主機
serverlink並不是在CREATE TRIGGER前建立的啦......而是要預先在系統上以SA或對等權限身份建立好,TRIGGER才能隨時動用