Trigger execution order after delete

All we need is an easy explanation of the problem, so here it is.

Let’s say I have two tables, one called ParentTable, and another called ChildTable, with a one-to-many relationship, where one Parent may have many Children.

I’m creating an AFTER DELETE trigger on ChildTable. In that trigger, I’m checking a value from ParentTable (if it exists), something like this:

-- ...
FROM deleted d
JOIN ParentTable pt ON d.FK_Parent = pt.ID
WHERE pt.Foo = 'bar'
  1. If I delete from ChildTable first, then from ParentTable, does the AFTER DELETE trigger fire before or after the row in ParentTable is deleted?

    a. These two DELETE statements would most often be in an explicit transaction, as I’m using Entity Framework.

  2. If I set the foreign key in ChildTable to CASCADE DELETE, then deleted from ParentTable only, will the row(s) in ParentTable still be accessible when the AFTER DELETE trigger fires?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

From the docs:

An AFTER trigger is run only after the triggering SQL statement has run successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted.

  1. Deleting from ChildTable first, then from ParentTable, will always execute the child trigger before the parent one, because triggers fire per statement, not per transaction or batch.

  2. Cascading foreign-keys you can test quite easily.

CREATE TABLE Parent (id int identity PRIMARY KEY);
CREATE TABLE Child (id int identity PRIMARY KEY, pId int REFERENCES Parent(id) ON DELETE CASCADE);
CREATE TRIGGER trParent ON Parent AFTER DELETE AS

PRINT 'trParent';
DECLARE @i int;
SELECT @i = COUNT(*) FROM Parent;
PRINT @i;
SELECT @i = COUNT(*) FROM Child;
PRINT @i;
CREATE TRIGGER trChild ON Child AFTER DELETE AS

PRINT 'trChild';
DECLARE @i int;
SELECT @i = COUNT(*) FROM Parent;
PRINT @i;
SELECT @i = COUNT(*) FROM Child;
PRINT @i;
INSERT Parent DEFAULT VALUES;
INSERT Child(pId) VALUES (SCOPE_IDENTITY());
DELETE Parent;

db<>fiddle

This prints

trChild
0
0
trParent
0
0

So the Child trigger is fired first, then the Parent trigger. But there are no rows in either table before even the first trigger was called. To access the original data you instead use the deleted table in the trigger.

Note that if there are no rows to cascade, it appears the child trigger does not fire at all.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply