August 11, 2011 at 9:06 pm
hi,
recently i have make a mistake which i forgot to put in the 'where' statement and cause the all record from a table ( around 3 million ) updated, i have read some post which we can use the Top to limit the record updated / deleted but this is not what i want. What i need is the SQL Server will auto cancel my query if detected the affected record is more than 1000, is it possible ?
* most of the time i will use begin tran and rollbak to check the number of record affected before run the query but sometime it is really forgot to do this step and cause all record updated.
August 12, 2011 at 1:34 am
you can use set rowcount but there a lot of conditions -
http://msdn.microsoft.com/en-us/library/ms188774.aspx
best use where clause...
August 12, 2011 at 3:07 am
Set rowcount will have nothing to do with update/delete in SQL 2008.
As i mention in my problem, sometime i have make mistake to forget to put in 'where' statement.
now, i am seeking some kind of control to auto cancel the query if the number of record affected is more than 1000 to avoid this mistake happend again. If i depend on human, the mistake will still hapend in future.
August 12, 2011 at 3:19 am
You could create a trigger on every table that you wish to protect in this way. The trigger would roll back the transaction if the deleted rows were more than 1000. I don't know what effect this would have on performance in general - you'd need to test that.
John
August 12, 2011 at 9:44 am
I think Trigger would be an easy option for stopping the DML command you run as per your condition. However there would some performance issue with triggers.
August 12, 2011 at 9:51 am
YHChan (8/11/2011)
hi,recently i have make a mistake which i forgot to put in the 'where' statement and cause the all record from a table ( around 3 million ) updated, i have read some post which we can use the Top to limit the record updated / deleted but this is not what i want. What i need is the SQL Server will auto cancel my query if detected the affected record is more than 1000, is it possible ?
* most of the time i will use begin tran and rollbak to check the number of record affected before run the query but sometime it is really forgot to do this step and cause all record updated.
This is why you test your code on a dev box. If it is a script on prod ALWAYS ALWAYS ALWAYS test your script inside a begin/rollback so you can double check your rowcounts and such.
I made this mistake once on a production box and set the employee table to inactive (no where clause). This caused a lot of people a lot of overtime because we had some com objects that disabled their AD account on update. This was at a law firm with literally thousand of users. Took a couple days to get it all straightened out again.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply