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

2024-02-25 06:45:03 164

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

步骤一：

--启用sqlserver邮件的功能
execsp_configure'showadvancedoptions',1
go
reconfigure;
go
execsp_configure'DatabaseMailXPs',1
go
reconfigure;
go

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

--启用sqlserver邮件的功能
execsp_configure'showadvancedoptions',1
go
reconfigurewithoverride
go
execsp_configure'DatabaseMailXPs',1
go
reconfigurewithoverride
go

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

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

步骤二：

ifexists(SELECT*FROMmsdb..sysmail_accountWHERENAME='test')--判断邮件账户名为test的账户是否存在
begin
EXECmsdb..sysmail_delete_account_sp@account_name='test'--删除邮件账户名为test的账户
end
execmsdb..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。不然后面会报服务器错误，这个错误搞了我好久，最后终于找到原因了。

步骤三：

ifexists(SELECT*FROMmsdb..sysmail_profilewhereNAME=N'SendEmailProfile')--判断名为SendEmailProfile的邮件配置文件是否存在
begin
execmsdb..sysmail_delete_profile_sp@profile_name='SendEmailProfile'--删除名为SendEmailProfile的邮件配置文件
end
execmsdb..sysmail_add_profile_sp--添加邮件配置文件
@profile_name='SendEmailProfile',--配置文件名称
@description='数据库发送邮件配置文件',--配置文件描述
@profile_id=NULL--输出参数，返回创建的邮件配置文件的ID

步骤四：

--邮件账户和邮件配置文件相关联
execmsdb..sysmail_add_profileaccount_sp
@profile_name='SendEmailProfile',--邮件配置文件名称
@account_name='test',--邮件账户名称
@sequence_number=1--account在profile中的顺序，一个配置文件可以有多个不同的邮件账户

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

首先创建一个表：

--创建一个表
createtableT_User
(
UserIDintnotnullidentity(1,1)primarykey,
UserNonvarchar(64)notnullunique,
UserPwdnvarchar(128)notnull,
UserMailnvarchar(128)null
)
go

然后创建一个insert类型的after触发器：

createtriggerNewUser_Send_Mail
onT_User
afterinsert
as
declare@UserNonvarchar(64)
declare@titlenvarchar(64)
declare@contentnvarchar(320)
declare@mailUrlnvarchar(128)
declare@countint
select@count=COUNT()frominserted
select@UserNo=UserNo,@mailUrl=UserMailfrominserted
if(@count>0)
begin
set@title='注册成功通知'
set@content='欢迎您'+@UserNo+'！您已成功注册！通知邮件，请勿回复！'
execmsdb.dbo.sp_send_dbmail@profile_name='SendEmailProfile',--邮件配置文件名称
@recipients=@mailUrl,--邮件发送地址
@subject=@title,--邮件标题
@body=@content,--邮件内容
@body_format='text'--邮件内容的类型，text为文本，还可以设置为html
end
go

下面就来测试一下吧：

--新添加一条数据，用以触发insert触发器
insertintoT_User(UserNo,UserPwd,UserMail)values('demo1','123456','1171588826@qq.com')

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

usemsdb
go
select*fromsysmail_allitems--邮件发送情况，可以用来查看邮件是否发送成功
select*fromsysmail_mailitems--发送邮件的记录
select*fromsysmail_event_log--数据库邮件日志，可以用来查询是否报错

usemsdb
go
--为角色名为dba的角色赋予发送数据库邮件的权限
createuserdbaforlogindba
go
execdbo.sp_addrolemember@rolename='DatabaseMailUserRole',
@membername='dba'
go
usemsdb
go
--为角色名为dba的角色赋予配置文件发送邮件的权限
execsysmail_add_principalprofile_sp@principal_name='dba',--角色名称
@profile_name='SendEmailProfile',--配置文件名称
@is_default=1--对于角色所拥有的配置文件的顺序，一个数据库角色可以有多个配置文件的权限

如果所使用的登陆数据库会话的角色没有发送数据库邮件的权限，那么也会报错。所以上面是赋予角色发送数据库邮件的权限sql语句。

以上所述是小编给大家介绍的SQLServer使用触发器（trigger）发送电子邮件，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！

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

热门推荐

随机推荐