How to delete records in related tables using triggers in ms sql server?

Question

SQL Server

Hello.
There were questions when working with the database. There are three related tables in the database, they are visible in the ER diagram.

I want that when an entry in the s_test table is deleted, the information associated with it in the test_task and s_task tables is deleted. For this I use triggers.
When an entry in s_test is deleted, the following trigger fires:

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 [email protected];

It works correctly and deletes all related records in the test_task table.
However, further problems arise. When using the following trigger in 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 [email protected];

only one entry in s_test is deleted, I understand why this happens, but I don’t know how to do it right. Those. so that all entries in s_task are deleted.
I ask you to indicate a solution method if there is one, or indicate the direction of the search.
Sorry for the long description and thanks in advance for your help.

Answer 1

Here we love all sorts of perversions ...
Have you heard about cascade deletion?
Example:

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

In the code, you need to pay attention to ON DELETE CASCADE
That is, when the header is deleted, the carcass data will also be deleted.
If you are a complete beginner:
Right click - relations.
And then as in the picture.
And I do not advise using triggers - they are difficult to debug and if suddenly there is an error in it, you will simply lose part of the data when working with the table.

Answer 2

The easiest way to develop and maintain is to write a stored procedure that cleans up your tables sequentially.
The fastest, in terms of performance, is to put the isDeleted flag on the required entity instead of deleting it, and then, during periods of low load on the database, manually clean it up by deleting records by this flag.