sql-server-2008 - T-SQL INSTEAD OF DELETE 触发器在没有 'provocation' 的情况下触发

Question

我有以下 INSTEAD OF DELETE 触发器，它在回滚任何删除尝试时发送一封电子邮件。

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

当我尝试从 ORDERS 表中删除订单时，这会起作用。但是，我不明白为什么这个触发器会定期发送空的电子邮件。据我所知，没有明显的删除订单的尝试。还有什么可能导致此类空白电子邮件？

Answer 1

此触发器处理多行删除，并且如果有人触发不删除任何行的删除语句，也不会向您发送无意义的电子邮件:

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