auto Abort query if update/delete record > 1000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

auto Abort query if update/delete record > 1000

Author

Topic

yhchan2005
Starting Member

26 Posts

Posted - 2011-08-11 : 23:03:07

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-11 : 23:12:09

Let's forget about the possibility of doing this first.

But is it practical to do this ? What if you really need to update 1001 records ?

KH
[spoiler]Time is always against us[/spoiler]

yhchan2005
Starting Member

26 Posts

Posted - 2011-08-11 : 23:33:14

in my environment, it is due to the program bugs and cause some one need to use the SQL Management studio to backdoor patching the data back to the correct value. and most of the case, the data need to be patch only few record. if i assign some one to do this patching and he also forgot to put the 'where' statement, then the all data will be mess up. if i can set or configure SQL server to do this, then i will very sure this mistake will not happend.

if really need to update more than 1000 record, then need to bring it up to the manage and that time we can temporary to disbale this and after that enable it again. By this will not happend frequently unless there is changes in the program or data structure.

doing back door patching is very dangerous but i have no choice and have to do it. A very senior people some time will also make mistake.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-11 : 23:49:46

there isn't a configurable parameter in SQL Server that can do this. The only way i can think of is to use trigger for this. But i still question the practicability of this.

KH
[spoiler]Time is always against us[/spoiler]

yhchan2005
Starting Member

26 Posts

Posted - 2011-08-12 : 00:30:32

Let's forget about practicability, can you share your idea how to do this by using trigger ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-12 : 03:56:16

[code]
create table test
(
col1 int,
col2 int
)

truncate table test
insert into test select 1, 10
insert into test select 1, 11
insert into test select 1, 12
insert into test select 1, 13
insert into test select 1, 14
insert into test select 2, 20
insert into test select 2, 21

create trigger tu_test on test for update
as
begin
if (select count(*) from inserted) >= 5
begin
raiserror ('Update affected more than 5 rows', 16, 1)
rollback transaction
end
end

update test
set col2 = 100
where col1 = 1

update test
set col2 = 100
where col1 = 2

select *
from test
[/code]

KH
[spoiler]Time is always against us[/spoiler]

yhchan2005
Starting Member

26 Posts

Posted - 2011-08-12 : 05:15:36

thanks for sharing !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources