34,588
社区成员
发帖
与我相关
我的任务
分享
两张表数据同步 (添加、删除、修改 触发器)
--建立环境
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 trigger tri_dep_name
on authors
for update
as
begin
declare @DepName nvarchar(50),@newDepName nvarchar(50)
select @DepName=au_id from deleted
select @newDepName=au_id from inserted
update titleauthors set au_id=@newDepName where depName=@DepName
end