create trigger tr_tb1_u on tb1 for update as update tb2 set tb2.col1 = inserted.col1,tb2.col2 = inserted.col2.. from tb2,inserted where tb2.keycol = inserted.keycol
--那再加個add動作就可以了. create trigger tr_tb1_u on tb1 for update as update tb2 set tb2.col1 = inserted.col1,tb2.col2 = inserted.col2.. from tb2,inserted where tb2.keycol = inserted.keycol insert tb2 select * from inserted where not exsits (select 1 from tb2 A where A.keycol = inserted.keycol and inserted.col1 = A.col1)
create trigger 名 on tb1 for update as if exists(select A from tb1 group by A having count(A)>1) begin insert into tb2 select * from inserted where tb2.主键 = inserted.主键 end update tb2 set tb2.字段 = inserted.字段,…… from tb2,inserted where tb2.主键 = inserted.主键
create trigger tr_tb1_u on tb1 for update as insert tb2 select c1,c2,c3 from inserted a,tb2 b where checksum(a.c1,a.c2,a.c3)<>checksum(b.c1,b.c2,b.c3)
create trigger tr_tb1_u on tb1 for update as insert tb2 select c1,c2,c3 from inserted a where not exists(select 1 from tb2 where checksum(a.c1,a.c2,a.c3)=checksum(c1,c2,c3)
7楼是不是没有体现update,只是说明一下当更改时插入tb2中的情况
create table tb1 (a int ,b int ,c int ,d int) go create table tb2 (a int ,b int ,c int ,d int) go insert into tb1 values(1,2,3,4) go create trigger t_update on tb1 for update as set nocount on insert into tb2 select * from inserted i where not exists ( select 1 from tb2 where a=i.a and b=i.b and c=i.c and d=i.d ) go update tb1 set c=100 go select * from tb2 go drop table tb1,tb2
create table tb1(a int ,b int ,c int ,d int)--a is the primary key go create table tb2(b int ,c int, d int) go create trigger t_insert on tb1 for insert as set nocount on insert into tb2(b,c,d) select distinct b,c,d from inserted t where not exists (select 1 from tb2 where a=t.a and b=t.b and c=t.c and d=t.d) set nocount off go insert into tb1 values(1,1,2,3) insert into tb1 values(2,1,2,3) insert into tb1 values(3,3,3,4) insert into tb1 values(4,4,4,5) go select * from tb1 /* a b c d 1 1 2 3 2 1 2 3 3 3 3 4 4 4 4 5 */ select * from tb2 /* b c d 1 2 3 3 3 4 4 4 5 */ go create trigger t_update on tb1 for update as set nocount on if exists (select 1 from tb1 inner join deleted d on tb1.b=d.b and tb1.c=d.c and tb1.d=d.d where tb1.a<>d.a) begin insert into tb2(b,c,d) select distinct i.b,i.c,i.d from inserted i where not exists ( select 1 from (select tb2.* from tb2 inner join deleted d on tb2.b=d.b and tb2.c=d.c and tb2.d=d.d) x where x.b=i.b and x.c=i.c and x.d=i.d ) end else update tb2 set b=i.b,c=i.c,d=i.d from tb2 inner join deleted d on tb2.b=d.b and tb2.c=d.c and tb2.d=d.d inner join inserted i on i.a=d.aset nocount off go update tb1 set c=100 where a=2--insert go select * from tb2 /* b c d 1 2 3 3 3 4 4 4 5 1 100 3 */ go update tb1 set c=100 where a=3--update go select * from tb2 /* b c d 1 2 3 3 100 4 4 4 5 1 100 3 */go drop table tb1,tb2
/******************************************/ /*回复:代码20080516002 总:00000000015 */ /*主题:通过触发器自动记录原表非重复数据 */ /*作者:二等草 */ /******************************************/ set nocount on /************例子数据 begin****************/ create table ta(a int,b int,c int)--a,b为主键 create table tb(b int,c int) --记录ta表存在的b,c不同的值 go /************例子数据 end******************//************代码 begin***************/ create trigger t_ab on ta for insert,update as begin delete a from tb a where exists(select 1 from deleted b where a.b = b and a.c = c and not exists(select 1 from ta where b.b=b and b.c = c)) insert tb select distinct b,c from inserted a where not exists(select 1 from tb where a.b=b and a.c = c) end go --测试 insert ta select 1,2,3 union select 2,2,3 union select 3,2,3 union select 1,3,2 select distinct 'a', b,c from ta select 'b',* from tb update ta set c = 6 where a =1 or a = 2 select distinct 'a',b,c from ta select 'b',* from tb go /************代码 end*****************//************结果 begin*************** b c ---- ----------- ----------- a 2 3 a 3 2 b c ---- ----------- ----------- b 2 3 b 3 2 b c ---- ----------- ----------- a 2 3 a 2 6 a 3 6 b c ---- ----------- ----------- b 2 3 b 2 6 b 3 6 ************结果 end*****************//************清除*************************/ drop table ta,tb
as
update tb2 set tb2.col1 = inserted.col1,tb2.col2 = inserted.col2..
from tb2,inserted where tb2.keycol = inserted.keycol
create trigger tr_tb1_u on tb1 for update
as
update tb2 set tb2.col1 = inserted.col1,tb2.col2 = inserted.col2..
from tb2,inserted where tb2.keycol = inserted.keycol
insert tb2
select * from inserted where not exsits (select 1 from tb2 A where A.keycol = inserted.keycol and inserted.col1 = A.col1)
create trigger 名
on tb1 for update
as
if exists(select A from tb1 group by A having count(A)>1)
begin
insert into tb2 select * from inserted where tb2.主键 = inserted.主键
end
update tb2 set tb2.字段 = inserted.字段,……
from tb2,inserted where tb2.主键 = inserted.主键
as
insert tb2 select c1,c2,c3 from inserted a,tb2 b
where checksum(a.c1,a.c2,a.c3)<>checksum(b.c1,b.c2,b.c3)
as
insert tb2 select c1,c2,c3 from inserted a
where not exists(select 1 from tb2 where checksum(a.c1,a.c2,a.c3)=checksum(c1,c2,c3)
go
create table tb2 (a int ,b int ,c int ,d int)
go
insert into tb1 values(1,2,3,4)
go
create trigger t_update on tb1 for update
as
set nocount on
insert into tb2 select * from inserted i
where
not exists
(
select 1 from tb2
where a=i.a
and b=i.b
and c=i.c
and d=i.d
)
go
update tb1 set c=100
go
select * from tb2
go
drop table tb1,tb2
go
create table tb2(b int ,c int, d int)
go
create trigger t_insert on tb1
for insert
as
set nocount on
insert into tb2(b,c,d) select distinct b,c,d from inserted t
where not exists (select 1 from tb2 where a=t.a and b=t.b and c=t.c and d=t.d)
set nocount off
go
insert into tb1 values(1,1,2,3)
insert into tb1 values(2,1,2,3)
insert into tb1 values(3,3,3,4)
insert into tb1 values(4,4,4,5)
go
select * from tb1
/*
a b c d
1 1 2 3
2 1 2 3
3 3 3 4
4 4 4 5
*/
select * from tb2
/*
b c d
1 2 3
3 3 4
4 4 5
*/
go
create trigger t_update on tb1 for update
as
set nocount on
if exists (select 1 from tb1 inner join deleted d
on tb1.b=d.b and tb1.c=d.c and tb1.d=d.d
where tb1.a<>d.a)
begin
insert into tb2(b,c,d) select distinct i.b,i.c,i.d from inserted i
where
not exists
(
select 1 from
(select tb2.* from tb2
inner join deleted d
on tb2.b=d.b
and tb2.c=d.c
and tb2.d=d.d) x
where x.b=i.b
and x.c=i.c
and x.d=i.d
)
end
else
update tb2 set b=i.b,c=i.c,d=i.d
from tb2 inner join deleted d
on tb2.b=d.b and tb2.c=d.c and tb2.d=d.d
inner join inserted i
on i.a=d.aset nocount off
go
update tb1 set c=100 where a=2--insert
go
select * from tb2
/*
b c d
1 2 3
3 3 4
4 4 5
1 100 3
*/
go
update tb1 set c=100 where a=3--update
go
select * from tb2
/*
b c d
1 2 3
3 100 4
4 4 5
1 100 3
*/go
drop table tb1,tb2
trigger t_update 是今天是问的这个关注好的解决办法
http://topic.csdn.net/u/20080515/19/92c063d4-2b3b-42fe-8d37-33e253355f38.html
不过已经结贴啦,呵呵,但我还是想看看17楼是不是其它的实现方式,以便增进更进一步学习,没分啦,不过纯粹是学习,谢谢啦!
/*回复:代码20080516002 总:00000000015 */
/*主题:通过触发器自动记录原表非重复数据 */
/*作者:二等草 */
/******************************************/
set nocount on
/************例子数据 begin****************/
create table ta(a int,b int,c int)--a,b为主键
create table tb(b int,c int) --记录ta表存在的b,c不同的值
go
/************例子数据 end******************//************代码 begin***************/
create trigger t_ab on ta for insert,update
as
begin
delete a from tb a where exists(select 1 from deleted b where a.b = b and a.c = c
and not exists(select 1 from ta where b.b=b and b.c = c))
insert tb select distinct b,c from inserted a where not exists(select 1 from tb where a.b=b and a.c = c)
end
go
--测试
insert ta select 1,2,3 union select 2,2,3 union select 3,2,3 union select 1,3,2
select distinct 'a', b,c from ta
select 'b',* from tb
update ta set c = 6 where a =1 or a = 2
select distinct 'a',b,c from ta
select 'b',* from tb
go
/************代码 end*****************//************结果 begin***************
b c
---- ----------- -----------
a 2 3
a 3 2 b c
---- ----------- -----------
b 2 3
b 3 2 b c
---- ----------- -----------
a 2 3
a 2 6
a 3 6 b c
---- ----------- -----------
b 2 3
b 2 6
b 3 6 ************结果 end*****************//************清除*************************/
drop table ta,tb