Thursday, October 15, 2009

Warn update without WHERE clause

Create below SQL Trigger for table need to have this check.
T-SQl Script:
CREATE TRIGGER [dbo].[trgWarnUpdate]
ON [dbo].[Employee]
FOR UPDATE AS
BEGIN
DECLARE @Count int
SET @Count = @@ROWCOUNT;
IF @Count >= (SELECT top 1 rows
FROM sysindexes where name like 'pk%'
and id = object_id('Employee'))
BEGIN
RAISERROR('Updating all records restricted. To update all records disable this trigger.',16,1)
ROLLBACK TRANSACTION
RETURN;
END
END
GO


Note:Rename Emplyoee with respective SQL table. Assuming table has primary key and same get checked.

No comments:

Post a Comment