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

Откуда: Санкт-Петербург
Сообщений: 211
Здравствуйте

Как удалить паренты при удалении записи,
Точнее:
есть справочник id - значение, parentID - id от которого зависит значение (дерево)
id int not null
ParentID int null

пример
id, ParentID
1, NULL
2, 1
3, 2
4, 3
5, NULL
6, NULL

и есть таблица OL куда вводятся id справочника.
При удалении значения из OL надо удалить все значения, которые зависят от удаленного. И так в цикле надо удалить все - которые зависят от удаленных.
Т.е при удалении записи с id = 1 должны удалится записи 2, 3, 4.

Написал триггер
ALTER TRIGGER [dbo].[tbl_BR_OL_TypesOfTaxation_del] ON [dbo].[tbl_BR_OL_TypesOfTaxation] FOR DELETE
AS 
BEGIN
DECLARE @DelCount int
	SET NOCOUNT ON

	-- если после удаления элемента (который являлся парентом для оставшегося в опр.листе элемента), 
	-- то удаляем оставшийся элемент (для которого был удален парент)
	-- времянка для отбора тех - кого надо удалить, т.к. парент удален
	IF object_id('tempDB..#tmp') is NOT NULL DROP TABLE #tmp
	CREATE TABLE #tmp (id INT NOT NULL)
	-- времняка для тех, кого удалили
	IF object_id('tempDB..#D') is NOT NULL DROP TABLE #D
	CREATE TABLE #D (id INT NOT NULL)
	-- скидываем первых удаляемых
	INSERT INTO #D (id)
	SELECT ID 
	FROM DELETED

Repeat_:
	-- собираем тех, кого удалим, т.к. удален парент
	INSERT INTO #tmp (id)
	SELECT OL.ID
	FROM #D D
		INNER JOIN 
		tbl_BR_TypesOfTaxation AS T ON D.ID = T.ParentID
		INNER JOIN
OL ON T.ID = OL.ID
	-- удаляем тех, у кого удален парент
	DELETE FROM OL
	FROM tbl_BR_OL_TypesOfTaxation AS OL
	INNER JOIN #tmp T ON T.id = OL.id
	SET @DelCount = @@ROWCOUNT 
	-- сохраняем тех, кого удалали
	INSERT INTO #D (id)
	SELECT T.ID
	FROM #tmp T
	-- если хоть кого-то удалили - повторяем цикл
	IF @DelCount > 0 GOTO Repeat_
	-- убираем времянки
	IF object_id('tempDB..#tmp') is NOT NULL DROP TABLE #tmp
	IF object_id('tempDB..#D') is NOT NULL DROP TABLE #D
	
END
16 ноя 18, 12:13    [21736175]     Ответить | Цитировать Сообщить модератору
 Re: Удаление веток при удалении ствола в триггере  [new]
NVT
Member

Откуда: Санкт-Петербург
Сообщений: 211
сорри, отправил не доделав.

продолжаю: написал триггер
ALTER TRIGGER [dbo].[OL_del] ON [dbo].[OL] FOR DELETE
AS 
BEGIN
DECLARE @DelCount int
	SET NOCOUNT ON

	-- если после удаления элемента (который являлся парентом для оставшегося в опр.листе элемента), 
	-- то удаляем оставшийся элемент (для которого был удален парент)
	-- времянка для отбора тех - кого надо удалить, т.к. парент удален
	IF object_id('tempDB..#tmp') is NOT NULL DROP TABLE #tmp
	CREATE TABLE #tmp (id INT NOT NULL)
	-- времняка для тех, кого удалили
	IF object_id('tempDB..#D') is NOT NULL DROP TABLE #D
	CREATE TABLE #D (id INT NOT NULL)
	-- скидываем первых удаляемых
	INSERT INTO #D (id)
	SELECT ID 
	FROM DELETED

Repeat_:	
	-- собираем тех, кого удалим, т.к. удален парент
	INSERT INTO #tmp (id)
	SELECT OL.ID
	FROM #D D
		INNER JOIN 
		Справочник AS T ON D.ID = T.ParentID
		INNER JOIN
		OL ON T.ID = OL.ID
	-- удаляем тех, у кого удален парент
	DELETE FROM OL
	FROM OL
	INNER JOIN #tmp T ON T.id = OL.id
	SET @DelCount = @@ROWCOUNT 
	-- сохраняем тех, кого удалали
	INSERT INTO #D (id)
	SELECT T.ID
	FROM #tmp T
	-- если хоть кого-то удалили - повторяем цикл
	IF @DelCount > 0 GOTO Repeat_
	-- убираем времянки
	IF object_id('tempDB..#tmp') is NOT NULL DROP TABLE #tmp
	IF object_id('tempDB..#D') is NOT NULL DROP TABLE #D
	
