通过触发器在SQL中发送电子邮件 [英] Send Email in SQL through trigger

问题描述

亲爱的，



我在插入电话时在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  = ' 通知'，
 < span class =code-sdkkeyword> @ 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  = < span class =code-sdkkeyword> @ 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 Table

User-Table
----------
us_No
Us_Name
Us_Password
Us_Email

I want when the customer call assign to particular user take that email address and put it in below trigger.

alter TRIGGER CallInserted
ON [dbo].[CustomerCall]
FOR INSERT
AS
BEGIN
    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'
    END
END
GO

解决方案

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 CallInserted
ON [dbo].[CustomerCall]
FOR INSERT
AS
BEGIN
   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'
   END
END
GO

However, 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中发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！