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```
Subject
Views
Written By
Posted
1424
August 29, 2017 12:08PM
733
August 29, 2017 01:08PM
Re: Create a trigger that prevents updating all rows in a table
1066
August 29, 2017 01:57PM
714
August 29, 2017 02:12PM
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.