something better than TRIGGER to send email after table populated with 20 items?

• KoldCoffee

  SSCoach

  Points: 18971

  More actions

  July 5, 2014 at 9:31 am

  #309749

  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?

• Tom257

  SSChampion

  Points: 11543

  More actions

  July 8, 2014 at 7:23 am

  #1727759

  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

• KoldCoffee

  SSCoach

  Points: 18971

  More actions

  July 9, 2014 at 9:54 pm

  #1728252

  Thanks a lot Erik and Tom. Very helpful!!!