首页 > 代码库 > SQL Server 使用触发器（trigger）发送电子邮件
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 发送电子邮件：
步骤一：
-- 启用 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 发送邮件的配置就基本结束了。下面创建一个触发器实现用户注册成功后，发送邮件给用户。
首先创建一个表：
1 -- 创建一个表 2 create table T_User 3 ( 4 UserID int not null identity(1,1) primary key, 5 UserNo nvarchar(64) not null unique, 6 UserPwd nvarchar(128) not null , 7 UserMail nvarchar(128) null 8 ) 9 go
然后创建一个 insert 类型的 after 触发器：
1 create trigger NewUser_Send_Mail 2 on T_User 3 after insert 4 as 5 declare @UserNo nvarchar(64) 6 declare @title nvarchar(64) 7 declare @content nvarchar(320) 8 declare @mailUrl nvarchar(128) 9 10 declare @count int 11 12 select @count=COUNT(1) from inserted 13 select @UserNo=UserNo,@mailUrl=UserMail from inserted 14 15 if(@count>0) 16 begin 17 set @title=‘注册成功通知‘ 18 set @content=‘欢迎您‘+@UserNo+‘！您已成功注册！通知邮件，请勿回复！‘ 19 20 exec msdb.dbo.sp_send_dbmail @profile_name=‘SendEmailProfile‘, -- 邮件配置文件名称 21 @recipients=@mailUrl, -- 邮件发送地址 22 @subject=@title, -- 邮件标题 23 @body=@content, --邮件内容 24 @body_format=‘text‘ -- 邮件内容的类型，text 为文本，还可以设置为 html 25 end 26 go
下面就来测试一下吧：
1 -- 新添加一条数据，用以触发 insert 触发器 2 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 -- 对于角色所拥有的配置文件的顺序，一个数据库角色可以有多个配置文件的权限
如果所使用的登陆数据库会话的角色没有发送数据库邮件的权限，那么也会报错。所以上面是赋予角色发送数据库邮件的权限 sql 语句。
参考：
http://blog.csdn.net/abclm/article/details/6341843
SQL Server 使用触发器（trigger）发送电子邮件
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。