SQL Server 使用觸發器（trigger）發送電子郵件

sql 使用系統存儲過程 sp_send_dbmail 發送電子郵件語法：參數參考地址：https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transac ...

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 = '[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 * 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','[email protected]')

執行上面的語句之後，大概兩三秒鐘，就會收到郵件了（如果沒有出現錯誤的話）。如果沒有收到郵件可以使用下麵的語句查看郵件發送情況。

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