22,207
社区成员
发帖
与我相关
我的任务
分享
--可以合成一个触发器
--并增加处理一条记录的检测
create trigger tr_nested_category
on nested_category
for insert,update,delete
as
set nocount on
--修改其他字段退出
if exists (select 1 from deleted)
and exists (select 1 from inserted)
and (not update(parent_id))
return
if (select count(1) from deleted)>1 or (select count(1) from inserted)>1
begin
RAISERROR ('一次只能处理一条记录.', 16, 1)
ROLLBACK TRANSACTION
return
end
declare @lft int
declare @rgt int
declare @id int
--删除操作
select @lft=lft,@rgt=rgt,@id=category_id from deleted
update nested_category
set lft=lft-1
where lft>=@rgt
update nested_category
set rgt=rgt-1
where rgt>=@rgt
update nested_category
set lft=lft-1
where lft>=@lft
update nested_category
set rgt=rgt-1
where rgt>=@lft
--插入操作
select @rgt=p.rgt,@id=i.category_id from nested_category p,inserted i
where i.parent_id=p.category_id
update nested_category
set lft=lft+2
where lft>=@rgt
update nested_category
set rgt=rgt+2
where rgt>=@rgt
update nested_category
set lft=@rgt,rgt=@rgt+1
where category_id=@id
go
--注意,同样需要限定一次只能更新一条记录
--可以理解为先删除一条记录,再插入一条记录
create trigger tr_nested_category_update
on nested_category
for update
as
declare @lft int
declare @rgt int
declare @id int
if update (parent_id) ---只有更新parent_id字段时才需要触发
begin
--删除操作
select @lft=lft,@rgt=rgt,@id=category_id from deleted
update nested_category
set lft=lft-1
where lft>=@rgt
update nested_category
set rgt=rgt-1
where rgt>=@rgt
update nested_category
set lft=lft-1
where lft>=@lft
update nested_category
set rgt=rgt-1
where rgt>=@lft
--插入操作
select @rgt=p.rgt,@id=i.category_id from nested_category p,inserted i
where i.parent_id=p.category_id
update nested_category
set lft=lft+2
where lft>=@rgt
update nested_category
set rgt=rgt+2
where rgt>=@rgt
update nested_category
set lft=@rgt,rgt=@rgt+1
where category_id=@id
end
go
select @lft=lft,@rgt=rgt,@id=category_id from deleted
--删除触发器
--同样只能一次删除一条记录
--而且不能删除非叶子节点
create trigger tr_nested_category_delete
on nested_category
for delete
as
declare @lft int
declare @rgt int
declare @id int
select @lft=lft,@rgt=rgt,@id=i.category_id from inserted
update nested_category
set lft=lft-1
where lft>=@rgt
update nested_category
set rgt=rgt-1
where rgt>=@rgt
update nested_category
set lft=lft-1
where lft>=@lft
update nested_category
set rgt=rgt-1
where rgt>=@lft
go
--插入触发器
--必须限定一次只能插入一条记录,代码里没写这个限定
create trigger tr_nested_category_Insert
on nested_category
for insert
as
declare @rgt int
declare @id int
select @rgt=p.rgt,@id=i.category_id from nested_category p,inserted i
where i.parent_id=p.category_id
update nested_category
set lft=lft+2
where lft>=@rgt
update nested_category
set rgt=rgt+2
where rgt>=@rgt
update nested_category
set lft=@rgt,rgt=@rgt+1
where category_id=@id
go