CODEWITHSUNDEEP
sql-servert-sqldatabase-triggernocount
zambonee
zambonee

Reputation: 1647

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

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?

Upvotes: 0

Views: 616

Answers (2)

zambonee
zambonee

Reputation: 1647

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)

Upvotes: 0

Eralper
Eralper

Reputation: 6622

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

Upvotes: 1

Related Questions