在server1上做连接服务器:
使用联结服务器:
EXEC sp_addlinkedserver
'别名',
'',
'MSDASQL',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=server2;UID=sa;PWD=;'
GO再对server1上的表tbl1做触发器:
CREATE TRIGGER 名1 ON tbl1
FOR INSERT
AS
insert 别名.db2.dbo.tbl2 select * from inserted
go
CREATE TRIGGER 名1 ON tbl1
FOR update
AS
update 别名.db2.dbo.tbl2 set name2=BB.name2 from inserted BB where BB.no1=别名.db2.dbo.tbl2.no2
go
CREATE TRIGGER 名1 ON tbl1
FOR deleted
AS
delete 别名.db2.dbo.tbl2 from deleted BB where 别名.db2.dbo.tbl2.no2=BB.no1
go
使用联结服务器:
EXEC sp_addlinkedserver
'别名',
'',
'MSDASQL',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=server2;UID=sa;PWD=;'
GO再对server1上的表tbl1做触发器:
CREATE TRIGGER 名1 ON tbl1
FOR INSERT
AS
insert 别名.db2.dbo.tbl2 select * from inserted
go
CREATE TRIGGER 名1 ON tbl1
FOR update
AS
update 别名.db2.dbo.tbl2 set name2=BB.name2 from inserted BB where BB.no1=别名.db2.dbo.tbl2.no2
go
CREATE TRIGGER 名1 ON tbl1
FOR deleted
AS
delete 别名.db2.dbo.tbl2 from deleted BB where 别名.db2.dbo.tbl2.no2=BB.no1
go
我照你的办法试了,建立链接后检索数据没问题,但是更新的时候提示ODBC的错误,还要启动什么嵌套事务?这是怎么回事?