Trigger with NOCOUNT outputs 1 or more row(s) affected when no datatable rows were changed

Question

I am using SQL Server 2012. I have triggers on a view, with the intent of allowing my users to perform DML actions on it like any other table. These triggers have SET NOCOUNT ON and have many IF...RAISERROR(...) RETURN statements to catch logical errors. I noticed that even when a trigger is returned without affecting any underlying tables, SSMS outputs "n or more row(s) affected" where n is the number of rows that would have been affected in the view. So, I tested the following simple trigger, with the same results:

CREATE TRIGGER dbo.triggerViewTest 
ON dbo.viewTest
INSTEAD OF INSERT AS
BEGIN
    SET NOCOUNT ON
    IF 1 != 0
    BEGIN
        RAISERROR('No rows should be affected!', 16, 1)
        RETURN
    END
END

INSERT INTO dbo.viewTest (columnA) VALUES (1)

And SSMS prints 1 row(s) affected.

How do I suppress this output?

That's the row count from the *insert*, not the row count by any actions taken by the trigger. — Damien_The_Unbeliever, Dec 28 '17 at 06:48

score 1 · Accepted Answer · answered Dec 28 '17 at 06:17

1

Following will prevent this message returned to console. It is related with session settings not with the trigger code

set nocount on
insert into viewTest select...
set nocount off

answered Dec 28 '17 at 06:17

Eralper

6,461
2
21
27

I'm marking this as the answer because `It is related with session settings...` is the reason for this behavior, and it turns out the only way to prevent that message is to `THROW` an error. – zambonee Dec 28 '17 at 16:49

score 0 · Answer 2 · answered Dec 28 '17 at 16:55

Answering my own question here based on what @Eralper said; this message is from the session settings and not from the trigger. So, the only way to prevent the message is to THROW an error, which cancels that session (RAISERROR() does not cancel the session). The following trigger does not display n row(s) affected:

CREATE TRIGGER dbo.triggerViewTest 
ON dbo.viewTest
INSTEAD OF INSERT AS
BEGIN
    SET NOCOUNT ON
    IF 1 != 0
    BEGIN
        ;THROW 50000, 'No rows should be affected!', 1
    END
END

INSERT INTO dbo.viewTest (columnA) VALUES (1)

Trigger with NOCOUNT outputs 1 or more row(s) affected when no datatable rows were changed

2 Answers2