Prevent update and delete without using where clause
This topic is about how to use a trigger to prevent an update or delete query to affect all the rows in a table
Stored Procedure For Update
This code prevents the accidental update and delete of datas when used without a where clause and the code is as follows
CREATE TRIGGER [ItemModule].[PreventUpdate]
ON [dbo].[Client]
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('dbo.Client')
AND index_id = 1)
BEGIN
RAISERROR('Cannot update all rows',16,1)
ROLLBACK TRANSACTION
RETURN;
END
END
GO
The sys.dm_db_partition_stats is used to return page and row-count information for every partition in the current databaseTrigger For Delete
CREATE TRIGGER [ItemModule].[PrevDel]
ON [dbo].[Client]
FOR DELETE 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('dbo.Client')
AND index_id = 1)
BEGIN
RAISERROR('Cannot Delete all the rows',16,1)
ROLLBACK TRANSACTION
RETURN;
END
Print 'Previous logic is working '
END
GO