4

Whenever I have to run an update statement, I always get nervous that I might forget the WHERE clause and overwrite the entire column in the database. Is there any setting or plugin or addon I can get for SQL Server Management Studio to block certain dangerous statements, unless you explicitly say its ok to run?

I'm working on a development server, so I can always roll back the changes, but still, I feel like I might have only the first two lines highlighted and click Execute or do something else stupid.

migrated from serverfault.com Jul 25 '14 at 14:36

This question came from our site for system and network administrators.

4

Create a trigger on update (another on delete is probably a good idea).

CREATE TRIGGER [Table].[uPreventUpdateOops] 
ON [Table]
FOR UPDATE AS 
BEGIN
     DECLARE @Count int
     SET @Count = @@ROWCOUNT;

     IF @Count >= (SELECT SUM(row_count)
         FROM sys.dm_db_partition_stats 
         WHERE OBJECT_ID = OBJECT_ID('Table') 
         AND index_id = 1)
     BEGIN
         RAISERROR('Cannot update all rows',16,1) 
         ROLLBACK TRANSACTION
         RETURN;
     END
END
GO

Note: This only prevents the most obvious mistake. Please do not use this on a production server as you're bound to find a statement that will totally Bork your table but pass this simple test. Also, this runs quite slow on large tables, performance will be affected.

3

The habits I got into very early on, after a couple whoopsie-daisy-sorry-about-your-project-tables:

  1. Begin Tran / Commit (but forgetting to commit can be just as detrimental)

  2. Write your WHERE clause first, even if it's a dummy 1=2 that will keep anything from being modified

  3. Highlight your syntax from the BOTTOM UP

2

You don't need a trigger to prevent you from doing something you shouldn't do. You should wrap your update in a TRANSACTION, and only commit if you are satisfied with the result.

You shouldn't be running update statements that may alter your whole db without the ability to roll back.

  • Unless the person highlights the commit portion accidentally as well. – datagod Feb 11 '15 at 5:00
  • You don't write the commit, or if you do, you wrap it in an IF that checks some condition (@@rowcount, @@error are good ones to check). – epic_fil Feb 20 '15 at 5:44
0

The Accidental Data Destruction Protector feature of the SSMS Tools plugin will do exactly what you want.

Also, it installs a New Query Template so that when you open a new script window, it has

BEGIN TRAN



ROLLBACK

as the standard query template, which I've found super helpful. You could just get in the habit of doing that, but SSMS Tools makes it really convenient.

The plugin isn't free, but it isn't expensive either, and it offers a trial period before you buy it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy