When writing adhoc queries, I find myself writing UPDATEs and DELETEs where I know how many rows should be affected, or, in a stored procedure, maybe I have a variable that I can reference that tells me that. With LINQ, we can use .Single() to throw an exception if there is more than one result, for example. It would be helpful if SQL had an extension that let us specify exactly how many rows should be affected, and if not, the whole command is canceled.
For example, imagine we could
UPDATE EXACTLY 1 table SET field = value WHERE key = whatever
DELETE EXACTLY 16 FROM table WHERE field = whatever
And those commands would fail and do nothing if they would affect more or less than 1 and 16 rows respectively.
Of course, SQL doesn’t support this syntax. A workaround is to use the output clause and a transaction. Alternatively, @@ROWCOUNT and a transaction can also be used. However, writing these transaction wrappers around a statement is a lot of text and space for a small benefit. Could these statements be automated somehow?
We can take advantage of implicit transactions in triggers to control success of the overall command. By executing a rollback within the trigger, the update or delete command itself will be rolled back, without an explicit begin transaction.
So, in use, this looks like:
EXEC RowCountExactly 'tblBridges', 2 UPDATE tblBridges SET fips_code = 12345;
In this case, more than 2 rows would have been updated, so the command fails:
Unexpected number of rows affected; 156507 vs 2 expected Msg 3609, Level 16, State 1, Line 6 The transaction ended in the trigger. The batch has been aborted.
The implementation is a stored proc “RowCountExactly” that creates a trigger on the specified table. The trigger checks if the rowcount differs from the specified value. If so, it rolls back the batch. Either way (succeed or fail), the trigger also removes itself, so that it only runs a single time.
IF OBJECT_ID ( 'dbo.RowCountExactly', 'P' ) IS NOT NULL DROP PROCEDURE dbo.RowCountExactly; GO CREATE PROCEDURE dbo.RowCountExactly ( @TableName nvarchar(MAX), @RowCount int ) AS BEGIN IF OBJECT_ID ('dbo.RowCountCheck','TR') IS NOT NULL EXEC('DROP TRIGGER dbo.RowCountCheck'); DECLARE @trigger varchar(max); SET @trigger = 'CREATE TRIGGER dbo.RowCountCheck ON ' + CONVERT(varchar(max), @TableName) + ' AFTER INSERT, UPDATE, DELETE AS BEGIN DECLARE @actual int; SET @actual = @@ROWCOUNT; IF @actual <> ' + CONVERT(varchar(max), @RowCount) + ' BEGIN ROLLBACK; IF OBJECT_ID (''dbo.RowCountCheck'',''TR'') IS NOT NULL EXEC(''DROP TRIGGER dbo.RowCountCheck''); RAISERROR (N''Unexpected number of rows affected; %d vs %d expected'', 10, 0, @actual, ' + CONVERT(varchar(max), @RowCount) + '); END ELSE IF OBJECT_ID (''dbo.RowCountCheck'',''TR'') IS NOT NULL EXEC(''DROP TRIGGER dbo.RowCountCheck''); END '; EXEC(@trigger); END; GO