Geek asks and answers

Prevent accidental updates / deletes on any table

I had to accidentally update all the records in the production table several times. Lack of attention and more ...

I heard in MySQL that this is a compile / start time switch to prevent such crashes. For example, if I did

UPDATE Table SET Field=0

this will not compile / execute due to a missing WHERE clause. And if you really wanted to update everything, you could

UPDATE Table SET Field=0 WHERE 42=42

Any ideas for MS SQL?

I found several answers on the Internet, citing a trigger. I think that would be a little expensive. And that would mean that I have to put a trigger on every needed table.

+5
source share
5 answers

, autocommit SQL Server, IMPLICIT_TRANSACTIONS. .

, , SET IMPLICIT_TRANSACTIONS ON. COMMIT, - . .

+4

, COMMIT . , , , , COMMIT. , ROLLBACK.

, INSERTS UPDATES, , .

, ;) .

+2

, ( MySQL, , ), , , SQL.

update
set
where

. WHERE, WHERE. , SQL SQL- , .

, , SQL emacs "UPDATE" WHERE.

- , WHERE, .

where ()


where (() or ())


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

.

, C. , "" . , .

if () {
}
+1

as stated in previous examples: do not allow access to prod db, do not upgrade without using; I add different users and security policies for them. Maintain a good journal so you can return it just in case. Updating the entire table depending on its size may block the entire table!

0
source

How to make a habit of always using:

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

Say you knew you only want to update 1 row. If you return “2 rows are affected,” you know you made a typo, but at least you did not destroy the enire table.

0
source

More articles:


All Articles