从触发器发送电子邮件 [英] Send e-mail from a trigger

问题描述

我正在尝试开发邮件触发器。有人可以协助如何实现这一点，以便在使用时插入记录，检查速度字段，以便当插入的值超过100时，邮件将发送到指定的地址。

解决方案

首先你需要设置数据库邮件 - 如果你还没有这样做，这个问题可能有帮助：

• 数据库邮件的脚本设置

然后你需要一个触发器：

  CREATE TRIGGER dbo.whatever 
 ON dbo.wherever 
 FOR INSERT 
 AS 
 BEGIN 
 SET NOCOUNT ON; 
 
如果EXISTS（SELECT 1 FROM inserted WHERE speed> 100）
 BEGIN 
 EXEC msdb.dbo.sp_send_dbmail 
 @recipients ='[e-mail]' ，
 @profile_name ='default'，
 @subject ='有人正在加速'，
 @body ='是的，他们肯定是'。 
 END 
 END 
 GO 
  

现在，你可能说你想要插入的数据实际上被包含在电子邮件中。而您的第一个倾向是声明一些局部变量并将它们从插入中分配 - 这不起作用，因为您的触发器可能会响应多行插入。所以正确的方法是：

  CREATE TRIGGER dbo.whatever 
 ON dbo.wherever 
 FOR INSERT 
 AS 
 BEGIN 
 SET NOCOUNT ON; 
 
 DECLARE @body NVARCHAR（MAX）= N; 
 
 SELECT @body + = CHAR（13）+ CHAR（10）+ RTRIM（some_col）FROM inserted; 
 
如果EXISTS（SELECT 1 FROM inserted WHERE speed> 100）
 BEGIN 
 EXEC msdb.dbo.sp_send_dbmail 
 @recipients ='[e-mail]' ，
 @profile_name ='default'，
 @subject ='至少有一个人正在超速'，
 @body = @body; 
 END 
 END 
 GO 
  

不是从触发器发送电子邮件的大粉丝。即使数据库邮件使用服务代理，因此是异步的，我更倾向于填充队列表，并有一个后台线程，并发送所有相应的电子邮件。

1. 最小化提交触发的外部事务的潜在延迟触发器 - 触发器中的逻辑越复杂，您进行该过程的速度越慢。


2. 由于电子邮件的发送可能不是该行插入的微秒，你可以很容易地摆动后台进程的时间 - 这样避免了非常小心地检查桌子，整整一天，甚至几乎没有必要做任何事情。


3. As @ goodeye指出，将此过程分开可以防止进程的电子邮件部分中的错误干扰原始DML（在其情况下，无效参数为 sp_send_dbmail - 我无意中建议 - 阻止插入）。

I am trying to develop a mail trigger. Could someone assist on how this could be achieved so that when a use inserts a record it check the "speed" field such that when the inserted value exceeds 100, a mail is send to the specified address.

解决方案

First you need to set up database mail - if you haven't done so, this question might help:

• Scripting setup of database mail

Then you need a trigger:

CREATE TRIGGER dbo.whatever
ON dbo.wherever
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM inserted WHERE speed > 100)
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
          @recipients = '[e-mail]', 
          @profile_name = 'default',
          @subject = 'Someone was speeding', 
          @body = 'Yep, they sure were.';
    END
END
GO

Now, you're probably going to say you want data from the insert to be actually be included in the e-mail. And your first inclination is going to be to declare some local variables and assign them from inserted - this doesn't work because your trigger could be responding to a multi-row insert. So the right way to do this is:

CREATE TRIGGER dbo.whatever
ON dbo.wherever
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @body NVARCHAR(MAX) = N'';

    SELECT @body += CHAR(13) + CHAR(10) + RTRIM(some_col) FROM inserted;

    IF EXISTS (SELECT 1 FROM inserted WHERE speed > 100)
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
          @recipients = '[e-mail]', 
          @profile_name = 'default',
          @subject = 'At least one person was speeding', 
          @body = @body;
    END
END
GO

That all said, I am not a big fan of sending e-mail from a trigger. Even though database mail uses service broker and so is asynchronous, I would be much more inclined to populate a queue table, and have a background thread that comes around and sends all of the appropriate e-mails. The twothree nice things about this are:

1. you minimize the potential delays in committing the outer transaction that fired the trigger - the more complicated your logic in the trigger, the slower you make that process.
2. since it is probably not essential that the e-mail is sent the microsecond the row is inserted, you can easily fluctuate the timing of the background process - this avoids having to check the table very minute, all day, when very few times it will ever have to actually do anything.
3. As @goodeye pointed out, keeping this process separate can prevent errors in the e-mail part of the process from interfering with the original DML (in their case, an invalid parameter to sp_send_dbmail - which I inadvertently suggested - prevented the insert).

这篇关于从触发器发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！