USE [master] GO ALTER DATABASE Test SET RECURSIVE_TRIGGERS ON WITH NO_WAIT --設置Test庫為觸發器遞歸,遞歸層數不超過32層 GO use Test--指定數據庫 go if not object_id('Tab') is null drop table Tab Go Create table Tab([Id] int,[ParentId] int,[Name] nvarchar(3)) Insert Tab select 1,1,N'a1' union all select 2,2,N'a2' union all select 14,1,N'a13' union all select 15,1,N'b13' union all select 16,14,N'a14' union all select 17,14,N'b14' Go create trigger tr_Tab on Tab after delete as begin if @@rowcount>0 delete a from Tab a where exists(select 1 from deleted where ID=a.[ParentId]) end go delete Tab where ID=1select * from Tab給個例子,樓主參照
create trigger tr_Tab on Tab after delete as begin if @@rowcount>0 delete a from Tab a where exists(select 1 from deleted where ID=a.[ParentId]) end可以再部门和子部门2章表都加上删除触发器即可。 自己想想就能做到。
--生成测试数据 create table Dept(ID int,ParentID int,name varchar(20)) insert into Dept select 1,0,null insert into Dept select 2,1,null insert into Dept select 3,1,null insert into Dept select 4,2,null insert into Dept select 5,3,null insert into Dept select 6,5,null insert into Dept select 7,6,null go
--创建用户定义函数 Create function [dbo].[GetChild](@ID varchar(10)) returns @t table(ID varchar(10),ParentID varchar(10),Level int) as begin declare @i int set @i = 1 insert into @t select @ID,@ID,0 --当前级,本级,如果不要的话可以注释掉或再加个参数来选择操作 insert into @t select ID,ParentID,@i from Dept where ParentID = @ID
while @@rowcount<>0 begin set @i = @i + 1 insert into @t select a.ID,a.ParentID,@i from Dept a,@t b where a.ParentID=b.ID and b.Level = @i-1 end return end
go Create trigger Trg_Dept_Del on Dept for Delete as begin declare @id int select @id = ID from deleted delete from Dept where id in (select ID from dbo.GetChild(@id)) end
GO
ALTER DATABASE Test SET RECURSIVE_TRIGGERS ON WITH NO_WAIT --設置Test庫為觸發器遞歸,遞歸層數不超過32層
GO
use Test--指定數據庫
go
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Id] int,[ParentId] int,[Name] nvarchar(3))
Insert Tab
select 1,1,N'a1' union all
select 2,2,N'a2' union all
select 14,1,N'a13' union all
select 15,1,N'b13' union all
select 16,14,N'a14' union all
select 17,14,N'b14'
Go
create trigger tr_Tab on Tab
after delete
as
begin
if @@rowcount>0
delete a from Tab a where exists(select 1 from deleted where ID=a.[ParentId])
end
go
delete Tab where ID=1select * from Tab給個例子,樓主參照
after delete
as
begin
if @@rowcount>0
delete a from Tab a where exists(select 1 from deleted where ID=a.[ParentId])
end可以再部门和子部门2章表都加上删除触发器即可。
自己想想就能做到。
--生成测试数据
create table Dept(ID int,ParentID int,name varchar(20))
insert into Dept select 1,0,null
insert into Dept select 2,1,null
insert into Dept select 3,1,null
insert into Dept select 4,2,null
insert into Dept select 5,3,null
insert into Dept select 6,5,null
insert into Dept select 7,6,null
go
--创建用户定义函数
Create function [dbo].[GetChild](@ID varchar(10))
returns @t table(ID varchar(10),ParentID varchar(10),Level int)
as
begin
declare @i int
set @i = 1
insert into @t select @ID,@ID,0 --当前级,本级,如果不要的话可以注释掉或再加个参数来选择操作
insert into @t select ID,ParentID,@i from Dept where ParentID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.ParentID,@i
from
Dept a,@t b
where
a.ParentID=b.ID and b.Level = @i-1
end
return
end
go
Create trigger Trg_Dept_Del
on Dept
for Delete
as
begin
declare @id int
select @id = ID from deleted
delete from Dept where id in (select ID from dbo.GetChild(@id))
end