CODEWITHSUNDEEP
sqlsql-serversql-server-2012triggers
J M
J M

Reputation: 11

SQL Server email trigger upon data insertion into a table not working

I'm having issues creating a trigger that will send an email containing a table of inserted data to a distribution list when a device is docked into an IP downloader and the data is sent to a table in the SQL Server. I need the Date, DownloadID, and Description from the most recent entries (23-26 lines in one download) to be selected and then formatted into an email and sent out.

I'm relatively new to SQL so I'm not sure where I need to look to see what error the trigger is generating. Below is the block of code I've been trying to work with. Any suggestions or advice would be appreciated. When I run the select statement and the email stored procedure manually, it works fine. As soon as I turn it into a trigger, it fails. 

CREATE TRIGGER [dbo].[Trg_Download] 
ON [dbo].[DownloadData] 
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DownloadID VARCHAR(7)
    DECLARE @xml NVARCHAR(MAX)
    DECLARE @body NVARCHAR(MAX)

    SET @DownloadID = (SELECT TOP 1 downloadID 
                       FROM inserted 
                       ORDER BY downloadid DESC)

    SET @xml = CAST((SELECT [Date] AS 'td','',[DownloadID] AS 'td','', [Description] AS 'td'
                     FROM inserted
                     WHERE [DownloadID] = @downloadID
                     ORDER BY Description
                     FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))
    SET @body = '<html><body><H3>Line Clearout Summary</H3>
<table border = 1> 
<tr>
<th> Date </th> <th> DownloadID </th> <th> Description </th></tr>'  

    SET @body = @body + @xml +'</table></body></html>'

    EXEC msdb.dbo.sp_send_dbmail
              @profile_name = N'DBMail',
              @body = @body,
              @body_format = HTML,
              @recipients = N'[email protected]',
              @subject = 'Line Clearout Summary'
END

Thank you

Upvotes: 1

Views: 110

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

Although I strongly agree that you should not put this "Send Email" logic in a trigger, treating your question as academic, I will suggest that you could wrap your current trigger code (from the first DECLARE to the final EXEC) in a TRY..CATCH block which could let you log any error that occurs in that code, as well as prevent the transaction from rolling back by not throwing the error.

Incidentally, since no one's suggested a specific alternative, I will tell you that the way we implement this kind of logic is with an agent job that runs at regular intervals and sends emails based on what rows have been added since the last time the job ran. You can either add column(s) to the DownloadData table that keeps track of whether an email has been sent, or you can use an AFTER INSERT trigger to populate a separate table that the job treats as an email queue.

Upvotes: 1

Related Questions