邮件功能 _for 触发器 pie smt 一个数 一个数据库 查看数据库
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 邮件的功能
- execsp_configure'show advanced options',1
- go
- reconfigure;
- go
- execsp_configure'Database Mail XPs',1
- go
- reconfigure;
- go
如果上面的语句执行失败,也可以使用下面的语句。
- -- 启用 sql server 邮件的功能
- execsp_configure'show advanced options',1
- go
- reconfigure with override
- go
- execsp_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
- wherenamelike '%mail%'
步骤二:
- if exists(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 = '[email protected]' -- 发件人邮件地址 ,@display_name = 'Brambling' -- 发件人姓名 ,@replyto_address = null -- 回复地址,@description = null -- 邮件账户描述,@mailserver_name = 'smtp.qq.com' -- 邮件服务器地址 ,@mailserver_type = 'SMTP' -- 邮件协议,@port = 25 -- 邮件服务器端口 ,@username = '[email protected]' -- 用户名 ,@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 * 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
步骤四:
- -- 邮件账户和邮件配置文件相关联
- 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 (
- 4UserIDint not null identity(1,1)primary key,
- 5UserNonvarchar(64)not null unique,
- 6UserPwdnvarchar(128)not null ,
- 7UserMailnvarchar(128)null
- 8 )
- 9 go
然后创建一个 insert 类型的 after 触发器:
- 1 create trigger NewUser_Send_Mail
- 2 on T_User
- 3afterinsert
- 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=UserMailfrom inserted
- 14
- 15 if(@count>0)
- 16 begin
- 17 set @title='注册成功通知'
- 18 set @content='欢迎您'+@UserNo+'!您已成功注册!通知邮件,请勿回复!'
- 19
- 20 execmsdb.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 intoT_User(UserNo,UserPwd,UserMail)values('demo1','123456','[email protected]')
执行上面的语句之后,大概两三秒钟,就会收到邮件了(如果没有出现错误的话)。如果没有收到邮件可以使用下面的语句查看邮件发送情况。
- use msdb
- go
- select * fromsysmail_allitems-- 邮件发送情况,可以用来查看邮件是否发送成功
- select * fromsysmail_mailitems-- 发送邮件的记录
- select * fromsysmail_event_log-- 数据库邮件日志,可以用来查询是否报错
- use msdb
- go
- --为角色名为 dba 的角色赋予发送数据库邮件的权限
- create userdbafor login dba
- go
- execdbo.sp_addrolemember@rolename = 'DatabaseMailUserRole',
- @membername = 'dba'
- go
- use msdb
- go
- --为角色名为 dba 的角色赋予配置文件发送邮件的权限
- execsysmail_add_principalprofile_sp@principal_name = 'dba',-- 角色名称
- @profile_name = 'SendEmailProfile',-- 配置文件名称
- @is_default = 1 -- 对于角色所拥有的配置文件的顺序,一个数据库角色可以有多个配置文件的权限
如果所使用的登陆数据库会话的角色没有发送数据库邮件的权限,那么也会报错。所以上面是赋予角色发送数据库邮件的权限 sql 语句。
参考:
http://blog.csdn.net/abclm/article/details/6341843
SQL Server 使用触发器(trigger)发送电子邮件
来源: http://www.bubuko.com/infodetail-2034847.html