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 = '[email protected]',
                         @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?

Answer 1

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 = '[email protected]',
         @body = @b,
         @subject = 'Attempt to Delete an order';

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

  RETURN;
END
GO

Answer 2

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