SQL Server 使用触发器（trigger）发送电子邮件

阿牛

　　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 发送电子邮件：
　　步骤一：
　　

　　
exec sp_configure 'show advanced options',1
　　
go
　　
reconfigure;
　　
go
　　

　　
exec sp_configure 'Database Mail XPs',1
　　
go
　　
reconfigure;
　　
go
　　

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

　　
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
　　

　　

　　
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')　　
begin
　　EXEC msdb..sysmail_delete_account_sp @account_name='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   
　　,@enable_ssl = 1        
　　,@account_id = null  
　　

　　PS：如果使用的是QQ邮箱，记得要把参数 @enable_ssl 的值设置为 1 。不然后面会报服务器错误，这个错误搞了我好久，最后终于找到原因了。
　　步骤三：
　　

if exists(SELECT * FROM msdb..sysmail_profile where NAME = N'SendEmailProfile')　　
begin  
　　exec msdb..sysmail_delete_profile_sp @profile_name = 'SendEmailProfile'  
　　
end
　　

　　
exec msdb..sysmail_add_profile_sp   
　　@profile_name = 'SendEmailProfile',   
　　@description = '数据库发送邮件配置文件',   
　　@profile_id = NULL        
　　

　　步骤四：
　　

　　
exec msdb..sysmail_add_profileaccount_sp   
　　@profile_name = 'SendEmailProfile',   
　　@account_name = 'test',   
　　@sequence_number = 1   
　　

　　好了，到这里 sql 发送邮件的配置就基本结束了。下面创建一个触发器实现用户注册成功后，发送邮件给用户。
　　首先创建一个表：
　　

　　
create table T_User
　　
(

　　
     UserID        int        not null   >　　
     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'   
　　
     end
　　
go
　　

　　下面就来测试一下吧：
　　

　　
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　　

　　
create user dba for login dba   
　　
go  
　　
exec dbo.sp_addrolemember @rolename   = 'DatabaseMailUserRole',  
　　@membername = 'dba'  
　　
go  
　　

　　

use msdb　　

go　　
exec sysmail_add_principalprofile_sp @principal_name = 'dba',        
　　@profile_name = 'SendEmailProfile',  
　　@is_default = 1   
　　

　　如果所使用的登陆数据库会话的角色没有发送数据库邮件的权限，那么也会报错。所以上面是赋予角色发送数据库邮件的权限 sql 语句。
　　参考：
　　http://blog.csdn.net/abclm/article/details/6341843