It's fairly common to allow certain users to perform ad-hoc updates or deletions to tables. Data Manipulation Language (DML) operations such as these always come with risk, and incidents may occur where someone accidentally issues a Delete command without a WHERE clause, thereby deleting all rows in a table! Luckily, there are some simple steps you can take to prevent accidental (or deliberate!) destructive DML operations. We'll examine a couple of these in today's blog.
A Dangerous Deletion Procedure
As a starting point, let's take a stored procedure that will delete rows in a table based on a user-supplied where clause. This SQL Server procedure, shown in the Navicat for SQL Server development and admin client, accepts a table and whereclause parameter and returns the number of rows deleted:
In Navicat, we can run a procedure right from the editor via the Execute button. Clicking it brings up a dialog to enter parameters:
The delcnt specifies the count of records we expect to be deleted. By leaving it blank, if any rows are deleted, the transaction is rolled back, and the row(s) preserved. Had we supplied a number, the procedure would have compared it to the @@rowcount server variable after the delete operation to determine if the number of records deleted match the expected number. I our case, a message is displayed to tell us how many rows would have been deleted:
As expected, the actcnt is zero, confirming that no rows were actually deleted:
The delcnt parameter acts as a built-in fail-safe in that it forces the user to specify how many rows that he/she expects to be deleted. We could also add one additional layer of safety by checking that the whereclause parameter was supplied:
Maximizing delcnt Variable Checks
Disallowing an empty where clause is not without drawbacks, in that there is nothing stopping someone from entering something like "id is not null". The best we can say about this safety check is that at least it would only fail in cases where the user to deliberately deletes all rows in a table.
For that reason, perhaps a better solution may be to expand on the idea of comparing the actual count of affected (deleted) rows to the expected count (@delcnt). With only a few extra lines of code, we can count the number of rows in the table and rollback the transaction if the number of affected rows is equal to the total number of rows in the table. This can be accomplished using the built-in sp_executesql stored procedure. It supports the use of both input and output parameters so we can store the results of the count(*) function to a variable. Here is the new code:
Now, if we try to run a query that would delete all of the rows in the table such as follows:
...the deletion is prevented:
Conclusion
While there is no sure-fire way to prevent data loss due to accidental or deliberate deletion, every counter-measure that we employ helps minimize the chances of a catastrophic event from taking place.
Interested in Navicat for SQL Server? You can try it for 14 days completely free of charge for evaluation purposes!
