【利用触发器动态同步2表的数据】解决办法

【利用触发器动态同步2表的数据】

SQL code

/*------------------------------ 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个表的内容----------------------------*/

说明:使2个表同步的方法有很多，其中有一种利用触发器进行同步，
在SQL Server2008里可以使用Merge进行同步(http://blog.csdn.net/feixianxxx/archive/2010/02/07/5296519.aspx)
这里介绍一种利用触发器同步的方法,特别适用于表的字段非常多缺不想列出全部列，只列出更新的列的情况。

PS：不知道UPDATE表的时候，set 后面的列越多是不是越耗资源，如果消耗几乎没变 那此脚本意义就不大了。希望大大们帮忙解惑

SQL code

--环境：--源表IF OBJECT_ID('dbo.Sources') IS NOT NULL  DROP TABLE dbo.Sources;GODECLARE @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 <= 100BEGIN  SET @cmd =    @cmd + N',col' + RTRIM(@i) +    N' INT NOT NULL DEFAULT 0';  SET @i = @i + 1;ENDSET @cmd = @cmd + N');'EXEC sp_executesql @cmd;go--目标表(表结构和源表相同，字段名字不同)IF OBJECT_ID('dbo.Target') IS NOT NULL  DROP TABLE dbo.Target;GODECLARE @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 <= 100BEGIN  SET @cmd =    @cmd + N',col_t' + RTRIM(@i) +    N' INT NOT NULL DEFAULT 0';  SET @i = @i + 1;ENDSET @cmd = @cmd + N');'EXEC sp_executesql @cmd;go--建立触发器create  TRIGGER trTongbu_SourcesToTarget on dbo.Sourcesfor insert,update,deleteasbegin    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    endgo--测试：--插入：    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  

希望指正错误，并且讨论下 2表同步最好的办法

[解决办法]
sf.
[解决办法]
sf....
[解决办法]
学习...
[解决办法]
学习,接分.

[解决办法]
..........UP
[解决办法]
BD.

查看更多 下一篇