在特殊的场景下,比如数据跟踪,会希望在数据库中A表的数据变化了,B表的数据也可以同步进行更新。
本文的方式完成以下要求
1、A表字段变化了,B表也需要同步变化
2、A表的数据新增了,B表也需要同步进行增加
本文做方法的演示,可以举一反三。
方法/步骤
我们可以先创建一个案例表。
--创建A表
create table table_A
(name varchar(20))
insert into table_A
select 'liu'
union
select 'zhang'
select * from table_A
- 该信息非法爬取自百度经验
通过复制表的方式,创建一个B表,输入脚本并执行。
select top 0 * into table_B from table_A
select * from table_B
同步实现的原理通过触发器进行实现,如果A表进行更新了,那么我们同时在B表进行表字段更新,同时进行B表的数据更新。
使用下一步的脚本,建立一个A表的触发器,注意表名,进行修改。
--创建一个A表的触发器
create trigger tr_table_a on table_a
for insert,update
as
begin
---同步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表增加字段address
alter table table_a
add address varchar(80)
alter table table_a
add 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_A
select 'liu'
union
select '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_a
for insert,update
as
begin
---同步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表增加字段address
alter table table_a
add address varchar(80)
alter table table_a
add 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
END