ALTER PROCEDURE [dbo].[DelNode]
(
    @product_id varchar(25),
    @node_id int
)
AS
declare @lft int
declare @rgt int
if exists(select product_id, node_id from BOM where product_id = @product_id and node_id = @node_id)
    begin
        SET XACT_ABORT ON
        BEGIN TRANSACTION
            select @lft = lft, @rgt = rgt from BOM where product_id = @product_id and node_id = @node_id
            delete from BOM where product_id = @product_id and lft >= @lft and rgt <= @rgt
            update BOM set lft = lft - (@rgt - @lft + 1) where product_id = @product_id and lft > @lft
            update BOM set rgt = rgt - (@rgt - @lft + 1) where product_id = @product_id and rgt > @rgt
        COMMIT TRANSACTION
        SET XACT_ABORT OFF
    end

CREATE TABLE bom(ID int primary key,pid int)
insert into bom select 1,0
insert into bom select 2,1
insert into bom select 3,1
insert into bom select 4,3
create table bomdetail(id int,bomid int)
insert into bomdetail select 1,1
insert into bomdetail select 2,1
insert into bomdetail select 3,2
insert into bomdetail select 4,3
insert into bomdetail select 5,1
insert into bomdetail select 6,2

go
alter table bomdetail
add constraint FK_det_bom
foreign key(bomid) references bom
on delete cascade  --级联删除
on update cascade
go
delete from bom where id=2  --删除bom表 id 为 2 的行
select * from bomdetail  --子表中查询,已无bomid=2的行了
/*
id          bomid
----------- -----------
1           1
2           1
4           3
5           1

(4 行受影响)

*/
go
drop table bomdetail,bom