根据每个更新的特定字段发送电子邮件 [英] Send email based on each specific field that is updated

问题描述

表
上的SQL Server 2017触发器我正在运行以下触发器，并且它可以正常工作。但是，我想根据要更新的特定字段发送不同的电子邮件部分。我基本上想使用SQL Server发送邮件代码将不同的字段发送给其他用户。我想知道是否有某种方式可以合并该语句？希望我能正确回答这个问题！我的SQL就像AT& T商业广告一样...好吧！因此，如果可以帮助，请提供代码。例如：

如果姓氏已更新，则发送/执行此操作：

  EXEC msdb.dbo.sp_send_dbmail 
 @profile_name ='Echo System'，
 @recipients ='sample1.com'，
 @subject ='发送电子邮件给sample1'，
 @body ='请启动后台检查'
  

如果姓氏已更新，则发送/执行此操作

< preclass = lang-sql prettyprint-override> EXEC msdb.dbo.sp_send_dbmail
@profile_name ='Echo System'，
@recipients ='sample2.com'，
@subject ='发送电子邮件给sample2'，
@body ='请开始背景检查'


发送/执行此国籍更新。

  EXEC msdb.dbo.sp_send_dbmail 
 @profile_name = '回声系统'，
 @recipients ='sample3.com'，
 @subject ='发送电子邮件给sample3'，
 @body ='请启动bac kground check'
  

  ALTER触发器[dbo]。 
上的[updatePerson] [dbo]。[person] 
用于更新
，因为
 
声明@personId int; 
声明@firstname varchar（50）; 
声明@lastname varchar（50）; 
声明@nationality varchar（100）; 
声明@activity varchar（100）; 
 
从插入的s中选择@personId = s.personId; 
从插入的s中选择@firstname = s.firstname; 
从插入的s中选择@lastname = s.lastname; 
从插入的s中选择@nationality = s.nationality; 
 
如果更新（名字）
设置@activity ='更新的人的名字'
如果更新（名字）
设置@activity ='更新的人的姓氏'
（如果更新（国籍）
已设置@activity ='已更新人员的国籍'
 
 
 
 EXEC msdb.dbo.sp_send_dbmail 
 @profile_name ='回声系统，
 @recipients ='[e-mail]'，
 @subject ='遵循您需要采取的采样操作。 http：//test.aspx'，
 @body ='请启动后台检查'
  

解决方案

以下代码显示了如何通过遍历 Inserted 伪表来处理多行。以及如何检测值的更改。

就每种更改类型发送不同的电子邮件而言，您已经有一个 IF 语句来分配您的 @activity ，只需将其扩展以设置要通过电子邮件发送的值即可。

