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. <\/p>\n
For example, imagine we could<\/p>\n
\r\nUPDATE EXACTLY 1 table SET field = value WHERE key = whatever\r\n<\/pre>\nor<\/p>\n
\r\nDELETE EXACTLY 16 FROM table WHERE field = whatever\r\n<\/pre>\nAnd those commands would fail and do nothing if they would affect more or less than 1 and 16 rows respectively. <\/p>\n
Of course, SQL doesn’t support this syntax. A workaround is to use the output clause<\/a> and a transaction. Alternatively, @@ROWCOUNT<\/a> 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?<\/p>\n
We can take advantage of implicit transactions in triggers<\/a> 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.<\/p>\n
So, in use, this looks like:<\/p>\n
\r\nEXEC RowCountExactly 'tblBridges', 2\r\nUPDATE tblBridges SET fips_code = 12345;\r\n<\/pre>\nIn this case, more than 2 rows would have been updated, so the command fails:<\/p>\n
\r\nUnexpected number of rows affected; 156507 vs 2 expected\r\nMsg 3609, Level 16, State 1, Line 6\r\nThe transaction ended in the trigger. The batch has been aborted.\r\n<\/pre>\nThe 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.<\/p>\n
\r\nIF OBJECT_ID ( 'dbo.RowCountExactly', 'P' ) IS NOT NULL \r\n DROP PROCEDURE dbo.RowCountExactly;\r\nGO\r\nCREATE PROCEDURE dbo.RowCountExactly\r\n(\r\n\t@TableName nvarchar(MAX),\r\n\t@RowCount int\r\n)\r\nAS\r\nBEGIN\r\n\tIF OBJECT_ID ('dbo.RowCountCheck','TR') IS NOT NULL\r\n\t\tEXEC('DROP TRIGGER dbo.RowCountCheck');\r\n\t\r\n\tDECLARE @trigger varchar(max);\r\n\r\n\tSET @trigger = 'CREATE TRIGGER dbo.RowCountCheck ON ' + \r\n\t CONVERT(varchar(max), @TableName) + \r\n\t' AFTER INSERT, UPDATE, DELETE\r\n\tAS\t\r\n\tBEGIN\r\n\t\tDECLARE @actual int;\r\n\t\tSET @actual = @@ROWCOUNT;\t\t\r\n\r\n\t\tIF @actual <> ' + CONVERT(varchar(max), @RowCount) + '\r\n\t\tBEGIN\r\n\t\t\tROLLBACK;\r\n\t\t\tIF OBJECT_ID (''dbo.RowCountCheck'',''TR'') IS NOT NULL\r\n\t\t\t\tEXEC(''DROP TRIGGER dbo.RowCountCheck'');\r\n\r\n\t\t\tRAISERROR (N''Unexpected number of rows affected; %d vs %d expected'',\r\n\t\t\t\t10, 0, \r\n\t\t\t\t@actual, ' + CONVERT(varchar(max), @RowCount) + ');\r\n\t\tEND\r\n\t\tELSE\r\n\t\t\tIF OBJECT_ID (''dbo.RowCountCheck'',''TR'') IS NOT NULL\r\n\t\t\t\tEXEC(''DROP TRIGGER dbo.RowCountCheck'');\r\n\tEND\t\t\r\n\t';\r\n\r\n\tEXEC(@trigger);\r\n\t\r\n\r\nEND;\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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, … Continue reading