时间戳

首页 > 解决方案 > 字段满足特定条件时的电子邮件提醒

sql-server-2012 - 字段满足特定条件时的电子邮件提醒

问题描述

使用 SQL Server Management Studio 2012

每次我表中的某个字段大于零时，我都希望向我发送一封电子邮件。这不是经常发生的事情，但我希望在它发生时得到提醒。

我只想通过电子邮件发送新的插入内容，而不是以前的任何内容。在下面的示例中，将第 3 列作为感兴趣的字段，Harry 是最新的插入，第 3 列大于零。这是我希望收到警报的时候，因为您可以看到 Jack 的值也大于零，但我们假设这是一个较旧的条目，所以我不希望它出现在电子邮件中。

Name     Department      Column3   Column4   Column5
Harry    HR                 2         ABC      DEF
James    Sport              0         ABC      DEF
Jack     Finance            1         ABC      DEF

SQL 中的电子邮件功能相对较新，但了解以下基础知识

use Database
go


begin
Execute msdb.dbo.sp_send_dbmail



 @recipients = 'emailaddress',
 @query = 'select Name, department, Column3 from    mytable

 where Column3 > 0 '



End

标签： sql-server-2012ssms

解决方案

检查INSERTED表中的值并在该值超过指定数量时执行的触发器sp_send_dbmail可用于此目的。下面是一个示例触发器。sp_send_dbmail如果您还没有使用，则需要正确配置数据库邮件。可以在此处找到有关数据库邮件和设置的更多详细信息。游标用于为添加/更新的每个新行发送电子邮件。由于您只想从新行或更新行中获取数据，因此这是从表INSERTED而不是查询中获取的，然后用于构建. 请注意，它用于保护空值，就像多个字符串与运算符一起添加并且一个为空一样，整个连接的字符串将为空。然而，如果@bodysp_send_dbmailCONCAT+CONCAT使用非空字符串仍将被保留。表变量用于最初从INSERTED表中获取值，该值稍后将输入游标。该INSERTED表将从INSERT和UPDATE操作中捕获值。从您的问题来看，您似乎仍然希望向表中添加新值，因此使用了AFTER触发器。

CREATE TRIGGER dbo.TestTrigger
ON YourDatabase.YourSchema.YourTable
AFTER INSERT, UPDATE
AS  
BEGIN

DECLARE @NewColumn3 INT;
DECLARE @NewName VARCHAR(25);
DECLARE @NewDepartment VARCHAR(25);
DECLARE @Tab Table (COLUMN3 INT, NAME VARCHAR(25), DEPARTMENT VARCHAR(25));
DECLARE @Message VARCHAR(2000);

INSERT INTO @Tab
SELECT COLUMN3, NAME, DEPARTMENT
FROM INSERTED

IF EXISTS((SELECT COLUMN3 FROM @Tab where COLUMN3 > 0))
BEGIN

--make sure to only add data from rows where COLUMN3 > 0
DECLARE EmailCursor CURSOR FOR 
SELECT COLUMN3, NAME, DEPARTMENT FROM @Tab WHERE COLUMN3 > 0

OPEN EmailCursor

FETCH NEXT FROM EmailCursor
INTO @NewVal, @NewName, @NewDepartment

--while there are still rows
WHILE (@@FETCH_STATUS = 0)
BEGIN

--use CONCAT to avoid null value voiding message
SET @Message = CONCAT('Name ', @NewName, ' from department ', @NewDepartment, ' added a value of ', @NewVal) 

   EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'YourProfileName',  
        @recipients = 'EmailAddress@domain.com',  
        @body = @Message,
        @subject = 'Email Subject'; 

FETCH NEXT FROM EmailCursor 
INTO  @NewVal, @NewName, @NewDepartment

END

CLOSE EmailCursor

DEALLOCATE EmailCursor

END

END

推荐阅读