Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Таблица с иерархией, обновение иерархических данных в триггере  [new]
Тэй
Member

Откуда:
Сообщений: 79
Для примера приведу упрощенную таблицу сотрудников
CREATE TABLE #Employee (
	EmployeeId INT NOT NULL,
	ParentId INT NULL,
	EmployeeName NVARCHAR(250) NOT NULL,
	Level INT NULL,
	Path VARCHAR(2000) NULL,
	DisplayPath varchar(4000) NULL
)

INSERT INTO #Employee(EmployeeId, ParentId, EmployeeName)
VALUES(1, NULL, N'Иванов'),
	  (2, 1, N'Петров'),
	  (3, 1, N'Сидоров'),
	  (4, 2, N'Смирнов')

SELECT * FROM #Employee


Далее в триггере заполняются Level, Path, DisplayPath
level - уровень вложенности
Path - путь к узлу через id, например путь к (4, 2, N'Смирнов') будет выглядеть как \1\2\4
DisplayPath -- тот же путь только вместо id используются имена например Иванов\Петров\Смирнов

если ParentId или EmployeeName изменился, надо пересчитать эти три поля, причем не только у измененного элемента,
но и у всех его дочерних элементов.

Строго не судите, структура данных досталась в наследство.
Поэтому такие варианты как поменять на вьюху с испольованием cte, вычисляющую эти 3 поля, не представляется возможным, таблица очень много где использутся.

Есть ли возможность как-то улучшить триггер, то, что пока получилось, работает крайне медленно?

читала про тип hierarchyid, но не уверена, что его можно тут применить,
потому что, во-первых нет единого рутового элемента
во вторых я так понимаю, можно было б получать из столбца типа hierarchyid методом ToString Path, но как тогда получать DisplayPath

вот так сейчас выглядит код в триггере выполняющийся после апдейта

