ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Удаление веток при удалении ствола в триггере
9 сообщений из 9, страница 1 из 1
Удаление веток при удалении ствола в триггере
    #39734001
NVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NVT
Участник
Здравствуйте

Как удалить паренты при удалении записи,
Точнее:
есть справочник 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.

Написал триггер
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
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
...
Рейтинг: 0 / 0
16.11.2018, 12:13
| Ответить | Цитировать | Написать  
Удаление веток при удалении ствола в триггере
    #39734010
NVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NVT
Участник
сорри, отправил не доделав.

продолжаю: написал триггер
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
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: В предыдущем посте исходник не смотреть. Прошу администратора удалить исходник из моего поста выше
...
Рейтинг: 0 / 0
16.11.2018, 12:21
| Ответить | Цитировать | Написать  
Удаление веток при удалении ствола в триггере
    #39734028
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
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;
...
Рейтинг: 0 / 0
16.11.2018, 12:41
| Ответить | Цитировать | Написать  
Удаление веток при удалении ствола в триггере
    #39734044
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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;
...
Рейтинг: 0 / 0
16.11.2018, 12:49
| Ответить | Цитировать | Написать  
Удаление веток при удалении ствола в триггере
    #39734057
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Однако, если между узлами дерева существуют связи в виде FOREIGN KEY (по-хорошему-то так и должно быть),
то надо создавать триггер INSTEAD OF DELETE.
Ибо удаление одного корневого узла, запускающего триггер, будет невозможно из-за ссылок из потомков.
...
Рейтинг: 0 / 0
16.11.2018, 12:53
| Ответить | Цитировать | Написать  
Удаление веток при удалении ствола в триггере
    #39734076
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
NVT,

если у вас массовые удаления, то можно смотреть в сторону hierarchyid типа вместо рекурсии.
...
Рейтинг: 0 / 0
16.11.2018, 13:14
| Ответить | Цитировать | Написать  
Удаление веток при удалении ствола в триггере
    #39734078
NVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NVT
Участник
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: )
...
Рейтинг: 0 / 0
16.11.2018, 13:15
| Ответить | Цитировать | Написать  
Удаление веток при удалении ствола в триггере
    #39734082
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
NVTiap,
я, видимо, ввел вас в заблуждение названием топика.

Удалять надо из таблицы OL (там только поле ID, которое подставляется из таблицы "справочник".ID),
а подчинение (ID, parentID) устанавливается в таблице "справочник".Что это принципиально меняет?
...
Рейтинг: 0 / 0
16.11.2018, 13:19
| Ответить | Цитировать | Написать  
Удаление веток при удалении ствола в триггере
    #39734086
NVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NVT
Участник
iap, точно! Спасибо. Все понял.
Просто моск под конец недели уже не пашет.

И ведь я уже делал такое ...
еще раз спасибо!
...
Рейтинг: 0 / 0
16.11.2018, 13:25
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Удаление веток при удалении ствола в триггере
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]