--------------------------------------------------------
 
 --  ItemID		Код узла дерева
 
 --  ParentID	Родительский элемент
 
 --  Name		Наименование узла
 
 --------------------------------------------------------
 
CREATE TABLE dict.Dict (
    ItemID	INTEGER	NOT NULL,
    ParentID	INTEGER,
    Name	CHAR( 35 ),
    constraint  AtParent CHECK (ParentID<>ItemID),
    constraint  AtItemID PRIMARY KEY (ItemID)
)
@

CREATE UNIQUE INDEX dict.IDict ON dict.Dict (Name) ALLOW REVERSE SCANS
@
CREATE UNIQUE INDEX dict.IDictParent ON dict.Dict (ParentID,ItemID)
@

 ----------------------------------------------
 
 -- Запрещаем ссылку на несуществующие записи
 
 -- при обновлении ParentID
 
 ----------------------------------------------
 
CREATE TRIGGER dict.BUDictParent
NO CASCADE BEFORE UPDATE ON dict.Dict
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (
	(N.ParentID IS NOT NULL)
    AND ((SELECT P.ItemID FROM dict.Dict P WHERE P.ItemID=N.ParentID) IS NULL)
)	SIGNAL SQLSTATE '75011' ('Несуществующий ParentID')
@

 ----------------------------------------------
 
 -- Защита от рекурсивного зацикливания
 
 -- при обновлении ParentID
 
 ----------------------------------------------
 
CREATE TRIGGER dict.BUDict
NO CASCADE BEFORE UPDATE ON dict.Dict
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WITH Lvl (TopItemID,ItemID,ParentID)
AS (
	SELECT bottom.ItemID,bottom.ItemID,bottom.ParentID
		FROM dict.Dict AS bottom
	        WHERE bottom.ItemID=N.ParentID
	UNION ALL
	SELECT Lvl.TopItemID,parent.ItemID,parent.ParentID
    	FROM Lvl, dict.Dict AS parent
	        WHERE parent.ItemID=Lvl.ParentID
            	AND Lvl.ParentID<>Lvl.TopItemID
)
SELECT
    CASE 
    	WHEN Lvl.ParentID=N.ItemID THEN RAISE_ERROR('70021','Рекурсивное зацикливание')
        ELSE Lvl.ParentID
    END
FROM Lvl 
@

 ----------------------------------------------
 
 -- Запрещаем ссылку на несуществующие записи
 
 -- при добавлении новой записи
 
 -- Побочный эффект - не позволяет строить
 
 -- дерево в одной транзакции
 
 ----------------------------------------------
 
CREATE TRIGGER dict.BIDictParentID
NO CASCADE BEFORE INSERT ON dict.Dict
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (
	(N.ParentID IS NOT NULL)
    AND ((SELECT P.ItemID FROM dict.Dict P WHERE P.ItemID=N.ParentID) IS NULL)
) SIGNAL SQLSTATE '75011' ('Несуществующий ParentID')
@


 ----------------------------------------------
 
 -- Запрещаем удаление родительского узла
 
 ----------------------------------------------
 
CREATE TRIGGER dict.BDDict
NO CASCADE BEFORE DELETE ON dict.Dict
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (
(SELECT COUNT(*) FROM dict.Dict D
	WHERE D.ParentID=O.ItemID)> 0 
) SIGNAL SQLSTATE '75031' ('Существуют ссылки на узел')
@



insert into dict.Dict (ItemID,ParentID,Name) values ( 1 ,NULL,'root')			@
insert into dict.Dict (ItemID,ParentID,Name) values ( 2 , 1 ,'Два')				@
insert into dict.Dict (ItemID,ParentID,Name) values ( 3 , 1 ,'Три')				@
insert into dict.Dict (ItemID,ParentID,Name) values ( 4 , 1 ,'Четыре')			@
insert into dict.Dict (ItemID,ParentID,Name) values ( 5 , 2 ,'Пять')			@
insert into dict.Dict (ItemID,ParentID,Name) values ( 6 , 2 ,'Шесть')			@
insert into dict.Dict (ItemID,ParentID,Name) values ( 7 , 3 ,'Семь')			@
insert into dict.Dict (ItemID,ParentID,Name) values ( 8 , 3 ,'Восемь')			@
insert into dict.Dict (ItemID,ParentID,Name) values ( 9 , 3 ,'Девять')			@
insert into dict.Dict (ItemID,ParentID,Name) values ( 10 , 6 ,'Десять')			@
insert into dict.Dict (ItemID,ParentID,Name) values ( 11 , 6 ,'Одиннадцать')	@
insert into dict.Dict (ItemID,ParentID,Name) values ( 12 , 9 ,'Двенадцать')		@
insert into dict.Dict (ItemID,ParentID,Name) values ( 13 , 12 ,'Тринадцать')	@
insert into dict.Dict (ItemID,ParentID,Name) values ( 14 , 13 ,'Четырнадцать')	@
@

 -----------------------------------------------------
 
 --
 
 --  1--+--2---------+--5
 
 --     |            |
 
 --     +--3--+--7   +--6--+--10
 
 --     |     |            |
 
 --     +--4  +--8         +--11
 
 --           |
 
 --           +--9--12--13--14
 
 --
 
 --
 
 ----------------------------------------------------
 

select * from dict.Dict@

 --Выбор всех предков для опции
 
with Node (ItemId,ParentId,Name,Cnt)
as (
	select child.ItemId,child.ParentId,child.Name, 0 
	    from dict.Dict child
		    where child.ItemId= 13 

	union all
   		select parent.ItemId,parent.ParentId,parent.Name,node.Cnt+ 1 
        	from dict.Dict parent, node 
            where parent.ItemId=node.ParentID )

select * from Node order by Cnt desc
@

 --Определение уровня вложеннности для узла
 
with Lvl (Level,ParentID) 
as (
	select  0 ,ParentID
    	from dict.Dict bottom
        	where ItemID= 1 

    union all

	select Lvl.Level+ 1 ,parent.ParentID from dict.Dict parent,Lvl
    	where parent.ItemID=Lvl.ParentID )
select count(*) from Lvl
@