SQL对某张表建立触发器，然后根据编码与名称，跨服务器动态建表与生成数据

SQL对某张表建立触发器，然后根据编码与名称，跨服务器动态建表（判断是否存在，存在即更新数据，否则先建表），然后生成数据。

注意：

1. 动态语句执行时最好指定服务器 exec LinkName.dbn.dbo.sp_executesql @sql;

    当建表时使用的exec('use linkdbn create table......) at LinkName;  紧随其后的CRUD直接用exec sp_executesql 'insert......'时会报错：

    消息 3970，级别 16，状态 2，第 1 行
    此操作与此事务上的另一个挂起操作冲突。此操作失败。

    exec LinkName.dbn.dbo.sp_executesql 'insert......‘则不会报错。

具体代码如下：

[code=sql]
drop TRIGGER syncFor_Update;
go

CREATE TRIGGER syncFor_Update
on 本地数据库表名
after update,insert
as
begin
 declare @mainTb nvarchar(100);
 declare @listTb nvarchar(100);
 declare @rowsCount nvarchar(100);
 declare @rowsCounts nvarchar(100);
 declare @sql nvarchar(2550);
 declare @sql1 nvarchar(2550);
 declare @sql2 nvarchar(2550);
 declare @name nvarchar(10);
 declare @code nvarchar(10);
 set @mainTb = 'mainTable'+'唯一标识';
 set @listTb = 'listTable'+'唯一标识';
 set @rowsCount = '-1';
 set @rowsCounts = '-1';
 
 ---- 表存在即更新，否则新增（表头+表体）
 set @sql='SELECT @rowsCounts = count(1) FROM dbo.SysObjects WHERE ID = object_id(@mainTb) AND OBJECTPROPERTY(ID, ''IsTable'') = 1';
 exec [LinkName].[DB].DBO.sp_executesql @sql,N'@rowsCounts int out,@mainTb nvarchar(100)',@rowsCounts out,@mainTb;
 select @rowsCounts;
 print (@rowsCounts);
 if @rowsCounts != 0
  begin
   set @sql='select @rowsCount = count(1) from '+@listTb+' where field0004 = @code;';
   exec [LinkName].[DB].DBO.sp_executesql @sql,N'@rowsCount int out,@code nvarchar(10)',@rowsCount out,@code;
   select @rowsCount;
   print (@rowsCount);
   if @rowsCount != 0
    begin
     -- 表体：更新
     set @sql = N'update '+@listTb+' set field0008 = @name where field0004 = @code;'
     exec [LinkName].[DB].DBO.sp_executesql @sql,N'@name nvarchar(100),@code nvarchar(100)',@name,@code;
    end
   else
    begin
     -- 表体：新增
     set @sql = N'insert into '+@listTb+' (ID,field0004,field0005,field0006,field0007,field0008,field0009)
        values (1,@code,3,4,5,@name,7);';
     exec [LinkName].[DB].DBO.sp_executesql @sql,
        N'@name nvarchar(100),@code nvarchar(100)',
        @name,@code;
    end
  end
 else
  BEGIN
   -- 建表-- 建表
   exec ('use 远程数据库名 CREATE TABLE '+@mainTB+'(
      ID numeric(19, 0) NOT NULL,
      field0001 nvarchar(255) NULL,
      field0002 nvarchar(255) NULL,
      field0003 nvarchar(255) NULL,
     PRIMARY KEY CLUSTERED
     (
      ID ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
     ) ON [PRIMARY];') at LinkName;
   
   exec ('use 远程数据库名 CREATE TABLE '+@listTB+'(
      ID numeric(19, 0) NOT NULL,
      field0004 nvarchar(255) NULL,
      field0005 nvarchar(255) NULL,
      field0006 nvarchar(255) NULL,
      field0007 nvarchar(255) NULL,
      field0008 nvarchar(255) NULL,
      field0009 nvarchar(255) NULL,
     PRIMARY KEY CLUSTERED
     (
      ID ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
     ) ON [PRIMARY];') at LinkName;
   -- 表头：新增
   set @sql1 = N'insert into '+@mainTb+' (id,field0001,field0002,field0003)
      select 1,2,3
       where not exists (select 1
            from '+@mainTb+');';
   exec [LinkName].[DB].DBO.sp_executesql @sql1;
   
   -- 表体：新增
   set @sql = N'insert into '+@listTb+' (ID,field0004,field0005,field0006,field0007,field0008,field0009)
      values (1,@code,3,4,5,@name,7);';
   exec [LinkName].[DB].DBO.sp_executesql @sql,
      N'@name nvarchar(100),@code nvarchar(100)',
      @name,@code;
  END
end

[/code]