Trigger is not allowing the database to populate when enabled

Other SQL Server Topics
1

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.

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';
2

What are the data types for the columns? (ie Show DDL/CREATE TABLE for dbo.[TV Data].)
Why are you using dbo.[TV Data] and not the inserted pseudo table in the trigger?

3

You should be using the inserted table, vastly better efficiency.

But you also have a syntax error, you're missing a comma in the sp_send_dbmail after @recipients:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Alarm emails',
@recipients ='bob@XXX.com' --<<--
@copy_recipients = 'me@BBB.biz',

4 
...
SELECT td = dbo.[TV Data].[Car ID], ' ',
...
						FROM inserted --<<--
						where [Front Image Alarm Status] <> '0' 
...
5

I made the correction and used the inserted table, but it still hangs up the database and does not populate.

6

Image Time is datetime
both temp fields are floats
both status fields are int
Direction label is varchar(255)

I just tried the inserted table, but it still hangs up the database and does not populate. Even when I try to send an email and the only criteria is that a record be inserted hangs up the database and does not populate. The body is not a table of data it is 1 word.

7

Is it possible that the status is NULL? If so, the <> 0 would skip the row, even though you might want to include it. Similarly, a NULL value in any other column would cause the result of the SELECT to be NULL. To be safe, add ISNULL()s (or COALESCES()) around the values in the SELECT.

8

There is not really much to go on here. You should really have some sort of exception handling in the trigger so you can monitor errors. As I presume the INSERT is being rolled back, there must be an exception. You have not provided the error message.

At a guess, maybe the login running the insert is not a member of DatabaseMailUserRole in msdb. See if the trigger works when it is created WITH EXECUTE AS sa. If that works then create a login with the minimum permissions required to execute the trigger.

Also, try using the CONCAT function, instead of +, as there will be fewer problems with NULLS and it is more efficient.

9

No, the status is not NULL, it goes from 0 to 3. I did a query, for 1 year, just to verify and there are no NULLS in that field for the past year.

I am wondering if it might be permissions, because I made a test trigger. See below. I get the same, the database hangs up.

USE [ET_TV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


	ON  [ET_TV.[dbo].[TV Data] 
	with execute as owner
	AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;

	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'Email Alert', 
		@recipients = 'XXX510@gmail.com', 
		@subject = 'Test Email from TV',
		@body = 'Finally!!!!';
END
10

Yeah, grant permissions to use msdb.dbo.sp_send_dbmail

11

WITH EXECUTE AS OWNER will not work as ownership chaining does not work by default across databases. (You do not want it to work as it is a security hole.)

Where is the exception handling?!
Where is the error message?!

12

I ended up having the trigger send the data to a new table then set up a job in the agent to run every 10 seconds to send out the email when applicable. Apparently, the email que was too long for a trigger.

Thank you for your input.

13

So, apparently the email que was too long and that is what was hanging up the database. I created a new table that the trigger sends the data to then I set up a job in the agent to run every ten seconds and send the email when applicable.
Thank you for all you input!