--1 create table
drop table FullPathTable
go
create table FullPathTable(
PathID integer identity,
PathName varchar(50),
ParentID integer,
InternalOrder integer,
FullPathID varchar(50))
--2 create trigger to FullPathTable
create trigger tr_FullPath
on FullPathTable
after insert
as
begin
declare @internalOrder integer, @str1 varchar(10), @parentFullPathID varchar(100),@FullPathID varchar(100)
,@parentID integer, @PathID integer
select @PathID = @@identity
select @parentID = ParentID from inserted
if(@parentID = -1
or (@parentID <> -1 and exists(select * from FullPathTable where PathID = @parentID and @parentID != @PathID))
)
begin
select @parentFullPathID = FullPathID from FullPathTable where PathID = @parentID
select @internalOrder = isnull(max(InternalOrder),0) from FullPathTable where ParentID = @parentID
set @internalOrder = @internalOrder + 1
set @str1 = '00000'
set @FullPathID = substring(@str1,1,len(@str1)-len(Convert(varchar,@internalOrder))) + Convert(varchar,@internalOrder)
if(@parentFullPathID <> '')
set @FullPathID = @parentFullPathID + '|' + @FullPathID
update FullPathTable
set FullPathID = @FullPathID, InternalOrder = @internalOrder
where PathID = @PathID
end
end
--3 insert data
insert into FullPathTable(PathName,ParentID) values('第3级1',5)
--4 query data
select * from FullPathTable
1 第一级1 -1 1 00001
2 第一级2 -1 2 00002
3 第一级3 -1 3 00003
4 第2级1 2 1 00002|00001
5 第2级2 2 2 00002|00002
6 第3级1 5 1 00002|00002|00001
select * from FullPathTable where FullPathID like '00002|%'
4 第2级1 2 1 00002|00001
5 第2级2 2 2 00002|00002
6 第3级1 5 1 00002|00002|00001
--5 主要作用,减少循环递归,用于多级数据结构,层次深度不定的情况