7

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.

  • 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
7

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.

  • 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
5

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.

2

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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