Trigger execution order after delete

[elysha]

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?

[emran]

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver16#remarks-for-dml-triggers

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;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=55f40682e31038e65fa374226d36d211

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 https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6af4a818d77b0548175e638c543164e0 .