0

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?

1 Answer 1

7

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.

1
  • Thanks, that really helped. Especially "triggers fire per statement, not per transaction or batch", because I was having trouble finding that information explained (in a way that made sense to me) in the docs.
    – CarenRose
    Jun 1, 2022 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.