Can you prevent more than X number of rows being updated in SQL Server?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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