TSQL 2012 deleted table in INSTEAD OF and AFTER triggers? My colleague ask me very simple, but difficult for me question:

If I have deleted table with 10 records in INSTEAD OF trigger and I delete 8 of them how many records will I have in deleted table in AFTER trigger?

I think it wuold be 10, but I am not sure.
Very easy to test.

CREATE TABLE T(X INT);

GO

INSERT INTO T 
SELECT TOP 10 1
FROM sys.all_objects;

GO

CREATE TRIGGER TR1 ON T INSTEAD OF DELETE AS DELETE TOP (8) FROM T;

GO

CREATE TRIGGER TR2 ON T AFTER DELETE AS SELECT COUNT(*) FROM DELETED;

GO

DELETE FROM T;


DROP TABLE T;



  Answer 8


This is the correct behaviour. The after trigger should reflect the rows that were actually deleted. If you had an audit trigger recording 10 deletions when only 8 occurred this would be clearly incorrect.