— 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