July 5, 2014 at 9:31 am
In our SQL Server 2008 database is a table used to track success or failure of the refreshing of 20 reports on a remote server. A status row exists per report and if all are successfully reloaded, 20 unique reports occupy their own row with success flag = 1.
The other metadata of interest for each report is as follows:
,[date_activity]
,[successful_reload]
,[failed_reload]
,[package_execution_id]
,[end_date]
,[memory_usage]
,[elapsed_seconds]
I need to a way to "listen" for these rows to finish being inserted and a way to notify once all 20 rows have a status for that morning, with some detail if for example a report failed reload.
I began to reach for Trigger and database email but see 2 things from googling:
-no advice for how to 'wait' for 20 rows to insert before activating trigger
-advice against using db mail within trigger.
What is the best way to notify end users as soon as 20 rows have inserted into a sql server table?
July 8, 2014 at 7:23 am
Is there a reason something like this wouldn't work?
CREATE TRIGGER CheckReportsFinished ON dbo.ReportStatus
AFTER INSERT
AS
DECLARE @COMPLETEDROWS INT = 0
SELECT @COMPLETEDROWS = COUNT(*) FROM dbo.ReportStatus WHERE [success flag] is not null;
IF (@COMPLETEDROWS = 20 )
BEGIN
-- just an example here
RAISERROR ('Report Status TRIGGER ACTIVATED.', 16, 1);
-- you will probably want to use
-- EXEC msdb.dbo.sp_send_dbmail instead
-- but this requires configuration before you can use it
END;
GO
July 9, 2014 at 9:54 pm
Thanks a lot Erik and Tom. Very helpful!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply