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

Question

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'DistributionList@GMAIL.com',
              @subject = 'Line Clearout Summary'
END

Thank you

[Just don't do it.](https://stackoverflow.com/questions/6153330/can-a-sql-trigger-call-a-web-service/6154188#6154188) — Lukasz Szozda, May 01 '18 at 18:02
Consider putting the email send command in the code itself that populates the table — Daniel Marcus, May 01 '18 at 18:09
I dont have permission to modify the code that populates the table unfortunately — J M, May 01 '18 at 18:19
Like @lad2025 said, don't do it; it can cause all sorts of problems. For example, if the email fails to send for whatever reason (maybe your email server is down, or someone without permission to use `sp_send_dbmail` tries to do an `INSERT`) then the `INSERT`, will fail. That should not be intended behaviour. A `TRIGGER` is one of the last places you should be putting logic to send an email. — Thom A, May 01 '18 at 18:39
A trigger runs in the same transaction as the code that caused it to fire, so please add that code to your question. I don't see anything obviously wrong in the trigger code. Also you say that it's not working, but you don't know what the error is. How do you know there is an error? What do you mean by "not working"? — Tab Alleman, May 01 '18 at 18:51
I dont have the code that is causing the trigger to fire. I know something is not working because the whole transaction rolls back when I try to import the data with the trigger on the table. Guess it just can't be done. — J M, May 01 '18 at 19:01
If you don't have permission to change the code yourself, speak to someone that can. As you can see, there are so many reason why not to do as you've asked, however, so few so why you should. I'm sure they would also understand the reasons as well, and be happy to look at alternatives or even implementing it in the application. — Thom A, May 01 '18 at 19:03
I've already tried asking them to change the code. They are not interested in customizing code to suit the way we are implementing the system. I'll look for non-trigger alternatives to carry out this task — J M, May 01 '18 at 20:13
On the "don't do it" comments: Don't do it that way. Instead have a look at the [Service Broker](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker?view=sql-server-2017) (read an [introduction here](https://technet.microsoft.com/en-us/library/ms345108%28v=sql.90%29.aspx)). It'll allow you to queue messages (not e-mails per see, any message) for asynchronous processing (by some other procedure). That way your trigger won't delay or tear down the whole transaction. If an e-mail could not be sent, the message can stay in the queue for later retries. — sticky bit, May 01 '18 at 22:32

score 1 · Answer 1 · answered May 01 '18 at 19:45

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.

Thank you. I will look into using a job agent to run the task instead of the trigger — J M, May 01 '18 at 20:10

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

1 Answers1