通过触发器在SQL中发送电子邮件

本文介绍了通过触发器在SQL中发送电子邮件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 亲爱的， 我在插入电话时在CustomerCall上创建了一个触发器。分配给我想要发送电子邮件的特定用户。 以下是工作触发器，仅用于从用户表中获取分配用户电子邮件地址 用户表 ---------- us_No Us_Name Us_Password Us_Email 我希望当客户呼叫分配给特定用户时，将该电子邮件地址放在触发器下方。 alter TRIGGER CallInserted ON [dbo ]。[CustomerCall] FOR INSERT AS BEGIN SET NOCOUNT ON ; IF EXISTS （ SELECT * FROM 已插入） BEGIN EXEC msdb.dbo.sp_send_dbmail //这里 如何获得收件人电子邮件来自 用户 表 @recipients = ' 收件人'， @ profile_name = ' SendEmailCM'， @ subject = ' 通知'， @ body_for mat = ' html'， @body = ' body' END END GO 解决方案 认为CustomerCall表有一个参考列 us_No 指向用户表然后你可以通过加入插入的和用户来获取电子邮件地址。 换句话说，例如： alter TRIGGER CallInserted ON [dbo]。[CustomerCall] FOR INSERT AS BEGIN DECLARE @ us_email varchar （ 100 ）; SET NOCOUNT ON ; IF EXISTS （ SELECT * FROM 已插入） BEGIN SELECT @ us_email = us_email FROM 插入i， user u WHERE u.us_no = i.us_no; EXEC msdb.dbo.sp_send_dbmail @ recipients = @ us_email ， @ profile_name = ' SendEmailCM'， @ subject = ' 通知'， @ body_format = ' html'， @ body = ' body' END END GO 但是，请注意，这只会获取一个电子邮件地址。如果在一个批处理中插入多行，则此触发器将失败，因为inserted将包含多行。 因此我建议使用游标循环插入记录是否应根据每次插入发送单独的电子邮件。 有关游标的更多信息，请参阅 DECLARE CURSOR（Transact-SQL） [ ^ Dear,i created a trigger on CustomerCall when call inserted & Assign to particular user i want to send email.Below is the working trigger only how to get the Assign user email address from User TableUser-Table----------us_NoUs_NameUs_PasswordUs_EmailI want when the customer call assign to particular user take that email address and put it in below trigger.alter TRIGGER CallInsertedON [dbo].[CustomerCall]FOR INSERTASBEGIN SET NOCOUNT ON; IF EXISTS (SELECT * FROM inserted) BEGIN EXEC msdb.dbo.sp_send_dbmail// here how to get receipent email from User table @recipients = 'recipients', @profile_name = 'SendEmailCM', @subject = 'Notification', @body_format = 'html', @body = 'body' ENDENDGO 解决方案 Taken that the CustomerCall table has a reference column us_No pointing to User table then you can fetch the email address by joining the inserted and the user.In other words, something like:alter TRIGGER CallInsertedON [dbo].[CustomerCall]FOR INSERTASBEGIN DECLARE @us_email varchar(100); SET NOCOUNT ON; IF EXISTS (SELECT * FROM inserted) BEGIN SELECT @us_email = us_email FROM inserted i, user u WHERE u.us_no = i.us_no; EXEC msdb.dbo.sp_send_dbmail @recipients = @us_email, @profile_name = 'SendEmailCM', @subject = 'Notification', @body_format = 'html', @body = 'body' ENDENDGOHowever, notice that this fetches only one email address. If multiple rows are inserted in a single batch this trigger would fail because inserted would contain several rows.Because of this I would suggest using a cursor to loop through the inserted records if separate emails should be sent based on each insertion. For more information about cursors, see DECLARE CURSOR (Transact-SQL)[^] 这篇关于通过触发器在SQL中发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！