--求一触发器,自动维护树形数据的级数及路径!

--求一触发器,自动维护树形数据的级数及路径!
--表结构如下:
item_id numeric --子id
item_de varchar(80) --子名称
parent_id numeric --父id
level_seq int --级别(树中的层数,树顶为1,向下递增)
path varchar(8000) --路径(从最顶层到本层的item_de相加,中间用_连接)
用户只录入前三项数据,要求在触发器中自动维护后两个字段的内容!
create table t(item_id numeric(20,0),item_de varchar(80),parent_id numeric(20,0),level_seq int,path varchar(8000))

insert into t(item_id,item_de,parent_id)
select 222,'aaa',0
union
select 324,'bbb',222
union
select 56,'ccc',324
union
select 543,'d',324

--要求插入上数据后,查询结果为
select * from t
/*
56 ccc 324 3 aaa_bbb_ccc
222 aaa 0 1 aaa
324 bbb 222 2 aaa_bbb
543 d 324 3 aaa_bbb_d
*/


执行如下语句,
update t
set item_de = 'dd'
where item_id = 324
查询数据
select * from t
结果如下
/*
56 ccc 324 3 aaa_dd_ccc
222 aaa 0 1 aaa
324 dd 222 2 aaa_dd
543 d 324 3 aaa_dd_d
*/


执行如下语句,
update t
set parent_id = 56
where item_id = 543
查询数据
select * from t
结果如下
/*
56 ccc 324 3 aaa_dd_ccc
222 aaa 0 1 aaa
324 dd 222 2 aaa_dd
543 d 56 4 aaa_dd_ccc_d
*/

11 个解决方案

#1

不会！

#2

create trigger ttt on t
after insert,update
as
set nocount on
declare @lev int
declare @path nvarchar(300)
set @lev = 1
set @path = ''

update t set level_seq=(
select 
case i.level_seq
when null then 1
else isnull(t.level_seq,0) + 1
end as lev
--isnull(i.level_seq,1)
from inserted as i
left join t as t
on i.parent_id = t.item_id)
,
path = (select 
substring(isnull(t.path,isnull(t.item_de,''))+'_'+ i.item_de,2,
len(isnull(t.path,isnull(t.item_de,''))+'_'+ i.item_de)-1) as path
from inserted as i
left join t as t
on i.parent_id = t.item_id
)
from inserted as ins
where t.item_id = ins.item_id

#3

调试的时候, 当一次性插入多个数据时,会出错
因为当一次性插入多层结构时, 会出现在原表中找不到匹配情况的问题

#4

有两个问题
1:单条新增时,path计算错误!
insert into t(item_id,item_de,parent_id)
select 222,'aaa',0
go

insert into t(item_id,item_de,parent_id)
select 324,'bbb',222
go
insert into t(item_id,item_de,parent_id)
select 56,'ccc',324
go
insert into t(item_id,item_de,parent_id)
select 543,'d',324
go
--要求插入上数据后,查询结果为
select * from t
/*
222 aaa 0 1 aaa
324 bbb 222 2 aa_bbb
56 ccc 324 3 a_bbb_ccc
543 d 324 3 a_bbb_d

2:更新item_de时,所有受影响到的path没有更新,只更新了本条记录,子集没有更新到!!!!!

#5

--创建数据表
create table t(item_id int,item_de varchar(1000),parent_id int,level_seq int,path varchar(1000))
go

--创建用户定义函数
create function f_getseq(@ITEM_ID int,@type int)
returns varchar(8000)
as
begin
    declare @t table(ITEM_ID int,ITEM_DE varchar(10),Parent_ID int,Level int)
    declare @i int,@ret varchar(8000)
    set @i = 1
    
    insert into @t select ITEM_ID,ITEM_DE,Parent_ID,@i from t where ITEM_ID=@ITEM_ID
    
    while @@rowcount<>0
    begin
        set @i=@i+1
        
        insert into @t 
        select a.ITEM_ID,a.ITEM_DE,a.Parent_ID,@i from t a,@t b where a.ITEM_ID=b.Parent_ID and b.Level=@i-1
    end
    
    if(@type=1)
        select @ret = cast(max(Level) as varchar(10)) from @t
    else
    begin
        select @ret = isnull(@ret,'') +'_'+ ITEM_DE from @t order by Level DESC
        set @ret = stuff(@ret,1,1,'')
    end
    return @ret
end
go

--创建触发器
create trigger trg_t
on t
for insert,update
as
begin
    if exists(select 1 from deleted) and update(Parent_ID)
        update t set level_seq=dbo.f_getseq(ITEM_ID,1),path=dbo.f_getseq(ITEM_ID,2)
    else
        update a set level_seq=dbo.f_getseq(a.ITEM_ID,1),path=dbo.f_getseq(a.ITEM_ID,2)
        from t a,inserted b
        where a.ITEM_ID=b.ITEM_ID
end
go

--执行数据插入操作
insert into t(item_id,item_de,parent_id) select 222,'aaa',0
insert into t(item_id,item_de,parent_id) select 324,'bbb',222
insert into t(item_id,item_de,parent_id) select 56 ,'ccc',324
insert into t(item_id,item_de,parent_id) select 543,'d'  ,324

--查看结果
select * from t

--输出结果
/*
222    aaa      0      1      aaa
324    bbb      222    2      aaa_bbb
56     ccc      324    3      aaa_bbb_ccc
543    d        324    3      aaa_bbb_d
*/

