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 database

Trigger 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


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: