SQL Transaction for Updates

August 26, 2008

Another quick tip: I used to get this functionality for free when I used an SQL plugin for Emacs years ago.  I wanted to use it again and had a quick think about how it must have been done; it’s trivial really, but I don’t have a huge background with SQL transactions.  Anyway, this controls your update statement so that it only affects one row (or as many rows as you specify) and rolls back if the rows affected is greater.  This fixes the worst nightmare of “250,000 rows affected” when you were expecting “1 row affected”, and it’s in a production system, and you don’t have a backup.

begin tran x

update Table set Value='NewValue' where Name='Key'
if @@rowcount > 1
begin
print 'error'
rollback tran x
end 
else
begin
commit tran x
end
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: