-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER triggertest
ON employee_infor
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements. SET NOCOUNT ON;
exec sp_addlinkedserver 'db1','','SQLOLEDB','192.168.65.10' update b set ID=i.ID,NAME=i.NAME,POST=i.POST,DEPARTMENT=i.DEPARTMENT,WAGE=i.WAGE,HIRE_DATE=i.HIRE_DATE
from db1.cxcv_db1.dbo.员工信息表 b,
deleted d,inserted i
where b.ID=i.ID -- Insert statements for trigger here
exec sp_dropserver 'db1' /*-----删除远程服务器------*/ END
GO出现如下错误:
消息 7202,级别 11,状态 2,过程 triggertest,第 16 行
在 sysservers 中找不到服务器 'db1'。请执行 sp_addlinkedserver 将该服务器添加到 sysservers。
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER triggertest
ON employee_infor
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements. SET NOCOUNT ON;
exec sp_addlinkedserver 'db1','','SQLOLEDB','192.168.65.10' update b set ID=i.ID,NAME=i.NAME,POST=i.POST,DEPARTMENT=i.DEPARTMENT,WAGE=i.WAGE,HIRE_DATE=i.HIRE_DATE
from db1.cxcv_db1.dbo.员工信息表 b,
deleted d,inserted i
where b.ID=i.ID -- Insert statements for trigger here
exec sp_dropserver 'db1' /*-----删除远程服务器------*/ END
GO出现如下错误:
消息 7202,级别 11,状态 2,过程 triggertest,第 16 行
在 sysservers 中找不到服务器 'db1'。请执行 sp_addlinkedserver 将该服务器添加到 sysservers。
然后才能drop table tablename否则提示找不到...链接服务器需要先sp_addlinkedserver ,才能 sp_dropserver
exec sp_addlinkedserver 'db1','','SQLOLEDB','192.168.65.10'
go select * from master..sysservers where srvname='db1' update b
set ID=i.ID,NAME=i.NAME,POST=i.POST,DEPARTMENT=i.DEPARTMENT,WAGE=i.WAGE,HIRE_DATE=i.HIRE_DATE
from db1.cxcv_db1.dbo.员工信息表 b,
deleted d,inserted i
where b.ID=i.ID -- Insert statements for trigger here
exec sp_dropserver 'db1'
2.sp_dropserver不能在用户定义的事务内执行
链接服务器"db1"的 OLE DB 访问接口 "SQLNCLI" 返回了消息 "建立到服务器的连接时发生错误。连接到 SQL Server 2005 时,默认设置 SQL Server 不允许远程连接这个事实可能会导致失败。"。
消息 1327,级别 16,状态 1,第 0 行
命名管道提供程序: 无法打开与 SQL Server 的连接 [1327].