create table table1 (sno varchar(10),sname varchar(10))
create table table2 (sno varchar(10),sname varchar(10))
go
create trigger t_table1 on table1
after delete,insert,update
as
begin
delete from table2 where sno in (select sno from deleted)
if not exists (select 1 from table2 a,inserted i where a.sno=i.sno)
insert into table2
select * from inserted
else
update a set a.sname=i.sname from table2 a,inserted i where a.sno=i.sno
end
go
/**********插入记录************/
insert into table1
select '0001','aa' union all
select '0002','bb' union all
select '0004','dd'
select * from table1
select * from table2
--table1
/*
sno sname
----- -------
0001 aa
0002 bb
0004 dd
--table2
sno sname
------ -------
0001 aa
0002 bb
0004 dd
*/
/********删除记录*************/
delete from table1 where sno='0004'
select * from table1
select * from table2
--table1
/*
sno sname
----- -------
0001 aa
0002 bb
--table2
sno sname
------ -------
0001 aa
0002 bb
*/
/***********更新记录************/
update table1 set sname='cc' where sno='0002'
select * from table1
select * from table2
--table1
/*
sno sname
----- -------
0001 aa
0002 cc
--table2
sno sname
------ -------
0001 aa
0002 cc
*/
/********删除测试**********/
drop table table1,table2
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/bqb/archive/2008/01/28/2069578.aspx
评论