SQL server 一张表随另一张表变化,建立触发器
create trigger t_delf on files --建立删除触发器
instead of delete
as
begin
declare @id char(100)
declare @sid char(100)
select @id=Fname from deleted
select @sid=Rno from deleted
delete from Allfiles where Fname=@id and no=@sid
delete from files where Fname=@id and Rno=@sid
end
go
create trigger t_delaf on Adminfiles
instead of delete
as
begin
declare @id char(100)
declare @sid char(100)
select @id=Fname from deleted
select @sid=Aname from deleted
delete from Allfiles where Fname=@id and no=@sid
delete from Adminfiles where Fname=@id and Aname=@sid
end
go
create trigger t_addf on files ----建立插入触发器
instead of insert
as
begin
declare @id char(100)
declare @sid char(100)
select @id=Fname from inserted
select @sid=Rno from inserted
insert into Allfiles select* from inserted where Fname=@id and Rno=@sid
insert into files select* from inserted where Fname=@id and Rno=@sid
end
go
create trigger t_addaf on adminfiles
instead of insert
as
begin
declare @id char(100)
declare @sid char(100)
select @id=Fname from inserted
select @sid=Aname from inserted
insert into Allfiles select* from inserted where Fname=@id and Aname=@sid
insert into adminfiles select* from inserted where Fname=@id and Aname=@sid
end
go
insert into adminfiles values('a','a','a','minoz') --插入测试
insert into files values('a','a','a','00000001')
delete from Adminfiles where fname='a'--删除测试
drop trigger t_addf --删除触发器
drop trigger t_addaf
drop trigger t_delf
drop trigger t_delaf