Hi,
I need to send out an email when the status field <> '0' in a newly inserted record. I have a trigger that works fine at another location, but is hanging up the insert of the record into the database. When I disable the trigger and a new record is inserted everything is fine. I have tested the database email and it was successful. I also successfully sent and received an email from a query using the EXEC msdb.dbo.sp-send-dbmail and the lines to follow below.
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 ='bob@XXX.com'
@copy_recipients = 'me@BBB.biz',
@subject = 'TV Alarm Alert',
@body = @tableHtml,
@body_format = 'HTML';