Как при помощи триггеров удалить записи в связанных таблицах в ms sql server?

Question

[Александр Волков]

Здравствуйте.
Возникли вопросы при работе с БД. В БД есть три связанные таблицы, они видны на ER диаграмме.

Я хочу чтобы при удалении записи в таблице s_test удалялась связанная с ней информация в таблицах test_task и s_task. Для этого я использую триггеры.
При удалении записи в s_test срабатывает следующий триггер:

ALTER TRIGGER [dbo].[t_del_test]
ON [dbo].[s_test] INSTEAD OF DELETE
AS
DECLARE @id int;
SELECT @id = id_test from deleted;
DELETE FROM test_task WHERE id_test = @id;
DELETE FROM s_test WHERE id_test=@id;

Он отрабатывает корректно и удаляет все связанные записи в таблице test_task.
Однако в дальнейшем возникают проблемы. При использовании в test_task следующего триггера

ALTER TRIGGER [dbo].[t_del_test_task]
ON [dbo].[test_task] AFTER DELETE
AS
DECLARE @id int;
SELECT @id = id_task from deleted;
DELETE FROM s_task WHERE id_task=@id;

удаляется только одна запись в s_test, я понимаю почему это происходит, но не знаю как сделать правильно. Т.е. чтобы в s_task удалялись все записи.
Прошу указать способ решения если такой существует, или обозначить направление поисков.

Прошу прощения за большое описание и заранее благодарю за помощь.

Answer 1

[Алексей]

Вот любим мы всякие извращения...
Вы, про каскадное удаление слышали?
Пример:

ALTER TABLE [dbo].[OrderGoods]  WITH CHECK ADD  CONSTRAINT [FK_OrderGoods_OrderHeader] FOREIGN KEY([OrderHeaderGUID])
REFERENCES [dbo].[OrderHeader] ([GUID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[OrderGoods] CHECK CONSTRAINT [FK_OrderGoods_OrderHeader]
GO

В коде нужно обратить внимание на ON DELETE CASCADE

То есть при удалении заголовка, удалятся и данные тушки.
Если совсем начинающий:
Правый клик - отношения.
И дальше как на картинке.


И не советую использовать триггеры - они сложно отлаживаются и если вдруг в нем будет ошибка вы просто потеряете часть данных при работе с таблицей.

Answer 2

[d-stream]

Про то, как без триггеров - выше уже сказали, а если-таки что-то потребуется в триггере, то следует иметь в виду, что сущности inserted, deleted - это таблицы с возможно более чем одной записью.

Соответственно по ним надо проходится либо курсором (классика со своими подводными камнями), циклом (тоже есть свои нюансы), либо join-ом (не всегда возможно).

По сути канва:

ALTER TRIGGER [dbo].[t_del_test]
ON [dbo].[s_test] INSTEAD OF DELETE
AS
DECLARE @id int; -- сюда будем гадить для каждой строки

declare cur_for_delete cursor for select id_test from deleted; -- объявляем курсор 
open cur_for_delete; -- открываем
fetch cur_for_delete into  @id;   -- получаем первую строку
while @@fetch_status=0 begin -- пока есть что получать
            DELETE FROM test_task WHERE id_test = @id;
            DELETE FROM s_test WHERE id_test=@id;
            fetch cur_for_delete into  @id; -- тянем следующую строку из курсора
end
close cur_for_delete; -- не забываем закрыть
deallocate cur_for_delete

по второму триггеру - аналогично

Answer 3

[Дмитрий Ковальский]

Самый простой в разработке и поддержке способ - написать хранимую процедуру, которая последовательно будет чистить ваши таблицы.
Самый быстрый, с точки зрения производительности, - вместо удаления проставить флаг isDeleted у нужной сущности, а затем в периоды низкой нагрузки на базу - руками почистить удаляя записи по данному флагу.