Пользовательская хранимая процедура каскадного удаления для временных таблиц в SQL Server
Предисловие:
Я хочу создать хранимую процедуру в SQL Server (2016 – v13.0) для ручного выполнения каскадного удаления таблиц с ограничениями внешнего ключа, включая таблицы, ссылающиеся на себя. Мне нужна специальная реализация из-за использования темпоральных таблиц, которые не поддерживают каскадное удаление. Меня это ограничение очень расстраивает, особенно учитывая, что темпоральные таблицы предназначены для отслеживания удалений. Чтобы удалить строку, необходимо сначала удалить все строки, которые от нее зависят.
Я должен внести ясность: я не хочу трогать данные в таблицах истории, меня интересует только удаление строк в текущих таблицах.
Мои знания SQL не очень глубоки, и я не уверен, что это лучший подход. Я рассматривал возможность использования обратимого удаления, но мне оно не нравится из-за беспорядка, который оно вносит, и необходимости тщательно поддерживать точность данных. Я слышал о других, предлагающих хранимую процедуру для явного выполнения удаления, но в моем сценарии у меня слишком много таблиц, чтобы реализовать процедуру удаления для каждой в отдельности; вместо этого мне нужен динамический, который будет работать для любой строки таблицы.
Цель:
Хранимая процедура должна:
- Примите три параметра:
@SchemaName(имя схемы),@TableName(имя таблицы) и@Id(первичный ключ удаляемой сущности). - Удалите указанную строку и все строки, которые от нее зависят, соблюдая все ограничения внешнего ключа.
- Правильно обрабатывайте самоссылающиеся таблицы, удаляя дочерние строки перед родительскими. Возможно, именно здесь все выходит на страшную территорию.
- Работайте в рамках одной транзакции.
Пример сценария:
Рассмотрим это гиперупрощенное представление базы данных, с которой я работаю.
Ожидания:
Учитывая эту иерархию, если я попытаюсь удалить Folder, процедура должна рекурсивно удалить все зависимые ElementInstances, Elements, Pages, Forms и дочерние элементы Folders (в указанном порядке).
Вопросы:
- Возможно ли реализовать хранимую процедуру для обработки такого рода операций динамического каскадного удаления для темпоральных таблиц?
- Как я могу эффективно обрабатывать рекурсивные удаления, особенно в самореферентных таблицах, не сталкиваясь с проблемами глубины рекурсии?
Любые рекомендации или примеры будут с благодарностью!
... так что... я не уверен, что вижу здесь какую-либо рекурсию, о которой стоит беспокоиться...
Делать это динамически по вашему запросу будет кошмаром. SQL не очень хорошо подходит для динамических операций, он предназначен для статики. Если бы это был я, я бы написал SP для удаления для каждой таблицы, из которой можно удалить строку, которая должна следовать этому подходу - это обычный способ сделать это. Можно было бы написать SP с динамическим SQL, который просматривал бы существующие отношения и т. д. и т. п., но это была бы огромная работа, в которой мы не можем вам помочь, и кошмар для тестирования и поддержки, и я бы предложил, чтобы это была огромная работа. риск для вашей организации из-за возможности его поломки.
Любую рекурсию можно записать в виде цикла, только это будет сложнее. Хотя я уверен, что кто-то это уже написал, просто посмотрите вокруг. Но есть ли у вас идея удалить FK? Или просто изменить, чтобы они не каскадировались?
@topsail Идея заключалась в том, чтобы предположить, что все они временны. Или, проще говоря, предположить, что ON CASCADE DELETE не работает (как и ограничение темпоральных таблиц). Проблема с удалениями в этом случае заключается в том, что их нелегко выполнить из-за этого ограничения. Я просто пытаюсь имитировать поведение каскадного удаления. Моя реальная база данных содержит примерно 10 темпоральных таблиц, и я хотел избежать написания процедуры удаления для каждой.
@DaleK Спасибо, это тот совет, который я искал. Если это противоречит сути, я не буду это реализовывать. Что касается написания SP для каждой таблицы... в моем реальном сценарии у меня есть, возможно, 10 временных таблиц, которые следуют таким отношениям, как A имеет B, B имеет C, C имеет D и так далее. В таблице A SP удаления я предполагаю, что мне нужно будет выполнить удаление для D, C, а затем B, верно? Для B мне пришлось бы удалить D и C. Это просто кажется очень излишним... Звучит как хороший случай для триггера, но он также не работает для темпоральных таблиц.
Триггеры прекрасно работают с временными таблицами. Вам просто нужно использовать триггер AFTER. На самом деле это указано в документации, на которую вы ссылаетесь в своем вопросе. В вашем примере я бы создал для каждого уровня вместо того, чтобы пытаться сделать все за один.
@DaleK Мой мозг запутался, я понимаю, что могу просто последовательно вызвать другую хранимую процедуру. В конечном итоге получается довольно чистое решение.
@SeanLange Это не сработает, если у меня есть ограничения FK… верно? Например, если я удалю Form, триггер AFTER сработает после попытки удаления, что приведет к ошибке ограничения FK. Если бы INSTEAD OF работал здесь, это не было бы проблемой. Если только я не задумаюсь.
Это можно сделать, но это довольно болезненно. Если вы опубликуете образцы таблиц в виде операторов создания, я смогу что-нибудь придумать.
@SeanLange Я снова попробовал триггеры, и это действительно было очень больно. Я выбрал отдельные процедуры удаления для каждой таблицы и опубликовал ответ.
Ответы 1
Я решил написать процедуру удаления для каждой таблицы. Ниже приведен пример приложения, которое я создаю, и он не соответствует 1:1 примеру, приведенному в моем ОП, но следует тем же принципам.
Опуская для краткости многие таблицы, предположим, что все они временные.
Ниже приведена процедура удаления объекта верхнего уровня, имеющего ссылку на самого себя. Сначала он найдет все свои дочерние папки и выполнит для них процедуру DeleteFolder, чтобы начать своего рода рекурсивный процесс, а затем удалит принадлежащие им формы с помощью процедуры DeleteForm.
DeleteFolder.sql
CREATE PROCEDURE [dbo].[DeleteFolder]
@FolderId INT
AS
BEGIN
SET NOCOUNT ON;
-- Delete child folders
DECLARE @ChildFolderId INT;
DECLARE ChildFoldersCursor CURSOR FOR
SELECT [Id] FROM [ui].[Folders] WHERE [ParentId] = @FolderId;
OPEN ChildFoldersCursor;
FETCH NEXT FROM ChildFoldersCursor INTO @ChildFolderId;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [dbo].[DeleteFolder] @ChildFolderId;
FETCH NEXT FROM ChildFoldersCursor INTO @ChildFolderId;
END
CLOSE ChildFoldersCursor;
DEALLOCATE ChildFoldersCursor;
-- Delete related forms
DECLARE @FormId INT;
DECLARE FormsCursor CURSOR FOR
SELECT [Id] FROM [config].[Forms] WHERE [FolderId] = @FolderId;
OPEN FormsCursor;
FETCH NEXT FROM FormsCursor INTO @FormId;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [dbo].[DeleteForm] @FormId;
FETCH NEXT FROM FormsCursor INTO @FormId;
END
CLOSE FormsCursor;
DEALLOCATE FormsCursor;
-- Delete the folder
DELETE FROM [ui].[Folders] WHERE [Id] = @FolderId;
END
Вот процедура, которая вызывается в предыдущей. В нем подробно описано, как удалить объекты, зависящие от данной формы. Опять же, он продолжает использовать хранимые процедуры для удалений вместо явного удаления строк.
DeleteForm.sql
CREATE PROCEDURE [dbo].[DeleteForm]
@FormId INT
AS
BEGIN
SET NOCOUNT ON;
-- Delete related pages
DECLARE @PageId INT;
DECLARE PagesCursor CURSOR FOR
SELECT [Id] FROM [ui].[Pages] WHERE [FormId] = @FormId;
OPEN PagesCursor;
FETCH NEXT FROM PagesCursor INTO @PageId;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [dbo].[DeletePage] @PageId;
FETCH NEXT FROM PagesCursor INTO @PageId;
END
CLOSE PagesCursor;
DEALLOCATE PagesCursor;
-- Delete related lookup lists
DECLARE @LookupListId INT;
DECLARE LookupListsCursor CURSOR FOR
SELECT [Id] FROM [config].[LookupLists] WHERE [FormId] = @FormId;
OPEN LookupListsCursor;
FETCH NEXT FROM LookupListsCursor INTO @LookupListId;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [dbo].[DeleteLookupList] @LookupListId;
FETCH NEXT FROM LookupListsCursor INTO @LookupListId;
END
CLOSE LookupListsCursor;
DEALLOCATE LookupListsCursor;
-- Delete the related Elements
DECLARE @ElementId INT;
DECLARE ElementsCursor CURSOR FOR
SELECT [Id] FROM [config].[Elements] WHERE [FormId] = @FormId;
OPEN ElementsCursor;
FETCH NEXT FROM ElementsCursor INTO @ElementId;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [dbo].[DeleteElement] @ElementId;
FETCH NEXT FROM ElementsCursor INTO @ElementId;
END
CLOSE ElementsCursor;
DEALLOCATE ElementsCursor;
-- Delete the form
DELETE FROM [config].[Forms] WHERE [Id] = @FormId;
END
В конце концов, мы дошли до того, что удаляем листы, которые ни от чего не зависят, и, таким образом, мы эмулируем ON DELETE CASCADE с немного большей гибкостью и (к сожалению) гораздо большим количеством кода.
Ваше «представление базы данных» показывает несколько таблиц, но не говорит, какая из них является темпоральной таблицей... или вы имеете в виду, что все они являются темпоральными таблицами? Кроме того, поскольку темпоральные таблицы отслеживают удаления (в таблицах истории), а вы говорите, что не хотите трогать таблицы истории, неясно, в чем здесь проблема с удалениями. Но, вообще говоря, если вы «выполняете собственное каскадное удаление», вы работаете снизу вверх - все экземпляры ElementInstance, связанные с любым элементом или страницей, связанной с любой формой, связанной с удаляемой вами папкой, затем элементы и страницы, затем формы. ...