但是如上所述，可以通过触发是一个非常糟糕的主意。相反，您应该将要通过电子邮件发送的数据插入队列表，然后让另一个服务处理该队列。

  ALTER触发器[dbo]。[updatePerson [人] 
用于更新
，因为
开始
设置为nocount； 
 
声明@personId int，@ firstname varchar（50），@ lastname varchar（50），@ nationality varchar（100），@ activity varchar（100）
，@ firstNameModified位，@ lastNameModified位，@nationalityModified位
，@profile_name sysname，@recipients varchar（max），@subject nvarchar（255），@body nvarchar（max）; 
 
选择
 I.personId 
，convert（bit，当coalcece（I.firstname，''）<> Coalesce（D.firstname，''）时的情况1 else 0 end）firstnameModified 
，I.firstname 
，convert（bit，case coalesce（I.lastname，''）<> Coalesce（D.lastname，''）的情况下，则为1 0 end）lastnameModified 
，I.lastname 
，convert（bit，case coalesce（I.nationality，''）<> Coalesce（D.nationality，''）的情况，则1 else 0 end ）nationalityModified 
，I.nationality 
从Inserted I 
到#updatePerson_temp 
-因为它是一个'update'触发器，所以我们可以内部联接
内部联接Deleted D.personId = I.personId 
其中结点（I.firstname，''）<> coalesce（D.firstname，’’）
或coalesce（I.lastname，’’）<> Coalesce（D.lastname，’’）
或Coalesce（I.nationality，’’）<>合并（D.nationality，’）； 
 
存在时（从#updatePerson_temp中选择1）开始
-获取处理
的第一条记录选择前1个@personId = personId 
，@firstnameModified = firstnameModified 
，@firstname = firstname 
，@lastnameModified = firstnameModified 
，@lastname = lastname 
，@ nationalityModified = nationalityModified 
，@ nationality =国籍
 updatePerson_temp; 
 
-以下假设每个记录仅更改一次，如果@firstnameModified = 1开始，则进行修改以适合
开始
 select @activity ='Updated person firstname'
，@ profile_name ='回声系统'
，@recipients ='sample1.com'
，@subject ='发送电子邮件给Sample1'
，@body ='请启动后台检查'; 
结尾；否则，如果@lastnameModified = 1开始
 select @activity ='更新的人的姓氏'
，@profile_name ='回声系统'
，@recipients ='sample2.com'
， @subject ='发送电子邮件给Sample2'
，@ body ='请开始背景检查'; 
结尾；否则，如果@nationalityModified = 1开始
 select @activity ='更新后的国籍'
，@profile_name ='回声系统'
，@recipients ='sample3.com'
， @subject ='发送电子邮件给Sample2'
，@ body ='请开始背景检查'; 
结尾； 
 
-而不是在此处发送电子邮件，而是将其排队等待以后
 exec msdb.dbo.sp_send_dbmail 
 @profile_name = @profile_name 
，@recipients = @recipients 
，@subject = @subject 
，@body = @body; 
 
-删除已处理的记录
从#updatePerson_temp中删除，其中personId = @personId; 
结尾； 
结尾； 
  

SQL Server 2017 Trigger on Table I have the following trigger running, and it works. However, I want to send a different email section based on specific fields that get updated. I basically want to use the SQL Server Send Mail code to send to a different user for a different field. I was wondering if there was some way to combine the statement? I hope I'm asking this correctly! My SQL is as the AT&T commercial...JUST OK! So if you can help please provide the code if this in fact can be done. For example:

Send/do this if firstname updated:

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = 'sample1.com',
      @subject = 'Send email for sample1',
      @body = 'Please start a background check'

Send/do this if last name updated

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = 'sample2.com',
      @subject = 'Send email for sample2',
      @body = 'Please start a background check'

Send/do this is nationality updated.

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = 'sample3.com',
      @subject = 'Send email for sample3',
      @body = 'Please start a background check'

ALTER trigger [dbo].[updatePerson] on
[dbo].[person]
for update
as
 
      declare @personId int;
      declare @firstname varchar(50);
      declare @lastname varchar(50);
      declare @nationality varchar(100);
      declare @activity varchar(100);
 
      select @personId = s.personId from inserted s;
      select @firstname = s.firstname from inserted s;
      select @lastname = s.lastname from inserted s;
      select @nationality = s.nationality from inserted s;
 
      if update(firstname)
                  set @activity = 'Updated person firstname'
      if update(lastname)
                  set @activity = 'Updated person lastname'
      if update(nationality)
                  set @activity = 'Updated person nationality'
 
      
 
      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = '[e-mail]',
      @subject = ' follow the sampleaction you need to take. http://test.aspx',
      @body = 'Please start a background check'

解决方案

The following code shows how to handle multiple rows by looping through the Inserted pseudo-table. And how to detect changes in values.

As far as sending a different email per change type, you already had an IF statement to assign your @activity so just expand that to set the values to be emailed.

But as has been commented above, sending emails in a trigger is a very bad idea. Instead you should insert the data to be emailed into a queue table and then have another service processing the queue.

ALTER trigger [dbo].[updatePerson] on
[dbo].[person]
for update
as
begin
    set nocount on;
     
    declare @personId int, @firstname varchar(50), @lastname varchar(50), @nationality varchar(100), @activity varchar(100)
      , @firstNameModified bit, @lastNameModified bit, @nationalityModified bit
      , @profile_name sysname, @recipients varchar(max), @subject nvarchar(255), @body nvarchar(max);

    select
        I.personId
        , convert(bit, case when coalesce(I.firstname,'') <> coalesce(D.firstname,'') then 1 else 0 end) firstnameModified
        , I.firstname
        , convert(bit, case when coalesce(I.lastname,'') <> coalesce(D.lastname,'') then 1 else 0 end) lastnameModified
        , I.lastname
        , convert(bit, case when coalesce(I.nationality,'') <> coalesce(D.nationality,'') then 1 else 0 end) nationalityModified
        , I.nationality
    into #updatePerson_temp
    from Inserted I
    -- Because its an 'update' trigger we can inner join
    inner join Deleted D on D.personId = I.personId
    where coalesce(I.firstname,'') <> coalesce(D.firstname,'')
    or coalesce(I.lastname,'') <> coalesce(D.lastname,'')
    or coalesce(I.nationality,'') <> coalesce(D.nationality,'');

    while exists (select 1 from #updatePerson_temp) begin
        -- Get first record to handle
        select top 1 @personId = personId
            , @firstnameModified = firstnameModified
            , @firstname = firstname
            , @lastnameModified = firstnameModified
            , @lastname = lastname
            , @nationalityModified = nationalityModified
            , @nationality = nationality
        from #updatePerson_temp;

        -- Following assumes only one change per record, modify to suit
        if @firstnameModified = 1 begin
            select @activity = 'Updated person firstname'
                , @profile_name = 'Echo System'
                , @recipients = 'sample1.com'
                , @subject = 'Send Email for Sample1'
                , @body = 'Please start a background check';
        end; else if @lastnameModified = 1 begin
            select @activity = 'Updated person lastname'
                , @profile_name = 'Echo System'
                , @recipients = 'sample2.com'
                , @subject = 'Send Email for Sample2'
                , @body = 'Please start a background check';
        end; else if @nationalityModified = 1 begin
            select @activity = 'Updated person nationality'
                , @profile_name = 'Echo System'
                , @recipients = 'sample3.com'
                , @subject = 'Send Email for Sample2'
                , @body = 'Please start a background check';
        end;

        -- Instead of sending the email here, queue it for later
        exec msdb.dbo.sp_send_dbmail
            @profile_name = @profile_name
            , @recipients = @recipients
            , @subject = @subject
            , @body = @body;

        -- Delete handled record
        delete from #updatePerson_temp where personId = @personId;
    end;
end;

这篇关于根据每个更新的特定字段发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！