CODEWITHSUNDEEP
sqlsql-server
Holmes IV
Holmes IV

Reputation: 1749

SQL Set Max Affected Rows for Session

I work with a group of people in a DEV environment and sometimes we get a little anxious on the F5. I would say on a monthly basis someone updates every record in a table instead of just 1 or 2 because they miss the were clause or were doing something like 

Begin Transaction

update table1 
set column1 = '50'

select * from table1
where column2= 'abc'
and column3 = '123'

 If @@ROWCOUNT != 1
    Begin
        Rollback
        Print 'Failed ' + @Ticket
    End
 else
    Begin
        commit
        Print 'Success ' + @Ticket
    End

In this case they meant to comment or delete the select line. My question is, can you set something to auto rollback if more than X number of rows are affected? I can never see them updating more than 400 or so. I have told them to you Begin Transaction and @@RowCount to verify but since it is 2 statements and the 2nd has the right number it doesn't help.

Upvotes: 0

Views: 331

Answers (2)

Paul Williams
Paul Williams

Reputation: 17040

It sounds like the developers performing these update queries need to be more rigorous. Every update-- even in development-- should be written with a begin tran and rollback until they are sure that the query updates the correct rows.

For example, I always use the following template:

-- review the data to be updated
select * from MyTable where MyColumn = 'A'

begin tran

update MyTable
set MyColumn = 'B'
where MyColumn = 'A'

-- be sure the query updated the expected number of rows
select @@rowcount 

-- re-review the original data to make sure those rows were changed
select * from MyTable where MyColumn = 'A'

-- reverse the changes; change this to a commit *only* when the above is proven to be correct
rollback

-- EDIT --

Adapting this to your example, you could capture the @@ROWCOUNT in a variable and refer to it later. For example:

declare @RowsUpdated int = 0

Begin Transaction

update table1 
set column1 = '50'

-- capture the @@ROWCOUNT for use later
select @RowsUpdated = @@ROWCOUNT

-- you could add row counts from multiple update statements together:
-- select @RowsUpdated = @RowsUpdated + @@ROWCOUNT

select * from table1
where column2= 'abc'
and column3 = '123'

If @RowsUpdated != 1
Begin
    Rollback
    Print 'Failed ' + @Ticket
End
else
Begin
    commit
    Print 'Success ' + @Ticket
End

Upvotes: 1

Greg
Greg

Reputation: 4055

I doubt SQL Server out of the box provides a solution for you. if this is a big issue, then deploy SQL Server database backup, with regularly scheduled log backups, so you can restore to a given point in time. Or, restrict their access so they are updating/inserting/deleting/querying from procs only (this probably won't scale, but it's an option). Or tell them to use TOP in all their queries.

In the meantime, ensure your devs never, ever, troubleshoot on a prod system.

Upvotes: 1

Related Questions