sqlserver数据库实现自动同步表字段表数据更新

sqlserver

触发器

我们可以先创建一个案例表。 --创建A表create table table_A(name varchar(20)) insert into table_Aselect 'liu'unionselect 'zhang' select * from table_A

通过复制表的方式，创建一个B表，输入脚本并执行。 select top 0 * into table_B from table_Aselect * from table_B

同步实现的原理通过触发器进行实现，如果A表进行更新了，那么我们同时在B表进行表字段更新，同时进行B表的数据更新。使用下一步的脚本，建立一个A表的触发器，注意表名，进行修改。

--创建一个A表的触发器 create trigger tr_table_a on table_afor insert,updateasbegin       ---同步table_a表的列            declare @newcolname varchar(2000),@newtype varchar(2000),@newlength varchar(2000),              @newprec varchar(2000),@newscale varchar(2000)      declare @sql varchar(5000)            set @sql = ''            declare cur_col cursor for   select B.name,C.name,B.length,B.prec,B.scale     FROM  sysobjects  a  left join syscolumns b on a.id = b.id     left join systypes c on c.[xusertype] = b.[xusertype] where a.name = 'table_a' and b.name not in (                            select b.name as typename                            FROM  sysobjects  a  left join syscolumns b on a.id = b.id             left join systypes c on c.[xusertype] = b.[xusertype]                            where a.name = 'table_b'      --注意修改表名                             )      open cur_col        fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale                 while @@FETCH_STATUS = 0   begin   if @newtype = 'varchar' or @newtype = 'char'  begin    set @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newlength+')'   --注意修改表名 end else begin    set  @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newprec+','+@newscale+')'   --注意修改表名 end          exec(@sql)   fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale      end   close cur_col   deallocate cur_col         --- 同步表数据      declare @name varchar(200) , @colid varchar(200)      declare @str_colname varchar(4000)      declare @sql_data varchar(5000)      set @str_colname = ''    select * into #tmp_inserted from inserted      declare cur cursor for  select b.name,colid  FROM  sysobjects  a  inner join syscolumns b  on a.id = b.id  where a.name =  'table_a'      --注意修改表名    open cur    fetch next from cur into @name,@colid      while @@FETCH_STATUS = 0  begin     set  @str_colname = @str_colname  + @name +', '      fetch next from cur into @name,@colid     end  close cur  deallocate cur    set @str_colname = left(@str_colname,len(@str_colname)-1)  print @str_colname        set @sql_data = '  insert into table_B ('+@str_colname+')     select  '+ @str_colname + ' from temp..#tmp_inserted'          exec(@sql_data)      end

建立好触发器，我们来测试给A表增加列，同时也给A表增加数据。 看到B表中，是不是自动完成列的增加，数据的增加 --测试给A表增加字段addressalter table table_aadd address varchar(80) alter table table_aadd personnum numeric(12,2)

给A表写入数据 ---测试给A表增加数据insert into table_A (name,address,personnum)select 'tbc','yi huan 101',40000200

查看两个表的数据，验证触发器已经生效。 可以发现列已经增加完毕，数据也同步增加了。---检查两个表数据select * from Table_A select * from table_B

以下是测试完整的代码：  --创建A表create table table_A(name varchar(20)) insert into table_Aselect 'liu'unionselect 'zhang' select * from table_A  ---复制的方式创建B表select top 0 * into table_B from table_A select * from table_B  --创建一个A表的触发器create trigger tr_table_a on table_afor insert,updateasbegin       ---同步table_a表的列            declare @newcolname varchar(2000),@newtype varchar(2000),@newlength varchar(2000),              @newprec varchar(2000),@newscale varchar(2000)      declare @sql varchar(5000)            set @sql = ''            declare cur_col cursor for   select B.name,C.name,B.length,B.prec,B.scale     FROM  sysobjects  a  left join syscolumns b on a.id = b.id     left join systypes c on c.[xusertype] = b.[xusertype] where a.name = 'table_a' and b.name not in (                            select b.name as typename                            FROM  sysobjects  a  left join syscolumns b on a.id = b.id             left join systypes c on c.[xusertype] = b.[xusertype]                            where a.name = 'table_b'      --注意修改表名                             )      open cur_col        fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale                 while @@FETCH_STATUS = 0   begin   if @newtype = 'varchar' or @newtype = 'char'  begin    set @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newlength+')'   --注意修改表名 end else begin    set  @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newprec+','+@newscale+')'   --注意修改表名 end          exec(@sql)   fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale      end   close cur_col   deallocate cur_col         --- 同步表数据      declare @name varchar(200) , @colid varchar(200)      declare @str_colname varchar(4000)      declare @sql_data varchar(5000)      set @str_colname = ''    select * into #tmp_inserted from inserted      declare cur cursor for  select b.name,colid  FROM  sysobjects  a  inner join syscolumns b  on a.id = b.id  where a.name =  'table_a'      --注意修改表名    open cur    fetch next from cur into @name,@colid      while @@FETCH_STATUS = 0  begin     set  @str_colname = @str_colname  + @name +', '      fetch next from cur into @name,@colid     end  close cur  deallocate cur    set @str_colname = left(@str_colname,len(@str_colname)-1)  print @str_colname        set @sql_data = '  insert into table_B ('+@str_colname+')     select  '+ @str_colname + ' from temp..#tmp_inserted'          exec(@sql_data)      end    ---测试语句 --测试给A表增加字段addressalter table table_aadd address varchar(80) alter table table_aadd personnum numeric(12,2)  ---测试给A表增加数据insert into table_A (name,address,personnum)select 'tbc','yi huan 101',40000200     ---检查两个表数据select * from Table_A select * from table_B