END


Но чувствую, что не оптимально. Может быть есть более оптимальный способ?

PS: В предыдущем посте исходник не смотреть. Прошу администратора удалить исходник из моего поста выше
16 ноя 18, 12:21    [21736186]     Ответить | Цитировать Сообщить модератору
 Re: Удаление веток при удалении ствола в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9127
alter trigger [dbo].[OL_del]
on [dbo].[OL]
instead of delete
as 
begin
 set nocount on;

 create table #t (id int primary key);

 with t as
 (
  select
   id
  from
   deleted

  union all

  select
   ol.id
  from
   t join
   [dbo].[OL] ol on ol.parent_id = t.id
 )
 insert into #t
  (id)
  select distinct id from t;

 delete ol
 from
  #t t join
  [dbo].[OL] ol on ol.id = t.id;
end;
16 ноя 18, 12:41    [21736220]     Ответить | Цитировать Сообщить модератору
 Re: Удаление веток при удалении ствола в триггере  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
CREATE TABLE T(id INT, ParentID INT);
INSERT T(id,ParentID) VALUES
 (1, NULL)
,(2, 1)
,(3, 2)
,(4, 3)
,(5, NULL)
,(6, NULL);

GO
CREATE TRIGGER tdT ON T FOR DELETE AS
WITH CTE(id,ParentID) AS
(
 SELECT T.id,T.ParentID FROM T JOIN deleted D ON T.ParentID=D.id
 UNION ALL
 SELECT T.id,T.ParentID FROM T JOIN CTE ON T.ParentID=CTE.id
)
DELETE T WHERE id IN(SELECT id FROM CTE);
GO

SELECT * FROM T;
DELETE T WHERE id=1;
SELECT * FROM T;
16 ноя 18, 12:49    [21736238]     Ответить | Цитировать Сообщить модератору
 Re: Удаление веток при удалении ствола в триггере  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
Однако, если между узлами дерева существуют связи в виде FOREIGN KEY (по-хорошему-то так и должно быть),
то надо создавать триггер INSTEAD OF DELETE.
Ибо удаление одного корневого узла, запускающего триггер, будет невозможно из-за ссылок из потомков.
16 ноя 18, 12:53    [21736255]     Ответить | Цитировать Сообщить модератору
 Re: Удаление веток при удалении ствола в триггере  [new]
Владислав Колосов
Member

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

если у вас массовые удаления, то можно смотреть в сторону hierarchyid типа вместо рекурсии.
16 ноя 18, 13:14    [21736296]     Ответить | Цитировать Сообщить модератору
 Re: Удаление веток при удалении ствола в триггере  [new]
NVT
Member

Откуда: Санкт-Петербург
Сообщений: 211
iap,
я, видимо, ввел вас в заблуждение названием топика.

Удалять надо из таблицы OL (там только поле ID, которое подставляется из таблицы "справочник".ID),
а подчинение (ID, parentID) устанавливается в таблице "справочник".

PS:
SELECT @@version
Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
16 ноя 18, 13:15    [21736299]     Ответить | Цитировать Сообщить модератору
 Re: Удаление веток при удалении ствола в триггере  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
NVT
iap,
я, видимо, ввел вас в заблуждение названием топика.

Удалять надо из таблицы OL (там только поле ID, которое подставляется из таблицы "справочник".ID),
а подчинение (ID, parentID) устанавливается в таблице "справочник".
Что это принципиально меняет?
16 ноя 18, 13:19    [21736307]     Ответить | Цитировать Сообщить модератору
 Re: Удаление веток при удалении ствола в триггере  [new]
NVT
Member

Откуда: Санкт-Петербург
Сообщений: 211
iap, точно! Спасибо. Все понял.
Просто моск под конец недели уже не пашет.

И ведь я уже делал такое ...
еще раз спасибо!
16 ноя 18, 13:25    [21736319]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить