Re: Create a trigger that prevents updating all rows in a table

[david lee]

The software is my own web app. I can't find the bug, and I've spent hours looking for it.

The only problem with this solution is that there are time when the 'sent_at' column should be set to today's date.

The problem is that there are times when the bug updates every single row in the "Emails" table and updates the 'sent_at' column to whatever today's date is.

I'm looking for a trigger that will prevent updating all the rows in the table. Or will prevent updating more than 100 rows at a time.

SQL Server has this functionality. So I'm hoping there is an equivalent to MYSQL for this SQL Server query

```--Script # 1: Create UPDATE trigger for SQL Server 2005 and SQL Server 2008

USE AdventureWorks
GO

CREATE TRIGGER [Purchasing].[uPreventWholeUpdate]
ON [Purchasing].[VendorContact]
FOR UPDATE AS
BEGIN
DECLARE @Count int
SET @Count = @@ROWCOUNT;

IF @Count >= (SELECT SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID('Purchasing.VendorContact' )
AND index_id = 1)
BEGIN
RAISERROR('Cannot update all rows',16,1)
ROLLBACK TRANSACTION
RETURN;
END
END
GO```