Creating a trigger to protect a specific row or group of rows from deletion

— Create test table:
CREATE TABLE dbo.TriggerTest
(
[_ID] INT NOT NULL
IDENTITY ,
[TESTdata] [VARCHAR](500) NOT NULL
)
ON [PRIMARY];
GO

— add a row to delete

INSERT INTO [dbo].[TriggerTest]
( [TESTdata] )
VALUES ( ‘Fubar1’ );
GO

— add a row to protect
INSERT INTO [dbo].[TriggerTest]
( [TESTdata] )
VALUES ( ‘Fubar2’ );
GO

— create trigger
CREATE TRIGGER ProtectTriggerTest ON TriggerTest
FOR DELETE
AS
BEGIN
DECLARE @Tdata VARCHAR(500);
SELECT @Tdata = TESTdata
FROM Deleted
WHERE Deleted.TESTdata = ‘Fubar2’;

IF @Tdata IS NOT NULL
BEGIN
ROLLBACK TRANSACTION;
— add in other code here!!!!!!
EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘a_sql’, — sysname
@recipients = ‘mark.horninger@xxx.com’, — varchar(max)
@copy_recipients = ”, — varchar(max)
@blind_copy_recipients = ”, — varchar(max)
@subject = N’DELETE WARNING!!!!’, — nvarchar(255)
@body = N’Someone tried to delete a row they were not supposed to!’, — nvarchar(max)

@mailitem_id = 0, — int
@from_address = ‘DoNotReply@xxx.com’, — varchar(max)
@reply_to = ‘DoNotReply@xxx.com’ — varchar(max);

–RAISERROR(‘You cannot delete that row from TriggerTEst!’,16,1);

RETURN;
END;

END;

GO

SELECT *
FROM dbo.TriggerTest;
GO

DELETE FROM dbo.TriggerTest
WHERE TESTdata = ‘Fubar1’;

DELETE FROM dbo.TriggerTest
WHERE TESTdata = ‘Fubar2’;

DELETE FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTest;
GO

— remove table
–DROP TABLE dbo.TriggerTest