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:
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?