"Instead of delete trigger" triggers two times

Question

I am using MS SQL Server 2016 where I have implemented a instead of delete trigger. It looks like this:

ALTER TRIGGER MyTrigger ON MyTable INSTEAD OF DELETE AS 
BEGIN
    IF --some condition
    BEGIN
        RAISERROR ('Error msg', 16, 1)
        ROLLBACK TRAN
        RETURN
    END
    DELETE MyTable FROM MyTable JOIN deleted ON MyTable.id = deleted.id
END

If I execute a DELETE statement on the table 'MyTable' and the condition in the if is not fulfilled the DELETE statement is executed after the if-block. This is absolutely correct. But in the console of SSMS it is written twice that the DELETE statement was executed. So the following is written in the console:

(1 rows affected)
(1 rows affected)

I do not understand why. Why does SSMS indicate twice that a row is affected? I use SSMS version 15.0.18338.0.

Answer 1

This is because there were 2 sets of data effect, the set outside the TRIGGER, and then again inside it, because the initial dataset doesn't perform the DML operation itself. If you don't want to see the latter count, turn NOCOUNT to ON. This, of course, means that if fewer rows are effected in your TRIGGER, you won't know about it in the output from SSMS (but it's just informational anyway).

It is also heavily advised that you don't use ROLLBACK inside a TRIGGER, handle transactions outside the TRIGGER, not inside. RAISERROR isn't recommend either and you should be using THROW for new development work (that's been recommended since 2012!). This results in a TRIGGER like below:

CREATE OR ALTER TRIGGER MyTrigger ON dbo.MyTable INSTEAD OF DELETE AS 
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM deleted WHERE SomeVal = 'Nonsense')
        THROW 95302, N'Error Msg', 16; --Use an error number appropriate for you
    ELSE
        DELETE MT FROM dbo.MyTable MT JOIN deleted ON MT.id = deleted.id;
END;
GO