Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
KoldCoffee
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
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?
Tom Brown
Tom  Brown
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1342 Visits: 1466
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
KoldCoffee
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 1905
Thanks a lot Erik and Tom. Very helpful!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search