Trigger is hanging up the database

Hi,

I need to send a database email when the status field of a newly inserted field is <> '0'. I have a trigger that works fine at another location but will cause the database to not populate when enabled at this location. I have tested the database email and successfully sent and received an email from a query using EXEC msdb.dbo.sp-send-dbmail and the lines to follow as seen below in the code.

If I just run the query the email goes out, but when I use it as a trigger just enabling it causes the database to hang up.

USE [AK_Mid_TV]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TV Front Image Alarm Alerts]
   ON  [AK_Mid_TV].[dbo].[TV Data]
   FOR INSERT
AS 

	SET NOCOUNT ON;

		DECLARE @tableHTML NVARCHAR(MAX);

		SET @tableHTML = 
			N'<h1>TORPEDO VISION FRONT ALARM ALERT</H1>' +
			N'<table border = "1">' +
			N'<tr><th>Car ID</th><th>Image Time</th>' +
			N'<th>Front Alarm Level</th><th>Front Alarm Temp</th><th>Direction</th>' +

			CAST ( ( SELECT td = dbo.[TV Data].[Car ID], ' ',
							td = dbo.[TV Data].[Image Time], ' ', 
							[td/@align] = 'center',
							td = dbo.[TV Data].[Front Image Alarm Status], ' ',
							[td/@align] = 'center',
							td = format(dbo.[TV Data].[Front Temp F], '#,#'), ' ',
							[td/@align] = 'center',
							td = dbo.[TV Data].[Direction Label]
						
						FROM dbo.[TV Data]
						where [Image Time] in (SELECT MAX([Image Time]) from dbo.[TV Data]) and [Front Image Alarm Status] <> '0' 
						FOR XML PATH ('tr'), TYPE
						) AS NVARCHAR(MAX) ) +
						N'</table>';

						If @tableHTML <> ' '
						EXEC msdb.dbo.sp_send_dbmail
						@profile_name = 'Alarm emails',
						@recipients ='email address removed for privacy reasons'
						@copy_recipients = 'email address removed for privacy reasons',
						@subject = 'TV Alarm Alert',
						@body = @tableHtml,
						@body_format = 'HTML';

navindevan

Jul 07, 2025

The issue you are facing is a common and known problem. Using sp_send_dbmail directly inside a trigger can cause blocking or hanging behavior in SQL Server.

This happens because of the below reasons:

sp_send_dbmail is an external operation.
Triggers are part of the transaction context of the INSERT, and if sp_send_dbmail takes time (due to network/email latency), it delays or blocks the transaction.
In some setups (especially with high insert frequency or locks), this may hang the calling process or cause deadlocks.

Suggested approach:

1. Create a table to queue alert data

CREATE TABLE dbo.EmailQueue (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    CarID VARCHAR(50),
    ImageTime DATETIME,
    FrontAlarmStatus VARCHAR(10),
    FrontAlarmTemp VARCHAR(10),
    Direction VARCHAR(50),
    IsSent BIT DEFAULT 0,
    CreatedAt DATETIME DEFAULT GETDATE()
);

2. Update the trigger to insert into EmailQueue instead of sending email.

ALTER TRIGGER [dbo].[TV Front Image Alarm Alerts]
   ON [dbo].[TV Data]
   FOR INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.EmailQueue (CarID, ImageTime, FrontAlarmStatus, FrontAlarmTemp, Direction)
    SELECT 
        i.[Car ID],
        i.[Image Time],
        i.[Front Image Alarm Status],
        FORMAT(i.[Front Temp F], '#,#'),
        i.[Direction Label]
    FROM inserted i
    WHERE i.[Front Image Alarm Status] <> '0'
END

3. Create a SQL Agent Job or Scheduled Task to process the email queue. You can schedule this depending on the requirement.

DECLARE @tableHTML NVARCHAR(MAX);

SELECT TOP 10 * INTO #toSend FROM dbo.EmailQueue WHERE IsSent = 0 ORDER BY CreatedAt;

IF EXISTS (SELECT 1 FROM #toSend)
BEGIN
    SET @tableHTML = 
        N'<h1>TORPEDO VISION FRONT ALARM ALERT</H1>' +
        N'<table border = "1">' +
        N'<tr><th>Car ID</th><th>Image Time</th><th>Front Alarm Level</th><th>Front Alarm Temp</th><th>Direction</th></tr>' +

        CAST( ( SELECT 
                    td = CarID, '',
                    td = ImageTime, '',
                    td = FrontAlarmStatus, '',
                    td = FrontAlarmTemp, '',
                    td = Direction
                FROM #toSend
                FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX)) +
        N'</table>';

    EXEC msdb.dbo.sp_send_dbmail
        profile_name = 'Alarm emails',
        @recipients ='email address removed for privacy reasons',
        @copy_recipients = 'email address removed for privacy reasons',
        @subject = 'TV Alarm Alert',
        Body = @tableHTML,
        Body_format = 'HTML';

    -- Mark as sent
    UPDATE dbo.EmailQueue
    SET IsSent = 1
    WHERE ID IN (SELECT ID FROM #toSend)
END

5 Replies