/*----------------------------
-- Author  :feixianxxx(poofly)
-- Date    :2010-04-29 14:45:30
-- Version:
--      Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
        Mar 29 2009 10:27:29 
        Copyright (c) 1988-2008 Microsoft Corporation
        Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )
-- Content:同步2个表的内容
----------------------------*/

--环境：
--源表
IF OBJECT_ID('dbo.Sources') IS NOT NULL
  DROP TABLE dbo.Sources;
GO
DECLARE @cmd AS NVARCHAR(4000), @i AS INT;
SET @cmd =
  N'CREATE TABLE dbo.Sources(keycol INT NOT NULL PRIMARY KEY';
SET @i = 1;
WHILE @i <= 100
BEGIN
  SET @cmd =
    @cmd + N',col' + RTRIM(@i) +
    N' INT NOT NULL DEFAULT 0';
  SET @i = @i + 1;
END
SET @cmd = @cmd + N');'
EXEC sp_executesql @cmd;
go
--目标表(表结构和源表相同，字段名字不同)
IF OBJECT_ID('dbo.Target') IS NOT NULL
  DROP TABLE dbo.Target;
GO
DECLARE @cmd AS NVARCHAR(4000), @i AS INT;
SET @cmd =
  N'CREATE TABLE dbo.Target(keycol INT NOT NULL PRIMARY KEY';
SET @i = 1;
WHILE @i <= 100
BEGIN
  SET @cmd =
    @cmd + N',col_t' + RTRIM(@i) +
    N' INT NOT NULL DEFAULT 0';
  SET @i = @i + 1;
END
SET @cmd = @cmd + N');'
EXEC sp_executesql @cmd;
go
--建立触发器
create  TRIGGER trTongbu_SourcesToTarget on dbo.Sources
for insert,update,delete
as
begin
    if exists(select * from deleted )
    begin
        if exists(select * from inserted)
        begin 
            DECLARE @i AS INT, @numcols AS INT;
            DECLARE @s varchar(8000)
            set @s=''
            SET @numcols =(SELECT COUNT(*) from syscolumns  where id=object_id('Sources'))
            SET @i = 1;
            --找出更新的列
            WHILE @i <= @numcols
            BEGIN
              IF (SUBSTRING(COLUMNS_UPDATED(),(@i - 1) / 8 + 1, 1) & POWER(2, (@i - 1) % 8) > 0)
                 begin 
                    set @s=@s+','+(select name from syscolumns where colid = @i  and id=object_id('Target'))+' = i.'+
                        (select name from syscolumns where colid = @i  and id=object_id('Sources'))
                 end 
             SET @i = @i + 1;
            END
            --先将INSERTED表的内容导入另外一个表，因为动态语法不识别INSERTED表
            select * into #test from inserted
            --只set 更新的列
            set @s='update Target set '+stuff(@s,1,1,'')+' from #test i where i.keycol=Target.keycol'
            exec (@s);    
        end
        else 
        begin
            delete dbo.Target
            from deleted d
            where dbo.Target.keycol=d.keycol
        end
    end
    else
    begin 
        insert dbo.Target
        select * from inserted    
    end    
end
go
--测试：
--插入：
    INSERT INTO dbo.Sources(keycol)  values(1)
    INSERT INTO dbo.Sources(keycol)  values(2)
    select * from dbo.target
--删除:
   delete dbo.Sources where keycol=1
   select * from dbo.target
--更新
  update dbo.Sources set col2=1,col9=10,col12=19,col87=99  where keycol=2
  select * from dbo.target