From the @@ROWCOUNT documentation:
Statements such as USE, SET , DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.
Which means that if you get into the good habit of beginning all your stored procedure bodies with
SET NOCOUNT ON;, you’ll be in for a nasty surprise when you code a trigger with short-curcuit logic.
For example, in this audit trigger, the INSERT statement will never execute:
CREATE TRIGGER dbo.Wibble_InsertAuditRow ON dbo.Wibble AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; IF @@ROWCOUNT = 0 BEGIN RETURN; END; INSERT INTO Audit.WibbleHistory ( Action, WibbleCode, WobbleSpec, GibletCount ) SELECT 'INSERT', WibbleCode, WobbleSpec, GibletCount FROM INSERTED UNION ALL SELECT 'DELETE', WibbleCode, WobbleSpec, GibletCount FROM DELETED END;
(Let’s assume that
Audit.WibbleHistory has extra columns with default values that audit when and by whom each change is made.)
SET NOCOUNT ON sets
@@ROWCOUNT to zero, the do-nothing code path will always execute.
To avoid this, make sure to check
@@ROWCOUNT before setting