IF(UPDATE(ParentId) OR UPDATE(EmployeeName))
	BEGIN
		WITH RecursiveTable (EmployeeId, EmployeeName, level, ParentId, path, DisplayPath)
		AS
		(
			----Anchor
			SELECT  tt.EmployeeId, 
				tt.EmployeeName, 
				tt.level,
				tt.ParentId,
				CAST(LTRIM(STR(tt.EmployeeId)) + '\'  AS NVARCHAR(2048)) AS path,
				CAST((tt.EmployeeName + '\') AS NVARCHAR(4000)) as DisplayPath
			FROM #Employee AS tt
			WHERE tt.ParentId IS null
			UNION ALL
			--Recursive member definition
			SELECT  tt.EmployeeId, 
					tt.EmployeeName, 
					ISNULL(rt.level, 0) + 1,
					tt.ParentId,
					CAST(ISNULL(rt.path, '') +  CONVERT(nvarchar(2048), tt.EmployeeId) + '\' AS nvarchar(2048)) ,
					ISNULL(rt.DisplayPath, '') + tt.EmployeeName + '\'
			FROM #Employee AS tt
				INNER JOIN RecursiveTable rt ON
				tt.ParentId = rt.EmployeeId
		)
		UPDATE nw
		SET
			nw.level = rt.level,
			nw.path = rt.path,
			nw.DisplayPath = rt.DisplayPath
		FROM
			#Employee nw
		INNER JOIN 
			RecursiveTable rt ON nw.EmployeeId = rt.EmployeeId
	END


получается, что обновлять приходится все записи таблицы
есть ли какой то достоверный способ обновить только нужные записи?
7 сен 17, 19:51    [20779803]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Shakill
Member

Откуда: мск
Сообщений: 1870
Тэй
если ParentId или EmployeeName изменился, надо пересчитать эти три поля, причем не только у измененного элемента,
но и у всех его дочерних элементов.
...
получается, что обновлять приходится все записи таблицы
есть ли какой то достоверный способ обновить только нужные записи?


все обновлять не надо. выбирайте в CTE в качестве корневого (Anchor) элемента новый назначенный родительский элемент (тот, который parentId). тогда можно будет составлять пути вниз от него и обновлять только измененный и его дочерние элементы.
7 сен 17, 20:42    [20779872]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Тэй
Member

Откуда:
Сообщений: 79
Shakill,

я может не совсем понимаю идею, но допустим я обновила 3 строки. И поменяла ParentId в них. Скажем один из элементов стал подчиняться другому в иерархии. мне кажется такой фильтр будет не корректным
7 сен 17, 20:51    [20779887]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Shakill
Member

Откуда: мск
Сообщений: 1870
Тэй
Shakill,

я может не совсем понимаю идею, но допустим я обновила 3 строки. И поменяла ParentId в них. Скажем один из элементов стал подчиняться другому в иерархии. мне кажется такой фильтр будет не корректным


если у элемента изменился parentId или EmployeeName, то надо пересчитать пути и уровни только его и всех его дочерних элементов. те элементы, которые не менялись и стоят выше него по иерархии или вообще в другом дереве, пересчитывать не надо
7 сен 17, 20:59    [20779908]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Тэй
Member

Откуда:
Сообщений: 79
Shakill,
судя по тому что в вашем ответе все время стоит слово "его" а не "их" вы не совсем понимаете задачу.

еще раз, строк может обновиться несколько. они могут быть друг у друга подчиненными и их подчиненные узлы будут входить одно в другое. если знаете как правильно отфильтровать, пожалуйста, подскажите кодом. а не абстрактными фразами.
7 сен 17, 22:01    [20780002]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30800
Тэй
есть ли какой то достоверный способ обновить только нужные записи?
Для начала хотя бы в UPDATE поставить условие, обновлять только то, что изменилось
8 сен 17, 02:50    [20780353]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
автор
но как тогда получать DisplayPath

Вы же можете сложить путь в обычную колонку, как это сейчас делаете. hierarchyid используйте для навигации и обновления.
8 сен 17, 12:57    [20781561]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Возможно потребуется создать CLR функцию для замены слова в нужной позиции.
8 сен 17, 12:58    [20781565]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
dies irae
Member

Откуда:
Сообщений: 78
сначала отбираем строки, которые нужно пересчитать - это те, что в inserted + их потомки
потом для всех отобранных идём вверх по иерархии, считая путь
типа так (не проверял):

IF(UPDATE(ParentId) OR UPDATE(EmployeeName))
	BEGIN
		WITH TouchedEmployee as (
		-- кого нужно пересчитать: изменённые + все потомки
			select EmployeeId
			from inserted
			union all
			select EmployeeId
			from #Employee as e
				join TouchedEmployee as t on t.EmployeeId = e.ParentId
		)
		,EmployeeWithParents as (
		-- кого нужно пересчитать + все их родители
			select e.EmployeeId
				,e.ParentId
				,0 as level
				,CAST(LTRIM(STR(e.EmployeeId)) + '\'  AS NVARCHAR(2048)) AS path
				,CAST((e.EmployeeName + '\') AS NVARCHAR(4000)) as DisplayPath
			from #Employee as e join TouchedEmployee as t on t.EmployeeId = e.EmployeeId
			union all
			select e.EmployeeId
				,e.ParentId
				,p.level + 1
				,CAST(ISNULL(p.path, '') +  CONVERT(nvarchar(2048), e.EmployeeId) + '\' AS nvarchar(2048)) 
				,ISNULL(p.DisplayPath, '') + e.EmployeeName + '\'
			from #Employee as e
				join EmployeeWithParents as p on p.EmployeeId = e.ParentId
		)
		,EmployeeWithPath as (
			-- отбираем строки c максимальным уровнем
			select top(1) with ties
				EmployeeId, level, path, DisplayPath
			from EmployeeWithParents
			order by row_number() over (partition by EmployeeId order by level desc)
		)
		update e set level = n.level
			,path = n.path
			,DisplayPath = n.DisplayPath
		from #Employee as e
			join EmployeeWithPath as n on n.EmployeeId = e.EmployeeId
		where exists(
			select e.level, e.path, e.DisplayPath
			except
			select n.level, n.path, n.DisplayPath
			)
	END
8 сен 17, 13:54    [20781776]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Тэй
Member

Откуда:
Сообщений: 79
Владислав Колосов,

мне нравится предложенный вариант, пробую реализовать и сравнить скорость
8 сен 17, 14:10    [20781855]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Тэй
Member

Откуда:
Сообщений: 79
dies irae,

если в inserted есть зависимые друг от друга записи, то уже на первой же cte выпадает ошибка

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
8 сен 17, 14:30    [20781941]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
dies irae
Member

Откуда:
Сообщений: 78
а если так?

IF(UPDATE(ParentId) OR UPDATE(EmployeeName))
	BEGIN
		WITH TouchedEmployee as (
		-- кого нужно пересчитать: изменённые + все потомки
			select i.EmployeeId
			from inserted as i
			union all
			select EmployeeId
			from #Employee as e
				join TouchedEmployee as t on t.EmployeeId = e.ParentId
			where not exists(
					select * 
					from inserted as i 
					where i.EmployeeId = e.EmployeeId
				)
		)
		,EmployeeWithParents as (
		-- кого нужно пересчитать + все их родители
			select e.EmployeeId
				,e.ParentId
				,0 as level
				,CAST(LTRIM(STR(e.EmployeeId)) + '\'  AS NVARCHAR(2048)) AS path
				,CAST((e.EmployeeName + '\') AS NVARCHAR(4000)) as DisplayPath
			from #Employee as e join TouchedEmployee as t on t.EmployeeId = e.EmployeeId
			union all
			select e.EmployeeId
				,e.ParentId
				,p.level + 1
				,CAST(ISNULL(p.path, '') +  CONVERT(nvarchar(2048), e.EmployeeId) + '\' AS nvarchar(2048)) 
				,ISNULL(p.DisplayPath, '') + e.EmployeeName + '\'
			from #Employee as e
				join EmployeeWithParents as p on p.EmployeeId = e.ParentId
		)
		,EmployeeWithPath as (
			-- отбираем строки c максимальным уровнем
			select top(1) with ties
				EmployeeId, level, path, DisplayPath
			from EmployeeWithParents
			order by row_number() over (partition by EmployeeId order by level desc)
		)
		update e set level = n.level
			,path = n.path
			,DisplayPath = n.DisplayPath
		from #Employee as e
			join EmployeeWithPath as n on n.EmployeeId = e.EmployeeId
		where exists(
			select e.level, e.path, e.DisplayPath
			except
			select n.level, n.path, n.DisplayPath
			)
	END
8 сен 17, 14:50    [20782031]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Тэй
Member

Откуда:
Сообщений: 79
dies irae,

работает, но получилось даже дольше чем пересчитывать полностью всю таблицу(
8 сен 17, 17:29    [20782665]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
aleks222
Guest
Тэй
dies irae,

работает, но получилось даже дольше чем пересчитывать полностью всю таблицу(


Считать тоже надо уметь


CREATE TABLE #Employee (
    id int identity primary key, -- очень бы украсило наличие ID строки, иначе у вас полное фуфло
	EmployeeId INT NOT NULL,
	ParentId INT NULL,
	EmployeeName NVARCHAR(250) NOT NULL,
	Level INT NULL,
	Path VARCHAR(2000) NULL,
	DisplayPath varchar(4000) NULL
)

IF exists( select * from inserted as i inner join deleted as d on i.id = d.id  where exists( select i.ParentId, i.EmployeeName except select d.ParentId, d.EmployeeName )) begin

 WITH e as ( select * from #Employee )
    , i as ( select i.* 
	           from inserted as i inner join deleted as d on i.id = d.id where exists( select i.ParentId, i.EmployeeName except select d.ParentId, d.EmployeeName ) )
	, t as ( select i.id 
	              , i.EmployeeId
				  , i.EmployeeName
				  , level = isnull( e.level, 0 ) + 1
				  , i.ParentId
				  , Path = isnull( e.Path, N'' ) + cast( i.EmployeeId as nvarchar(16) ) + N'\'
				  , DisplayPath = isnull( e.DisplayPath, N'' ) + t.EmployeeName + N'\'
			   from i left outer join e on e.EmployeeId = i.ParentId
			 union all
			 select e.id 
				  , e.EmployeeId
				  , e.EmployeeName
				  , level = t.level + 1
				  , e.ParentId
				  , Path = t.Path + cast( e.EmployeeId as nvarchar(16) ) + N'\'
				  , DisplayPath = t.DisplayPath + e.EmployeeName + N'\'
			   from t inner join e on e.ParentId = t.EmployeeId
		)
	update e set level = t.level, path = t.path, DisplayPath = t.DisplayPath
	   from e inner join t on e.id = t.id
	END
8 сен 17, 18:31    [20782834]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3462
Владислав Колосов
автор
но как тогда получать DisplayPath

Вы же можете сложить путь в обычную колонку, как это сейчас делаете. hierarchyid используйте для навигации и обновления.
8 сен 17, 19:35    [20782921]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Тэй
Member

Откуда:
Сообщений: 79
aleks222,

спасибо! супер, все работает. pk конечно есть - это employeeId, ну не суть
8 сен 17, 20:46    [20782980]     Ответить | Цитировать Сообщить модератору
 Re: Таблица с иерархией, обновение иерархических данных в триггере  [new]
Тэй
Member

Откуда:
Сообщений: 79
Ролг Хупин,

в общем не пошел этот подход, надо было его делать изначально. а так все запросы уже работают через parent id, не будет особого выигрыша в выборках. а так вообще прикольно.
8 сен 17, 20:53    [20782988]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить