Prevent accidental updates/deletes on any table

Question

It happened to me a couple of times to accidentally update all records of a production table. Lack of attention and whatnot...

I've heard in MySQL to be a compile/run time switch to prevent such accidents. Like, if I would do a

UPDATE Table SET Field=0

this won't compile/run because of the missing WHERE clause. And if you really wanted to update all, you could

UPDATE Table SET Field=0 WHERE 42=42

Any ideas for MS SQL?

I found some answers online referring to a trigger. That would be a little costly I guess. And it would mean I must put the trigger on every necessary table.

Answer 1

If you are trying to set this for everybody, you can turn off SQL Server's autocommit functionality by turning IMPLICIT_TRANSACTIONS on. See here.

If it is just you that you are concerned about, the above can be used in your session as well with SET IMPLICIT_TRANSACTIONS ON. Then you must actually call COMMIT to make anything stick. See here.

Answer 2

In cases like this, you can always start a transaction before issuing the command, but without adding a COMMIT at the end. That way you can verify the query had the desired effect, and if so, manually issue a COMMIT. In case you made a mistake, simply issue a ROLLBACK.

You can also add triggers to the tables to prevent INSERTS or UPDATES from happening, but that would effect everyone and not just you.

The bottom line is that you should just never run untested queries on your production system ;) Use a snapshot copy of the database instead.

Answer 3

In addition to what others have said (MySQL safe update mode, restrict access to production, test first on a development server), it's not hard to train yourself to write an update statement like this in a SQL window.

update
set
where

Then backtrack to the top, and fill in the details. This prevents you from omitting the WHERE clause, but it doesn't prevent you from writing a bad WHERE clause. I do this all the time now, even when I'm writing SQL in a SQL window on my local machine where the penalty for a bad update is nil.

The best thing to do is probably to write your SQL in emacs your text editor of choice, and program it to expand "UPDATE" to a partial statement with a WHERE clause.

Before anyone asks, I also write complex WHERE clauses like this.

where ()

followed by

where (() or ())

then by

where ((() and ()) or ())

Then I go back and fill in the conditions.

I picked up this habit years ago when I had to write a lot of code in C. It stems from training myself to write "if" statements like this. Omitted parens and braces caused other people a lot of headaches, so I eliminated that possibility.

if () {
}

Answer 4

How about making a habit of always using:

UPDATE TOP (2) dbo.table set name = etc

Let's say you knew you only wanted to update 1 row. If you got back "2 rows affected", you know you made a typo, but at least you havn't destroyed the enire table.