根据每个更新的特定字段发送电子邮件

本文介绍了根据每个更新的特定字段发送电子邮件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 表上的SQL Server 2017触发器我正在运行以下触发器，并且它可以正常工作。但是，我想根据要更新的特定字段发送不同的电子邮件部分。我基本上想使用SQL Server发送邮件代码将不同的字段发送给其他用户。我想知道是否有某种方式可以合并该语句？希望我能正确回答这个问题！我的SQL就像AT& T商业广告一样...好吧！因此，如果可以帮助，请提供代码。例如： 如果姓氏已更新，则发送/执行此操作： EXEC msdb.dbo.sp_send_dbmail @profile_name ='Echo System'， @recipients ='sample1.com'， @subject ='发送电子邮件给sample1'， @body ='请启动后台检查' 如果姓氏已更新，则发送/执行此操作 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 ='[email protected]'， @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 TableI 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 updateas 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 = '[email protected]', @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 updateasbegin 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; 这篇关于根据每个更新的特定字段发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！