Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

something better than TRIGGER to send email after table populated with 20 items? Expand / Collapse
Author
Message
Posted Saturday, July 5, 2014 9:31 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:39 AM
Points: 605, Visits: 1,406
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?


it helps to talk it out
Post #1589517
Posted Tuesday, July 8, 2014 7:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:31 AM
Points: 1,292, Visits: 1,423
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


Post #1590351
Posted Wednesday, July 9, 2014 9:54 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:39 AM
Points: 605, Visits: 1,406
Thanks a lot Erik and Tom. Very helpful!!!

it helps to talk it out
Post #1590992
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse