How to Avoid Accidentally Update All Rows

Here are just some thoughts on preventing this problem:

Before sql submitted to be executed

  • Peer Code review: Although it may be not necessary to fully review the details, a quick review  can at least catch some blunt errors such as missing where clause in update/delete statement;
  • Run by some tools to detect such problems

Executing time

  • Dry run first then run, only commit after double checked
BEGIN TRANSACTION
--Put all the execution code here
Update MyTable set quantity=1
ROLLBACK TRANSACTION

Database guard

  • Add a trigger on the updating table before execute any statement
  • Execute the statement
  • Drop the trigger

A sql script with the prevention trigger can look like:

USE myDB
GO

--create a trigger to block update/delete all rows in a table, or an arbitrary number of rows
CREATE TRIGGER tempTrigger
ON dbo.MyTable
FOR UPDATE AS
BEGIN
  DECLARE @Count int
  SET @Count = @@ROWCOUNT;
  IF @Count >= (SELECT SUM(row_count) --or just put a number here, for example 5
    FROM sys.dm_db_partition_stats
    WHERE OBJECT_ID = OBJECT_ID('dbo.MyTable ' )
    AND index_id = 1)
  BEGIN
    RAISERROR('Cannot update all rows',16,1)
    ROLLBACK TRANSACTION
    RETURN;
  END
END
GO
--Put all the execution code here
Update MyTable set quantity=1
--Drop the trigger after completion to avoid runtime cost
DROP TRIGGER tempTrigger

Use Some Tools for Detection
Two free add-ons can be installed if you use SQL Server Management Studio. The installation and usage is straghtforward.

 

Write Your Own Tools

If you still need more

Advertisements
This entry was posted in Best Software Practices, Database and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s