Using SQL Server Management Studio 2012
I would like an email sent to me every time a certain field in my table is greater than zero. This is not something that happens often but I would like to be alerted when it does.
I only want to be emailed the new insert not any of the previous. In the example below, taking column 3 as the field of interest, Harry is the newest insert and column 3 is greater than zero. This is when I would like to be alerted, as you can see Jack's is greater than zero too but lets assume this is an older entry so I don't want this to appear on the email.
Name Department Column3 Column4 Column5
Harry HR 2 ABC DEF
James Sport 0 ABC DEF
Jack Finance 1 ABC DEF
Relatively new with the email function in SQL but understand the basics below
use Database
go
begin
Execute msdb.dbo.sp_send_dbmail
@recipients = 'emailaddress',
@query = 'select Name, department, Column3 from mytable
where Column3 > 0 '
End
A trigger that checks the values from the INSERTED table and executes sp_send_dbmail if this values exceeds the specified number can be used for this. An example trigger is below. Using sp_send_dbmail will require properly configuring Database Mail if you haven't already. More details on Database Mail and setting this up can be found here. A cursor is used to send an email for each new row that was added/updated. Since you only want to data from new or updated rows, this is obtained from the INSERTED table instead of a query, then used to build the @body parameter of sp_send_dbmail.
Note that CONCAT is used for a safeguard for nulls, as if multiple strings are added together with the + operator and one is null, the entire concatenated string will be null. However if CONCAT is used the non-null strings will still be preserved. A table variable is used to initially obtain the values from the INSERTED table which will later be feed into the cursor. The INSERTED table will capture values from both INSERT and UPDATE operations. From your question, it seems as though you still wanted to new value added to the table, so an AFTER trigger was used.
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