Safer SQL

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

or

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

Comments are closed.