MySQL Forums
Forum List  »  Triggers

Re: Create a trigger that prevents updating all rows in a table
Posted by: david lee
Date: August 29, 2017 01:57PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Create a trigger that prevents updating all rows in a table
345
August 29, 2017 01:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.