SQL Server: combination of Cascade deletes and Triggers might not work in the order you expect them to and how to work around it

Now that’s a long title.

I was working on a database with multiple levels of children, all stemming from one root table. The top few levels look something like the diagram below.

Now when any record is being deleted, all of it’s children have to go as well.

If a record in the parent table is deleted, all the records referencing it in both FirstChild and SecondChild tables need to go, and all the records in FirstChildsChild referencing FirstChild need to go as well.

Seems like a cascade delete would be a perfect and straight forward solution, at least in this case. So, lets say that we do have our cascade deletes on the Foreign Keys for now.
Deleting a child with multiple parents is a separate issue, but also fixable with the solution I am about to talk about.

Another thing that needs to be in this database is logging. Every INSERT, DELETE and UPDATE needs to be logged. What do we do?

Triggers.

CREATE TRIGGER TR_On_Parent_Delete ON [Parent]
AFTER DELETE, INSERT, UPDATE
AS -- pass the tables to some procedure processing the logging
EXEC Your_logging_proc [inserted], [deleted]

And same for all of its children, right?

Well, that would be too easy. Thing is, that the trigger is not called when you expect it to be called. At least not how I expected it, which is something like this:

  1. Call delete on Parent
  2. Delete is called on both children, before the parent is deleted
  3. Delete is called on grandchildren, before the children are deleted
  4. Grandchildren are deleted, executing the trigger
  5. Children are deleted, executing the trigger
  6. Parent is deleted, executing the trigger.

But in reality, the cascade deletes happen first and only then do the triggers execute, but the [inserted] and [deleted] tables are overwritten and not accessible for logging anymore.

So what to do?

Get rid of the cascade deletes and the FOR or AFTER triggers and put everything in INSTEAD OF triggers. This way you can do all the operations in the exact order you want to do it.

This is how I did it.

CREATE TRIGGER TR_Instead_Of_Delete_Parent ON Parent
INSTEAD OF DELETE
AS
    -- note: in some SQL languages # means a comment, in TSQL it's 
-- a prefix for a temporary table
SELECT * INTO #DeletedParent -- save the values in a temp table
FROM [deleted]
-- have a variable to hold values from each row
DECLARE @RowValues TABLE (Id INT, Name VARCHAR(10))
    -- iterate through all the deleted records
WHILE (EXIST(SELECT TOP 1 * FROM #DeletedParent))
BEGIN
-- extract the values you need from the temp table
SET @RowValues = SELECT TOP 1 [Id], [Name]
FROM #DeletedParent

--and log them
EXEC Your_logging_proc @RowValues
        -- extract the Id to use in children deletion
DECLARE @id INT
SELECT TOP 1 @id = Id FROM #DeletedParent
        -- Delete the children
DELETE FROM FirstChild WHERE ParentId = @id
DELETE FROM SecondChild WHERE ParentId = @id
        -- Delete the record itself
DELETE FROM Parent WHERE Id = @id
       -- delete the row from the temp table
DELETE FROM #DeletedParent WHERE Id = @id
    END;
GO

Do the same for the child tables, minus the children deletion, if there aren’t any, and you’re good to go.