Trigger is hanging up the database

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