• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    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 发送邮件的配置就基本结束了。下面创建一个触发器实现用户注册成功后,发送邮件给用户。

    首先创建一个表:

    -- 创建一个表
     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  -- 对于角色所拥有的配置文件的顺序,一个数据库角色可以有多个配置文件的权限

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

    以上所述是小编给大家介绍的SQL Server 使用触发器(trigger)发送电子邮件,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!

    您可能感兴趣的文章:
    • 关于喜忧参半的SQL Server触发器详解
    • 利用SQL Server触发器实现表的历史修改痕迹记录
    • SQLSERVER对加密的存储过程、视图、触发器进行解密(推荐)
    • SQL Server实现用触发器捕获DML操作的会话信息【实例】
    • SQL Server:触发器实例详解
    • SqlServer触发器详解
    • SqlServer实现类似Oracle的before触发器示例
    • SQL SERVER中各类触发器的完整语法及参数说明
    • SQL SERVER 触发器介绍
    • SQL Server误区30日谈 第4天 DDL触发器就是INSTEAD OF触发器
    • SQL Server 触发器详情
    上一篇:SQL Server实时同步更新远程数据库遇到的问题小结
    下一篇:SQL 获取所有上级的实现方法
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    SQL Server 使用触发器(trigger)发送电子邮件步骤详解 SQL,Server,使用,触发器,trigger,