SQL Server 使用触发器（trigger）发送电子邮件步骤详解

admin

2024年3月30日 16:35 本文热度 628

本文将分步骤给大家详细介绍下SQL Server 使用触发器（trigger）发送电子邮件的方法，需要的朋友可以参考下，记得收藏，点赞！

使用系统存储过程 sp_send_dbmail 向指定的收件人发送电子邮件。消息可能包括查询结果集、文件附件或两者。当邮件成功放入数据库邮件队列时，sp_send_dbmail 将返回邮件的mailitem_id。此存储过程位于msdb数据库中。

使用系统存储过程 sp_send_dbmail 发送电子邮件语法如下：

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
      [ , [ @recipients = ] 'recipients [ ; ...n ]' ]
      [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
      [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
      [ , [ @from_address = ] 'from_address' ]
      [ , [ @reply_to = ] 'reply_to' ]
      [ , [ @subject = ] 'subject' ]
      [ , [ @body = ] 'body' ]
      [ , [ @body_format = ] 'body_format' ]
      [ , [ @importance = ] 'importance' ]      [ , [ @sensitivity = ] 'sensitivity' ]
      [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
      [ , [ @query = ] 'query' ]
      [ , [ @execute_query_database = ] 'execute_query_database' ]
      [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
      [ , [ @query_attachment_filename = ] query_attachment_filename ]
      [ , [ @query_result_header = ] query_result_header ]
      [ , [ @query_result_width = ] query_result_width ]
      [ , [ @query_result_separator = ] 'query_result_separator' ]
      [ , [ @exclude_query_output = ] exclude_query_output ]
      [ , [ @append_query_error = ] append_query_error ]
      [ , [ @query_no_truncate = ] query_no_truncate ]
      [ , [ @query_result_no_padding = ] @query_result_no_padding ]
      [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

下面开始配置如何使用 sql 发送电子邮件：

第一步：启用SQL Server邮件功能

-- 启用 sql server 邮件的功能
exec sp_configure 'show advanced options',1
go
reconfigure;
go
exec sp_configure 'Database Mail XPs',1
go
reconfigure;
go

如果上面的语句执行失败，也可以使用下面的语句，自行选择。

-- 启用 sql server 邮件的功能
exec sp_configure 'show advanced options', 1
go
reconfigure with override
go
exec sp_configure 'Database Mail XPs', 1
go
reconfigure with override
go

执行结果如图所示

使用下面的语句可查看数据库邮件功能是否开启成功和数据库相关配置信息：

-- 查询数据库的配置信息
select * from sys.configurations
-- 查看数据库邮件功能是否开启，value 值为1表示已开启，0为未开启
select name,value,description,
    is_dynamic,is_advanced
from sys.configurations
where name like '%mail%'

第二步：设置相关邮件账户信息参数

if exists(SELECT * FROM msdb..sysmail_account WHERE NAME='test') --判断邮件账户名为 test 的账户是否存在
begin
  EXEC msdb..sysmail_delete_account_sp @account_name='test' -- 删除邮件账户名为 test 的账户
end
exec msdb..sysmail_add_account_sp  --创建邮件账户
    @account_name = 'test'   -- 邮件帐户名称
,@email_address = '656393951@qq.com'   -- 发件人邮件地址
,@display_name = 'SQLMinutes'    -- 发件人姓名
    ,@replyto_address = null    -- 回复地址
    ,@description = null      -- 邮件账户描述
    ,@mailserver_name = 'smtp.qq.com'  -- 邮件服务器地址
    ,@mailserver_type = 'SMTP'    -- 邮件协议
    ,@port = 465         -- 邮件服务器端口
    ,@username = '656393951@qq.com'    -- 用户名
    ,@password = 'xxxxxxxx'   -- 密码
    ,@use_default_credentials = 0  -- 是否使用默认凭证，0为否，1为是
    ,@enable_ssl = 1    -- 是否启用 ssl 加密，0为否，1为是
    ,@account_id = null -- 输出参数，返回创建的邮件账户的ID

注意：如果使用的是QQ邮箱，记得要把参数 @enable_ssl 的值设置为 1 。不然后面会报服务器错误（前人经验之谈）。

第三步：添加配置文件相关信息

if exists(SELECT * FROM msdb..sysmail_profile where NAME = N'SendEmailProfile') --判断名为 SendEmailProfile 的邮件配置文件是否存在
begin
   exec msdb..sysmail_delete_profile_sp @profile_name = 'SendEmailProfile' --删除名为 SendEmailProfile 的邮件配置文件
end
exec msdb..sysmail_add_profile_sp  -- 添加邮件配置文件
   @profile_name = 'SendEmailProfile',  -- 配置文件名称
   @description = '数据库发送邮件配置文件',  -- 配置文件描述
   @profile_id = NULL    -- 输出参数，返回创建的邮件配置文件的IDD

第四步：邮件账户和邮件配置文件相关联

-- 邮件账户和邮件配置文件相关联

 exec msdb..sysmail_add_profileaccount_sp

     @profile_name = 'SendEmailProfile',  -- 邮件配置文件名称

     @account_name = 'test',  -- 邮件账户名称

     @sequence_number = 1  -- account 在 profile 中的顺序，一个配置文件可以有多个不同的邮件账户

配置到现在 sql 发送邮件的配置就基本结束了。

下面做个测试，测试邮件是否可以发送成功，创建一个触发器实现用户注册成功后，发送邮件给用户。

第一步：创建一个表

-- 创建一个表 
create table T_User 
(
   UserID    int    not null  identity(1,1) primary key,
   UserNo    nvarchar(64)  not null unique,
   UserPwd    nvarchar(128) not null ,
   UserMail  nvarchar(128)  null
 )
 go

第二步：创建一个 insert 类型的 after 触发器

create trigger NewUser_Send_Mail

 on T_User

 after insert as

   declare @UserNo  nvarchar(64)

   declare @title  nvarchar(64)

   declare @content nvarchar(320)

   declare @mailUrl nvarchar(128)

   declare @count  int

   select @count=COUNT(1) from inserted

   select @UserNo=UserNo,@mailUrl=UserMail from inserted

   if(@count>0)

   begin

     set @title='注册成功通知'

     set @content='欢迎您'+@UserNo+'！您已成功注册！通知邮件，请勿回复！'
      exec msdb.dbo.sp_send_dbmail @profile_name='SendEmailProfile',  -- 邮件配置文件名称

                   @recipients=@mailUrl,    -- 邮件发送地址

                   @subject=@title,    -- 邮件标题

                   @body=@content,  --邮件内容

                   @body_format='text'  -- 邮件内容的类型，text 为文本，还可以设置为 html

end

go

第三步：插入测试数据，进行测试

-- 新添加一条数据，用以触发 insert 触发器

insert into T_User(UserNo,UserPwd,UserMail) values('demo1','123','1261881008@qq.com')

执行上面的语句之后，大概两三秒钟，就会收到邮件了（如果没有出现错误的话）。如果没有收到邮件可以使用下面的语句查看邮件发送情况。

use msdb
go
select * from sysmail_allitems    -- 邮件发送情况，可以用来查看邮件是否发送成功
select * from sysmail_mailitems    -- 发送邮件的记录
select * from sysmail_event_log      -- 数据库邮件日志，可以用来查询是否报错

该文章在 2024/3/30 16:35:16 编辑过

关键字查询

正在查询...

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。