有两表。表字段如下
表1 oldldbiao
admin dhlx sjhm
001 来电已接 13901252101
002 来电已接 13901252101
001 来电未接 13901252101
002 来电未接 13901252101
003 来电已接 1302120210
表2 newldbiao
admin dhlx sjhm
001 来电已接 13901252101
002 来电已接 13901252101
003 来电已接 15902102102
表2记录不能增加与删除
我现在要求是每次表1添加记录时候,要求对表2的记录进行刷新
刷新规律是
当表1增加一条记录时候。当dhlx='来电已接'时。用表2的sjhm去刷新表1的同一admin的sjhm
表1 oldldbiao
admin dhlx sjhm
001 来电已接 13901252101
002 来电已接 13901252101
001 来电未接 13901252101
002 来电未接 13901252101
003 来电已接 1302120210
表2 newldbiao
admin dhlx sjhm
001 来电已接 13901252101
002 来电已接 13901252101
003 来电已接 15902102102
表2记录不能增加与删除
我现在要求是每次表1添加记录时候,要求对表2的记录进行刷新
刷新规律是
当表1增加一条记录时候。当dhlx='来电已接'时。用表2的sjhm去刷新表1的同一admin的sjhm
create table oldldbiao
(
admin varchar(3),
dhlx varchar(10),
sjhm varchar(11)
)
insert into oldldbiao
select '001', '来电已接', '13901252101' union all
select '002', '来电已接', '13901252101' union all
select '001', '来电未接', '13901252101' union all
select '002', '来电未接', '13901252101' union all
select '003', '来电已接', '13021202101' create table newldbiao
(
admin varchar(3),
dhlx varchar(10),
sjhm varchar(11)
)
insert into newldbiao
select '001', '来电已接', '13901252101' union all
select '002', '来电已接', '13901252101' union all
select '003', '来电已接', '15902102102' create trigger tr_ins_old on oldldbiao for insert
as
begin
declare @admin varchar(3)
declare @dhlx varchar(10)
declare @sjhm varchar(21)
declare cur cursor for select * from inserted where dhlx='来电已接'
open cur
fetch next from cur into @admin,@dhlx,@sjhm
while @@fetch_status=0
begin
update newldbiao set sjhm=@sjhm where admin=@admin
fetch next from cur into @admin,@dhlx,@sjhm
end
close cur
deallocate cur
end
create trigger tr_tab1 on 表1
after insert
as
begin
if exists(select 1 from inserted where dhlx='来电已接')
update a
set a.sjhm=b.sjhm
from 表1 a
inner join 表2 b
on a.admin=b.admin
end