July 22, 2016 at 12:51 pm
I have a case where a table is written to with a status column set to 1 from 0 to maybe 4 times a day. I am trying to send an email including some values from the inserted row. I only want the trigger to send an email if the status=1. I've tested the code to work without the trigger by providing the values to populate the fields that I would need from the inserted line. It fully works this way with the body as expected.
The trigger fires, it even sends an email, and gets the correct email addresses to send to based on values from the insert row. So I know the logic works, the select from inserts works. The subject gets set, the email gets sent but the email body doesn't get populated AT ALL. Not even the stuff before the query /results. Below is the code for the trigger. I would think that first @body statement should at a minimum put that static part into the body but that doesn't appear to happen even.
Any help would be greatly appreciated. The fact is I know that the variables are getting set by the values from the inserted row because the subsequent queries for finding email addresses work, and populate the correct variables for the email. Just with a blank email.
ALTER TRIGGER TRG_POPendEmail
ON [Navision].[dbo].[Radia Inc PS - LIVE$Approval Entry]
AFTER INSERT AS
begin
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @email NVARCHAR(MAX)
DECLARE @semail NVARCHar(MAX)
DECLARE @sendereid NVARCHAR(max)
DECLARE @approverid NVARCHAR(max)
DECLARE @document NVARCHAR(MAX)
DECLARE @sequence INT
DECLARE @status INT
SET @status= (select [Status] from inserted)
SET @document= (select [Document No_] from inserted)
SET @sequence= (select [Sequence No_] from inserted)
Set @approverid = (select [Approver ID] from inserted)
Set @sendereid = (select [Sender ID] from inserted)
set @xml=CAST((Select [Document No_] as 'td','',[Sequence No_] as 'td'
,'',[Sender ID] as 'td'
,'',AE.[Approver ID] as 'td'
,'','http://dcportal/Account/Login?ReturnUrl=%2F' as 'td'
,'',AE.[Status] as 'td'
,'',dateadd(hour,-7,[Date-Time Sent for Approval]) as 'td'
,'',dateadd(hour,-7,[Last Date-Time Modified]) as 'td'
,'',[Last Modified By ID] as 'td','',AE.[Due Date] as'td'
,'',[Amount] as 'td','',[Original Approver ID] as 'td'
,'',PH.[Posting Description] as 'td'
FROM [Navision].[dbo].[Radia Inc PS - LIVE$Approval Entry] AE (nolock)
left outer join [Navision].[dbo].[Radia Inc PS - LIVE$Purchase Header] PH (nolock) on AE.[Document No_]=PH.No_
left join [Navision].[dbo].[Radia Inc PS - LIVE$User Setup] U (nolock) on AE.[Approver ID]=U.[User ID]
WHERE AE.[Approver ID]=@approverid and @status=1
FOR XML Path('tr'), elements ) as nvarchar(max))
set @body='<html><body><H3>PO Info</H3>
<table border = 1>
<tr>
<th> PO# </th> <th> Seq# </th>
<th> Sender </th> <th> Approver </th>
<th> Approval Link</th><th> Status </th>
<th> Time Sent </th> <th> Time Modified </th> <th> Modified By </th>
<th> Due Date </th> <th> Amount </th> <th> Original Approver </th>
<th> Description </th>
</tr>'
Set @body=@body+ @xml + '</table></body></html>'
Set @email=(SELECT top 1 U.[E-Mail]
FROM [Navision].[dbo].[Radia Inc PS - LIVE$User Setup] U (nolock) where U.[User ID]=@approverid)
Set @semail=(SELECT top 1 U.[E-Mail]
FROM [Navision].[dbo].[Radia Inc PS - LIVE$User Setup] U (nolock) where U.[User ID]=@sendereid)
EXEC msdb.dbo.sp_send_dbmail
@profile_name='NAV',
@recipients=@email,
@copy_recipients=@semail
,@blind_copy_recipients='sql@sqlemailfake.com'
,@Subject='PO Pending Approval'
,@Body=@body
,@body_format='html'
END
July 22, 2016 at 1:04 pm
Will Stillwell (7/22/2016)
...the email body doesn't get populated AT ALL....
I'm betting you are not handling nulls correctly; you have to isnull every column as you build the string, so that you don't get 'somestring' + null + 'somestring' resulting in a NULL html body.
the other thing i see is you are using the INSERTED table but assuming ONE AND ONLY ONE row was modified.
even if you "know" that's how the application works, best practice is to assume multiple rows are affected,and build your string accordingly.
this old post by me has a pretty full fledged example of detecting set based changes, pulling the multi row details via FOR XML in a trigger and sending an email:
http://www.sqlservercentral.com/Forums/FindPost1179651.aspx
Lowell
July 22, 2016 at 1:15 pm
Lowell (7/22/2016)
Will Stillwell (7/22/2016)
...the email body doesn't get populated AT ALL....I'm betting you are not handling nulls correctly; you have to isnull every column as you build the string, so that you don't get 'somestring' + null + 'somestring' resulting in a NULL html body.
If that was the case why would running the exact same code outside the trigger it works? I'll look at your post. But I just wanted to bring up that manually running it the body builds just fine. Using the exact same code just populating variables from static entries instead of the inserted info.
The trigger isn't based on updates, and the app only inserts one row at a time.
Also, with the select I'm using, it can bring back more than one row from the table to populate the body. That works. I've done manual runs with it running and returning 20+ rows. works perfect. Ran from the trigger it fails to build the body.
Thank you for your time in trying to help me.
July 24, 2016 at 9:11 pm
Is ApproverID a unique key in table [Navision].[dbo].[Radia Inc PS - LIVE$Approval Entry] ?
_____________
Code for TallyGenerator
July 25, 2016 at 5:43 pm
Will Stillwell (7/22/2016)
Lowell (7/22/2016)
Will Stillwell (7/22/2016)
...the email body doesn't get populated AT ALL....I'm betting you are not handling nulls correctly; you have to isnull every column as you build the string, so that you don't get 'somestring' + null + 'somestring' resulting in a NULL html body.
If that was the case why would running the exact same code outside the trigger it works? I'll look at your post. But I just wanted to bring up that manually running it the body builds just fine. Using the exact same code just populating variables from static entries instead of the inserted info.
The trigger isn't based on updates, and the app only inserts one row at a time.
Also, with the select I'm using, it can bring back more than one row from the table to populate the body. That works. I've done manual runs with it running and returning 20+ rows. works perfect. Ran from the trigger it fails to build the body.
Thank you for your time in trying to help me.
add this just before you send the email:
set @body = isnull(@body,N'BODY IS NULL, LOWELL IS RIGHT, I AM USING OUTER JOINS');
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply