SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;
trChild
0
0
trParent
0
0
3 rows affected