Share SQL Server using trigger to send email example code

This article gives you a step-by-step detailed introduction to how SQL Server uses triggers to send emails. Friends who need it can refer to

sql uses the system stored procedure sp_send_dbmail to send emails syntax:

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 ]

Start configuring sql to send email:

Step 1:

-- 启用 sql server 邮件的功能
exec sp_configure 'show advanced options',1
go
reconfigure;
go
exec sp_configure 'Database Mail XPs',1
go
reconfigure;
go

If the above If the statement execution fails, you can also use the following statement.

-- 启用 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

Use the following statement to check whether the database mail function is successfully enabled and the database configuration information:

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

Step 2:

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: If using It is a QQ mailbox. Remember to set the value of parameter @enable_ssl to 1. Otherwise, a server error will be reported later. This error bothered me for a long time, and I finally found the reason.

Step three:

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

Step four:

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

Okay, here the configuration of sql sending email is basic it's over. Create a trigger below to send an email to the user after the user successfully registers.

First create a table:

-- 创建一个表
 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

Then create an insert type after trigger:

 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

Let’s test it now:

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

Execute After the above statement, you will receive the email in about two or three seconds (if no errors occur). If you do not receive the email, you can use the following statement to check the email delivery status.

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

If the role used to log in to the database session does not have the permission to send database emails, an error will also be reported. So the above is the sql statement that gives the role the permission to send database emails.

The above is the detailed content of Share SQL Server using trigger to send email example code. For more information, please follow other related articles on the PHP Chinese website!