--执行更新操作
update t set parent_id = 56 where item_id = 543

--查看结果
select * from t

--输出结果
/*
222    aaa      0       1     aaa
324    bbb      222     2     aaa_bbb
56     ccc      324     3     aaa_bbb_ccc
543    d        56      4     aaa_bbb_ccc_d
*/


--删除测试数据
drop function f_getseq
drop table t
go

#6

create proc vvv
as
set nocount on
declare @level int
declare @path nvarchar(300)
declare @first int
declare @tb table(tid int,lev int,path nvarchar(300))
set @level = 1
set @path = ''
select @first=item_id,@path = item_de from t where parent_id = 0
insert @tb select @first,@level,@path
--select * from  @tb
while @@rowcount > 0
begin
set @level = @level +1
insert @tb
select t.item_id,@level,cast(tb.path as nvarchar(30)) + '_'+ cast(t.item_de as nvarchar(30))
from t join @tb as tb
on t.parent_id = tb.tid
where tb.lev = @level -1
end
update t set level_seq = nt.lev, path = nt.path from @tb as nt where t.item_id = nt.tid

create trigger ttt on t
after insert,update
as
exec vvv
exec vvv

#7

测试数据
------------------------------------------------------------
insert into t(item_id,item_de,parent_id)
select 222,'aaa',0

insert into t(item_id,item_de,parent_id)
select 324,'bbb',222

insert into t(item_id,item_de,parent_id)
select 56,'ccc',324

insert into t(item_id,item_de,parent_id)
select 543,'d',324

select * from t
/--------------
222 aaa 0 1 aaa
324 bbb 222 2 aaa_bbb
56 ccc 324 3 aaa_bbb_ccc
543 d 324 3 aaa_bbb_d
--------------------/
update t set item_de = 'abcde' where item_id = 222
select * from t
/--------------------------
222 abcde 0 1 abcde
324 bbb 222 2 abcde_bbb
56  ccc 324 3 abcde_bbb_ccc
543 d 324 3 abcde_bbb_d
-------------------------------/

#8

to:libin_ftsafe

/*
更新item_de时,所有受影响到的path没有更新,只更新了本条记录,子集没有更新到!!!!!
update t set item_de = 'asf' where item_id = 222
--查看结果
select * from t
/*222 asf 0 1 asf
324 bbb 222 2 aaa_bbb
56 ccc 324 3 aaa_bbb_ccc
543 d 56 4 aaa_bbb_ccc_d
*/

#9

to:libin_ftsafe 及各位大虾

/*
更新item_de时,所有受影响到的path没有更新,只更新了本条记录,子集没有更新到!!!!!
update t set item_de = 'asf' where item_id = 222
--查看结果
select * from t
/*222 asf 0 1 asf
324 bbb 222 2 aaa_bbb
56 ccc 324 3 aaa_bbb_ccc
543 d 56 4 aaa_bbb_ccc_d
*/
正确的结果应该是:

/*222 asf 0 1 asf
324 bbb 222 2 asf_bbb
56 ccc 324 3 asf_bbb_ccc
543 d 56 4 asf_bbb_ccc_d
*/

#10

create proc vvv
as
set nocount on
declare @level int
declare @path nvarchar(300)
declare @first int
declare @tb table(tid int,lev int,path nvarchar(300))
set @level = 1
set @path = ''
select @first=item_id,@path = item_de from t where parent_id = 0
insert @tb select @first,@level,@path
--select * from  @tb
while @@rowcount > 0
begin
set @level = @level +1
insert @tb
select t.item_id,@level,cast(tb.path as nvarchar(30)) + '_'+ cast(t.item_de as nvarchar(30))
from t join @tb as tb
on t.parent_id = tb.tid
where tb.lev = @level -1
end
update t set level_seq = nt.lev, path = nt.path from @tb as nt where t.item_id = nt.tid

create trigger ttt on t
after insert,update
as
exec vvv
exec vvv

这段列?

#11

to:hhwufeng
你的可以了,谢谢!