tab1: id , name ,pwd ,date ,ip
tab2: id , name ,pwd ,address,zip我是想:
1、无论是哪个程序tab1添加一条记录,就把name,pwd 提取出来增加到tab2中;2、无论是哪个程序tab1删除一条记录,就把tab2中,name相同的记录也删除掉;3、无论是哪个程序tab1修改了其中一条记录,就把tab2中,name相同的记录的中相应的字段(如pwd)也得到修改;
tab2: id , name ,pwd ,address,zip我是想:
1、无论是哪个程序tab1添加一条记录,就把name,pwd 提取出来增加到tab2中;2、无论是哪个程序tab1删除一条记录,就把tab2中,name相同的记录也删除掉;3、无论是哪个程序tab1修改了其中一条记录,就把tab2中,name相同的记录的中相应的字段(如pwd)也得到修改;
create trigger tri_tab1 on tab1 for insert,update,delete
as
if exists(select 1 from tab2 a where exists(select 1 from inserted where id=a.id))
begin
update tab2 set name=b.name,pwd=b.pwd from tab2 a,inserted b where a.id=b.id
end
else
begin
insert into tab2(id,name,pwd) select id,name,pwd from inserted
end
if exists(select 1 from tab2 a where exists(select 1 from deleted where id=a.id))
begin
delete tab2 from tab2 a,deleted b where a.id=b.id
end
--顺序换一下:
create trigger tri_tab1 on tab1 for insert,update,delete
as
if exists(select 1 from tab2 a where exists(select 1 from deleted where id=a.id))
begin
delete tab2 from tab2 a,deleted b where a.id=b.id
end
if exists(select 1 from tab2 a where exists(select 1 from inserted where id=a.id))
begin
update tab2 set name=b.name,pwd=b.pwd from tab2 a,inserted b where a.id=b.id
end
else
begin
insert into tab2(id,name,pwd) select id,name,pwd from inserted
end
ON tab1
FOR insert
as
insert tab2
select name,pwd from insertedgo
on tab1
for insert,update,delete
as
set nocount on
if not exists (select 1 from inserted)
begin
--无论是哪个程序tab1删除一条记录,就把tab2中,name相同的记录也删除掉;
delete a from tab2 a join deleted b on a.name=b.name
end
if not exists (select 1 from deleted)
begin
--无论是哪个程序tab1添加一条记录,就把name,pwd 提取出来增加到tab2中;
insert tab2(name,pwd) select name,pwd from inserted
end
else
begin
--无论是哪个程序tab1修改了其中一条记录,就把tab2中,name相同的记录的中相应的字段(如pwd)也得到修改;
delete a from tab2 a join deleted b on a.name=b.name
insert tab2(name,pwd) select name,pwd from inserted
end
set nocount off
create trigger tr1 on tab1
for insert, update, delete
as
begin
delete from tab2 where id in (select id from deleted)
insert tab2 select * from inserted
end
go
你这有问题吧!?
for insert, update, delete
as
begin
delete from tab2 where id in (select id from deleted)
insert tab2(id,name,pwd) select id,name,pwd from inserted
end
go 为什么第二行中有 "for insert, update, delete ", 而第五行却只有"delete from tab2 where id in (select id from deleted) " 这第5行是何意?