--
建立环境
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
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