February 25, 2016 at 6:20 am
I need to create a process that will be running all the time and monitor one table and if some criteria met email to a group.
What is the best way to accomplish this?
Need to setup alerts to different groups on table insert on different criteria.
Environment: SQL Server 2014
Many thanks.
February 25, 2016 at 7:25 am
it depends on what you mean by "monitor"
DDL changes; database scoped trigger, or maybe an extended event
data changes: trigger on the table itself, or maybe an extended event
Access(SELECT ) detection: maybe an extended event or an old style SQL trace.
Lowell
February 25, 2016 at 8:13 am
In addition to DDL or DML trigger (I am betting the latter), you should NOT send an email from the trigger!! Set up and use SQL Server Service Broker for that so it is asynchronous. Otherwise you will slow down the actual transaction too much.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2016 at 8:13 am
Need to setup alerts to different groups on table insert on different criteria.
February 25, 2016 at 8:20 am
SQL!$@w$0ME (2/25/2016)
Need to setup alerts to different groups on table insert on different criteria.
data on insert is probably going to be an insert trigger on the table itself.
Since database mail IS a service brokered asyncronous solution, the only thing to worry about is permissions: you need to do is make sure is it runs under the context of a user with the role of DatabaseMailUserRole in the msdb database.
here's an old example that sends an email on insert; it is not handling the permissions issue i identified it is assuming that every calling user is also a user in msdb, and is also in the role.
CREATE TABLE WHATEVER(
WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30)
)
GO
ALTER TRIGGER TR_WHATEVER_NOTIFICATIONS
ON WHATEVER FOR INSERT
AS
BEGIN
SET NOCOUNT ON
--gather the information, making sure you get it from the INSERTED virtual table, and not the full table
DECLARE @CAPTUREDSTRING VARCHAR(max)
--In this example i want a comma delimited list of important facts about what was inserted.
--using the xml technique to make my comma delimited string.
SELECT @CAPTUREDSTRING = [Skills]
FROM (
SELECT TOP 1 GETDATE()As TheDate,stuff(( SELECT ',' + DESCRIP
FROM INSERTED s2
--WHERE s2.WHATEVERID= s1.WHATEVERID --- must match GROUP BY below
WHERE 1 = 1
ORDER BY DESCRIP
FOR XML PATH('')
),1,1,'') as [Skills]
FROM INSERTED s1
GROUP BY s1.WHATEVERID --- without GROUP BY multiple rows are returned
ORDER BY s1.WHATEVERID) myAlias
--now email the results.
declare @body1 varchar(4000)
set @body1 = 'New Item Notification on the Whatever Table '
+ CONVERT( VARCHAR( 20 ), GETDATE(), 113 )
+ '
<P> The following new items were inserted into the table:<P>'
+ @CAPTUREDSTRING
+ '
'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Default Mail Account',
@recipients='lowell@somedomain.com',
@subject = 'New Item Notification',
@body = @body1,
@body_format = 'HTML'
END --TRIGGER
GO
INSERT INTO WHATEVER(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI'
Lowell
February 25, 2016 at 9:59 am
Thanks a lot Lowell.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply