MSSQL Trigger

I have this trigger that takes the table data from Log_Transactions and Entryaceess and sends out email alerts.  I'm having issues with the table format on the email body, the entire record is crammed into one field.  see attached Screencast that shows the email.

The trigger code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER trigger [checkinemail] on [INCIDENTTRAX].[dbo].[Log_Transactions]
after insert as
begin

DECLARE @xml nvarchar(MAX)
DECLARE @body nvarchar(MAX)
DECLARE @useremail as nvarchar(100)


SELECT @useremail=e.notifyemail FROM EntryAccess e, inserted i WHERE e.userid=i.hostuserid

SET @xml =CAST(( select FirstName as 'td', LastName as 'td', HostUserId as 'td', CredentialId as 'td', [Description] as 'td', dtDate as 'td', ReaderName as 'td', IncidentID as 'td' FROM inserted FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><H1>INCIDENTTRAX WST, NYC, WST</H1><body bgcolor=yellow><table border = 2><tr><th>FirstName</th><th>LastName</th><th>HostUserId</th><th>CredentialId</th><th>Description</th><th>dtDate</th><th>ReaderName</th><th>IncidentID</th></tr>'
SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@recipients =@useremail,
@body = @body,
@body_format ='HTML',
@subject ='INCIDENTTRAX ALERT!',
@profile_name ='INCIDENTTRAX'

end jtequia-410357.flv
jtequiaAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Just do this:
SET @xml =CAST((SELECT TOP 10 FirstName as 'td', LastName as 'td', HostUserId as 'td', CredentialId as 'td', [Description] as 'td', dtDate as 'td', ReaderName as 'td', IncidentID as 'td' FROM [INCIDENTTRAX].[dbo].[Log_Transactions] FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

PRINT @Xml

And you will see the problem immediately.
0
 
momi_sabagCommented:
can you post an example of how the XML variable value looks like?
0
 
jtequiaAuthor Commented:
See attached Sample output
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
momi_sabagCommented:
add a print statement in your triggegr
i want to see the html code that is genrated
0
 
Anthony PerkinsCommented:
In other words, if you run that from a query window (outside the Trigger) you should see the problem.
0
 
jtequiaAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.