Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to configure SQL Server to limit the number of rows than an update statement can modify? Say I wanted the limit to be 30,000 rows and someone fired an update that would modify 45,000 rows, they would receive an error or some other preventive message.

share|improve this question

migrated from stackoverflow.com Nov 30 '11 at 16:05

This question came from our site for professional and enthusiast programmers.

    
Interesting problem, but why are you trying to do this? –  Nick Chammas Nov 30 '11 at 16:52
    
I don't know much about this in dbms's other than sybase, but you could set the transaction log appropriately so it'll fill up and fail if you act on too many rows. –  user606723 Nov 30 '11 at 20:28

3 Answers 3

up vote 7 down vote accepted

A trigger would be how I would handle this. This will slow down all your update statements as SQL now has a bunch of extra work to do, but it'll handle your requirement.

CREATE TRIGGER Something on dbo.SomeTable
FOR UPDATE
AS
BEGIN
IF (select count(*) from inserted) > 30000
BEGIN
   RAISERROR('To many rows being processed at one.  Reduce and try again.', 16, 1)
   ROLLBACK
END
END

Personally I wouldn't like the idea of the instead of trigger and reapplying the update with a smaller row set as this gets into tricky areas of half completed operations now which could get very messy, quickly.

share|improve this answer
2  
Of course the same trigger would have to be built on every table that you wanted to limit inserts on. There is no global setting for this. –  HLGEM Nov 30 '11 at 21:12
    
Yep, excellent point. –  mrdenny Dec 1 '11 at 0:44
    
This approach works perfectly. We need to lock down a couple of critical tables so they don't get bogged down with massive updates. –  Chad Cook Dec 1 '11 at 16:49

You could also use an instead of trigger. You would either use SET ROWCOUNT <a number> (which is being deprecated) or UPDATE TOP <a number>... to re-apply the update (or insert or whatever).

share|improve this answer

You could do this with an AFTER INSERT TRIGGER, and count the number of records in the inserted table, and handle your desired record limit that way.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.