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

sql 使用系统存储过程 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 ]' ]

									 [ , [ @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 ]

									 [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

参数参考地址：https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql

下面开始配置 sql 发送电子邮件：

步骤一：

				?

									-- 启用 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 = '980095349@qq.com'   -- 发件人邮件地址 

									    ,@display_name = 'Brambling'    -- 发件人姓名 

									    ,@replyto_address = null    -- 回复地址

									    ,@description = null      -- 邮件账户描述

									    ,@mailserver_name = 'smtp.qq.com'  -- 邮件服务器地址 

									    ,@mailserver_type = 'SMTP'    -- 邮件协议

									    ,@port = 25         -- 邮件服务器端口 

									    ,@username = '980095349@qq.com'    -- 用户名 

									    ,@password = 'xxxxxx'   -- 密码 

									    ,@use_default_credentials = 0  -- 是否使用默认凭证，0为否，1为是

									    ,@enable_ssl = 1    -- 是否启用 ssl 加密，0为否，1为是

									    ,@account_id = null -- 输出参数，返回创建的邮件账户的ID

PS：如果使用的是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    -- 输出参数，返回创建的邮件配置文件的ID

步骤四：

				?

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

									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() 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','123456','1171588826@qq.com')

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

				?

									use msdb

									go

									select * from sysmail_allitems    -- 邮件发送情况，可以用来查看邮件是否发送成功

									select * from sysmail_mailitems    -- 发送邮件的记录

									select * from sysmail_event_log      -- 数据库邮件日志，可以用来查询是否报错

				?

									use msdb 

									go

									--为角色名为 dba 的角色赋予发送数据库邮件的权限

									create user dba for login dba  

									go 

									exec dbo.sp_addrolemember @rolename  = 'DatabaseMailUserRole', 

									             @membername = 'dba'

									go 

									use msdb 

									go 

									  --为角色名为 dba 的角色赋予配置文件发送邮件的权限

									exec sysmail_add_principalprofile_sp @principal_name = 'dba',    -- 角色名称

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

									                   @is_default = 1  -- 对于角色所拥有的配置文件的顺序，一个数据库角色可以有多个配置文件的权限