SQL server 一张表随另一张表变化，建立触发器

最新推荐文章于 2023-01-10 15:08:09 发布

原创最新推荐文章于 2023-01-10 15:08:09 发布 · 889 阅读

1 ·

CC 4.0 BY-SA版权

SQL server 专栏收录该内容

1 篇文章

订阅专栏

博客介绍了在SQL Server中，当一张表随另一张表变化时，可通过建立触发器来实现。这涉及到数据库的操作与管理，利用触发器能有效处理表之间的关联变化。

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