Уважаемые товарищи гуру!
Может кто сталкивался?
Типичная древовидная структура child-parent, нужно написать триггер на UPDATE, который исключал бы возможность перемещения ветви дерева в саму себя (или в своего потомка). Процедура MakeTree строит список всех потомков данного узла и проверяет присутствует ли новое значение родителя для перемещаемого узла в его потомках и возвращает единицу, если да. Далее строится сам триггер на UPDATE (для простоты разрешаем изменять только одну запись в один момент времени, то есть inserted должен иметь не более одной записи). Вроде все прозрачно, процедура MakeTree, будучи вызванной отдельно, по-честному строит дерево и возвращает правильный результат. А вот в случае, когда она вызывается из триггера, она наглухо зацикливается по совершенно непонятной причине на цикле while @level>0.
Однако, если сделать триггер INSTEAD OF вместо FOR UPDATE, то все нормально. Можете пояснить, в чем тут дело?
Запустите прилагаемый скрипт, все должно зациклиться


CREATE TABLE [ActPack_1] (
[Id_ActPack] [INT] IDENTITY (1, 1) NOT NULL ,
[Id_Parent] [INT] NOT NULL ,
[Num] [int] NULL ,
[Name] Char(10) NOT NULL )
GO
-- формируем простое деревце
Insert Into ActPacK_1 (id_parent,name) values (0,'a')
GO
Insert Into ActPacK_1 (id_parent,name) values (1,'b')
GO
Insert Into ActPacK_1 (id_parent,name) values (2,'c')
GO

--Процедура,
CREATE PROCEDURE MakeTree @current int, @newid int ,@ret bit output as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (id int, level int)
CREATE TABLE #result (id int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1


WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = id
FROM #stack
WHERE level = @level
INSERT #result VALUES (@current)
DELETE FROM #stack
WHERE level = @level
AND id = @current
INSERT #stack
SELECT id_actpack, @level + 1
FROM actpack_1
WHERE id_parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE
if exists (SELECT id FROM #result where id in (@newid))
set @ret=1
else set @ret=0


--Триггер
go
CREATE TRIGGER TUB_ActPack ON ActPack_1
FOR UPDATE AS

DECLARE @ID_CUR INT
DECLARE @NEWPARENT INT
DECLARE @NCOUNT INT
SET NOCOUNT ON

if @@ROWCOUNT>1 begin
RAISERROR ('ИЗМЕНЯТЬ НЕ БОЛЬШЕ ОДНОЙ ЗАПИСИ',1,11)
ROLLBACK TRAN
RETURN
end

if update(id_parent)
begin
select @id_cur=i.id_actPack from deleted i --ID изменяемой записи
select @newparent=i.id_parent from inserted i --ID нового родителя
exec maketree @id_cur, @newparent , @ncount
if @ncount = 1
begin
RAISERROR ('НЕЛЬЗЯ ПЕРЕМЕЩАТЬ В САМОГО СЕБЯ',1,11)
ROLLBACK TRAN
RETURN
end
end

commit
go
--Теперь, собственно, команда, котрая все зацикливает
UPDATE actPack_1 SET id_parent = 3 WHERE id_actPack=1

для Pavel:
Попробовали твой вариант. Недопустимые апдейты он отсекает, все правильно, в себя скопировать не дает. Но при попытке переместить узел в соседнюю ветку, то есть, произвести допустимую операцию, триггер опять зациклился.
конструкция Select @Parent_category_id = Parent_category_id From Categories Where Category_id = @Parent_category_id почему-то никогда не устанавливает @parent_category_id в NULL.
Похоже, что проблема все-таки не в том, как мы работаем с деревьями, а где-то в другом месте.
Другие варианты деревьев нам не подходят, это мы все уже читали.

Alexeyvg:
Читай внимательно первый постинг, я писал, что зацикливается не триггер, а процедура, и именно на WHILE, и именно при вызове ее из триггера, сама по себе она работает нормально, там правильная логика.

Pavel:
Твой вариант таки заработал, но только после того, как мы после Select @Parent_category_id = Parent_category_id From Categories Where Category_id = @Parent_category_id поставили if @@rowcount=0 break. То есть, насколько я понял, @parent_category_id не устанавливается в NULL, если где-то до этого ей было присвоено какое-то значение.
То есть, проблема вроде решена, но почему зацикливался наш вариант, не понятно.

for SySop
Вот-Вот

2Alexeyvg:
Исправление указанной тобой ошибки (добавление OUTPUT) к исправлению ситуации не приводит, все равно зацикливается.
Однако, благодаря тебе я разгадал загадку! Только что вдруг осенило. Глянул на твой скрипт и все понял. Триггера нет, но по-прежнему есть UPDATE. То есть, изменение, которое должен откатить триггер, на момент вего выполнения УЖЕ ВНЕСЕНО в таблицу ActPack, и процедура MakeTree пытается построить дерево, опираясь уже на неправильные данные, где корень дерева скопирован сам в себя, вот и цикл!!! Ведь это AFTER UPDATE триггер - сначала меняет данные, потом проверяет, и в случае неправильности - откатывает. Вот почему при INSTEAD OF все работало - изменения в таблицу ActPack не вносились!!!
Век живи, век учись. Всем спасибо, все понятно. :)