create trigger tr_tab_insert
on tab
for insert
as

update a set
    path=cast(i.pid as varchar)+','+b.path,
    classid=b.classid+1
from tab a,inserted i,tab b
where a.id=i.id
and b.id=i.pid

go

------解决方案--------------------
1直接插入就可以
2 ..
3delete tb where path like '%'+(select path from tb where id=3)

------解决方案--------------------
1,--insert
INSERT tt SELECT 9,8,(SELECT RTRIM(id) + ',' + path  FROM tt WHERE ID=8),8
SELECT * FROM tt
2,要写函数或循环,分别针对目标pid已存在于路径中,和目标pid不存在于路径中.
3,比较简单.


路径法的树,查询容易,修改麻烦.
bom的树,查询麻烦,修改一般.

------解决方案--------------------

SQL code
--建立测试环境
create table BOM(id int,pid int,path varchar(20),classid int)
insert into BOM values(1,0,rtrim('0          '),1)
insert into BOM values(2,1,rtrim('1          '),2)
insert into BOM values(3,2,rtrim('2,1        '),3)
insert into BOM values(4,3,rtrim('3,2,1      '),4)
insert into BOM values(5,0,rtrim('0          '),1)
insert into BOM values(6,4,rtrim('4,3,2,1    '),5)
insert into BOM values(7,6,rtrim('6,4,3,2,1  '),6)
insert into BOM values(8,7,rtrim('7,6,4,3,2,1'),7)
go

--插入记录id 9 pid 8 获得path classid 
declare @ID int,@PID int
set @ID=9
set @PID=8

insert into BOM
select 
    @ID ,
    @PID,
    isnull(rtrim(id),'')+isnull((case path when '0' then '' else ','+path end),'0'),
    isnull(classid,0)+1
from 
    BOM
where
    id=@PID 

--select * from BOM

--修改记录id 6 的pid为2时 修改其path,classid和其子id的path,classid 
set @ID =6
set @PID=2

update BOM 
set 
    pid=@PID,
    path=(case @PID when 0 then '' else rtrim(@PID) end)+(select isnull(','+path,'') from BOM where ID=@PID)
where 
    ID=@ID

update BOM
set
    path=left(path,charindex(','+rtrim(@ID)+',',','+path+',')-1)+rtrim(@ID)+(select isnull(','+path,'') from BOM where ID=@ID) 
where
    charindex(','+rtrim(@ID)+',',','+path+',')>0

--删除记录id 3 把其子id都删掉 
set @ID=3
delete BOM where id=@ID or charindex(','+rtrim(@ID)+',',','+path+',')>0

--清除测试环境
drop table BOM 
go