大概流程:
在删除部门时,同事删除部门的子部门出现的问题:
用于删除子部门的递归存储过程单独调用时没有问题,
但在触发器中调用时没有作用
表结构:
部门表
ID 自身编号 父部门编号 名字--触发器:
Create Trigger trdDepartment
On Department
For Delete
As--得到删除的部门ID
Set @DepartmentID = (Select ID From Deleted)
--删除自己和子部门
Declare @Code Varchar(20)--部门自身编号
Select @Code = Department_Code From Department Where ID = @DepartmentID
Exec Up_DepartmentDeleteChild_Delete @Code存储过程:
Create Proc UP_DepartmentDeleteChild_Delete
@selfCode Varchar(20)--自身编号
As
Declare @Code Varchar(20)--暂存游标中的编号
Declare cur_chidDepats Cursor local forward_only
For Select Department_Code From Department Where Department_ParentCode = @selfCode
Open cur_chidDepats Fetch next From cur_chidDepats Into @Code
While @@Fetch_status = 0
Begin
Exec UP_DepartmentDeleteChild_Delete @Code
Delete From Department Where Department_ParentCode = @selfCode--删除子部门
Fetch next From cur_chidDepats Into @Code
End
Close cur_chidDepats
Deallocate cur_chidDepats
Delete From Department Where Department_Code = @selfCode--删除自己
Go
在删除部门时,同事删除部门的子部门出现的问题:
用于删除子部门的递归存储过程单独调用时没有问题,
但在触发器中调用时没有作用
表结构:
部门表
ID 自身编号 父部门编号 名字--触发器:
Create Trigger trdDepartment
On Department
For Delete
As--得到删除的部门ID
Set @DepartmentID = (Select ID From Deleted)
--删除自己和子部门
Declare @Code Varchar(20)--部门自身编号
Select @Code = Department_Code From Department Where ID = @DepartmentID
Exec Up_DepartmentDeleteChild_Delete @Code存储过程:
Create Proc UP_DepartmentDeleteChild_Delete
@selfCode Varchar(20)--自身编号
As
Declare @Code Varchar(20)--暂存游标中的编号
Declare cur_chidDepats Cursor local forward_only
For Select Department_Code From Department Where Department_ParentCode = @selfCode
Open cur_chidDepats Fetch next From cur_chidDepats Into @Code
While @@Fetch_status = 0
Begin
Exec UP_DepartmentDeleteChild_Delete @Code
Delete From Department Where Department_ParentCode = @selfCode--删除子部门
Fetch next From cur_chidDepats Into @Code
End
Close cur_chidDepats
Deallocate cur_chidDepats
Delete From Department Where Department_Code = @selfCode--删除自己
Go
On Department
For Delete
As--得到删除的部门ID
Set @DepartmentID = (Select ID From Deleted)
--删除自己和子部门
Declare @Code Varchar(20)--部门自身编号
Select @Code = Department_Code From Department Where ID = @DepartmentID
Exec Up_DepartmentDeleteChild_Delete @Code
变量都不声明
On Department
For Delete
As --得到删除的部门ID
--Set @DepartmentID = (Select ID From Deleted)
--删除自己和子部门
Declare @Code Varchar(8000)--部门自身编号
Select @Code = Department_Code From Department Where ID IN (Select ID From Deleted)
Exec Up_DepartmentDeleteChild_Delete @Code这样试试
On Department
For Delete
As --得到删除的部门ID
--Set @DepartmentID = (Select ID From Deleted)
--删除自己和子部门
Declare @Code Varchar(8000)--部门自身编号
Select @Code = Department_Code From Deleted Exec Up_DepartmentDeleteChild_Delete @Code是同一个表,你删除了还能成,
--try:--触发器:
Create Trigger trdDepartment On Department
For Delete
As
;with wsp
as
(
select department_code from deleted
union all
select a.department_code from department a,wsp b Where a.Department_ParentCode=b.department_code
)
delete department from wsp a,department b where a.department_code=b.department_code
go
if object_id('[Department]') is not null drop table [Department]
create table [Department] (department_code int,name varchar(2),Department_ParentCode int)
insert into [Department]
select 1,'aa',0 union all
select 2,'bb',1 union all
select 3,'cc',2 union all
select 4,'dd',3 union all
select 5,'ee',4 union all
select 6,'ff',0
--触发器:
Create Trigger trdDepartment On Department
For Delete
As
;with wsp
as
(
select department_code from deleted
union all
select a.department_code from department a,wsp b Where a.Department_ParentCode=b.department_code
)
delete department from wsp a,department b where a.department_code=b.department_code
godelete [Department] where department_code=1
select * from [Department]--结果:
department_code name Department_ParentCode
--------------- ---- ---------------------
6 ff 0
;with wsp
As()是什么意思?