sql server 多表触发器

表的关系为：

a表是b表的主表

b表是c表的主表

对应关系为 ：

a对b  1对多

b对c  1对多

首先创建触发器 当a表数据删除是 自动删除b表数据 b表数据删除时自动删除c表数据 

--检测是否存在此触发器存在则删除重新建立 此语句为创建一个名称为tgr_a的触发器 触发器执行的操作为：当a表数据删除时删除b表中的子数据

if(object_id('tgr_a','tr') is not null)
drop trigger tgr_a
go
create trigger tgr_a
on a
for 
delete
as

declare @id int
select @id = a from Deleted; 
delete from b where a=@id
print '删除表a成功！';

--创建一个名称为tgr_b的触发器当b表数据删除时删除c表中的子数据，因为a删除一条数据b可能删除n条数据 b的一条数据又对应c的n条数据所以要对b的数据进行循环

if(object_id('tgr_b','tr') is not null)
drop trigger tgr_b
go
create trigger tgr_b
on b
for 
delete
as

--首先创建游标
declare @tempid int
declare blist cursor for select b from Deleted

--打开游标
open blist
fetch next from blist into @tempid

--循环游标
while @@fetch_status=0
begin

--此句为删除c表中的数据
delete from c where b=@tempid
--循环下一条数据 不写将进入死循环
fetch next from blist into @tempid
end

--关闭游标
close blist

--删除游标
deallocate blist

CREATE TABLE [dbo].[a](
[a] [int] NULL,
[name] [nvarchar](50) NULL
) ON [PRIMARY]

 

CREATE TABLE [dbo].[b](
[b] [int] NULL,
[name] [nvarchar](50) NULL,
[a] [int] NULL
) ON [PRIMARY]

 

CREATE TABLE [dbo].[c](
[c] [int] NULL,
[name] [varchar](50) NULL,
[b] [int] NULL
) ON [PRIMARY]

 附带 Instead of 触发器用法 自己查查吧

if(object_id('tgr_adelete','tr') is not null)
    drop trigger  tgr_adelete
go
Create Trigger tgr_adelete
  ON a
  Instead Of delete
AS

Begin
    set Nocount on
    print '已经设置'
    Declare @id int
    select @id=a from Deleted
    if(@id)=2
    begin
       print 'id等于2的不能删除你傻啊'
    end
    else
    begin
    --select * from a
       delete from a where a=@id
    end
End