Triggers and disabling “row(s) affected” message

Question

I have a trigger type INSTEAD OF Insert, Update. It looks something like below:

IF EXISTS(some select staement) 
 updade
 set
 where
 ELSE IF (some other select staement)
    insert
    values
 ELSE--(3)
RAISERROR ('msg',16,1)
RETURN; SET NOCOUNT ON;

The issue is that in 3rd - "else" option I would like to show only error message without any "row(s) affected" message. SET NOCOUNT ON dosen't work for me. I've already tried different configurations, put this with and without return. I was putted it everywhere in my statement. It doesn't work anywhere. I use SQL Server 2005 Can anybody help me please? Rows affected massage should appear always. The only exception is else statement.

Answer 1

Use SET NOCOUNT ON; before a query and then use GO

SET NOCOUNT ON;
GO

Answer 2

In a simplified example:

create table T (
    ID int not null
)
go
create trigger TT
on T
instead of insert
as
    RAISERROR('No',16,1)
go
insert into T (ID)
select 1

We get the output:

Msg 50000, Level 16, State 1, Procedure TT, Line 5
No

(1 row(s) affected)

The only way to suppress that "1 row affected" message is to rollback the transaction (by including ROLLBACK after the error message). And that will generate this instead:

Msg 50000, Level 16, State 1, Procedure TT, Line 5
No
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

There's no way to suppress these messages further. The "1 row affected" message is being generated in the outer scope (the scope in which the INSERT statement is being run) rather than within the scope of the trigger, and is being generated because the trigger is running to completion - so far as the outer statement is concerned, it's been succesful.

The usual advice about NOCOUNT is to use it to suppress additional rowcounts being returned from within a trigger. If you're using it for that, it should be the first statement within the body of the trigger. IN your sample, you have it as the last statement, where it will have no effect anyway.