T-SQL INSTEAD OF DELETE Trigger fires without 'provocation'

Question

I've got the following INSTEAD OF DELETE trigger, which sends an email while rolling back any Delete attempts.

ALTER TRIGGER tr_OnDel_Orders
ON  ORDERS 
INSTEAD OF DELETE
AS 
BEGIN

SET NOCOUNT ON;

declare @b varchar(5000)
Declare @OrderID BIGINT,

SELECT @OrderID  = OrderID
FROM deleted d

set @b = 'Someone attempted to delete the following order:' + CHAR(10);
set @b = @b + ' OrderID: ' + cast(@OrderID as varchar(30)) + CHAR(10);
set @b = @b + ' UserID: ' +  SYSTEM_USER

RAISERROR('Cannot delete order', 16, 1)
ROLLBACK TRAN

EXEC msdb.dbo.sp_send_dbmail @recipients = 'myemail@mycompany.com',
                         @body = @b,
                         @subject = 'Attempt to Delete an order' 


RETURN
END
GO

This works when I try to delete an order from the ORDERS table. However, I cannot understand why, this trigger keeps sending emails periodically with empty body. As far as I'm aware, there are no obvious attempts to delete an order. What else could cause such blank emails?

This trigger don't work with rows sets, you know? Also, has `orders` a foreign key with delete cascade option? — dani herrera, Feb 06 '12 at 21:36
Perhaps it fires when someone does `delete orders where 1=0`, and `@OrderID` ends up being `null`. That would make `@b` null too, I think. — Blorgbeard, Feb 06 '12 at 21:37
You can set up an extended events trace to capture the TSQL call stack when the trigger fires [Example code here](http://stackoverflow.com/a/3149717/73226) — Martin Smith, Feb 06 '12 at 21:44

Aaron Bertrand · Accepted Answer · 2012-02-06T21:54:48.377

This trigger handles multi-row deletes, and also doesn't bother sending you a meaningless e-mail if someone fires a delete statement that doesn't delete any rows:

ALTER TRIGGER dbo.tr_OnDel_Orders
ON dbo.ORDERS 
INSTEAD OF DELETE
AS 
BEGIN
  IF EXISTS (SELECT 1 FROM deleted)
  BEGIN
    SET NOCOUNT ON;

    DECLARE @b VARCHAR(5000) = '';

    SELECT @b = @b + ',' + CONVERT(VARCHAR(12), OrderID)
        FROM deleted;

    SET @b = 'Someone attempted to delete the following orders:' 
        + CHAR(10) + @b + CHAR(10) + ' UserID: ' +  SYSTEM_USER;

    ROLLBACK TRANSACTION; -- should probably check @@TRANCOUNT first!

    EXEC msdb.dbo.sp_send_dbmail 
         @recipients = 'myemail@mycompany.com',
         @body = @b,
         @subject = 'Attempt to Delete an order';

    RAISERROR('Cannot delete order(s)', 16, 1);
  END

  RETURN;
END
GO

score 2 · Answer 2 · answered Feb 07 '12 at 07:47

The most common error about triggers is that they are generally created as they will work once for each deleted record.

But if more than 1 records are deleted with one DELETE statement, the trigger should be able to handle all deleted records as Aaron suggested.

It is important to avoid single value variables in a trigger, developers should build set based solutions within triggers

T-SQL INSTEAD OF DELETE Trigger fires without 'provocation'

2 Answers2