SQL mail message body with data from two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL mail message body with data from two tables

Author

Topic

Herkamer333
Starting Member

6 Posts

Posted - 2013-10-27 : 16:59:04

I need to set a trigger so that an email is generated when a row is inserted into the ABC_DB_OnlineFormsConfirm table. The message will include data not only from that table, but from a related table called ABC_DB_OnlineForms. The common field between the two is FormID. This is related to an online account application process where the user has the option of completing it in one session, or saving it and coming back at a later time. Once the user has completed the first screen and clicks continue, a record is written to ABC_DB_OnlineForms. At the very end, when he clicks “Save and Complete”, a row is written to the ABC_DB_OnlineForms table. I would like the body of the email to include three separate lines, as follows. The first two pieces of data come from the OnlineForms table and the third from OnlineFormsConfirm. The field names are userid, IRSBackup and ConfirmationCode.

UserID:
Subject to IRS Backup Withholding?:
Confirmation Code:

What I have below results in an email body that contains the confirmation code all by itself. If I switch @body=@MessageBody to @body=@UserID or @body=@Backup, it will report the correct single piece of data, but I have no idea how to get all three pieces at once as separate lines including the leading descriptor (UserID:, Confirmation Code, etc.). I am a total greenhorn and if someone could take my sample below and modify it to my specs I would appreciate it. I will be doing several more of these and it will illustrate the methods I can use for self service at that point. Thanks for reading.

CREATE TRIGGER SendEmail
ON [ABC].[dbo].[ABC_DB_OnlineFormsConfirm]
AFTER INSERT
AS
Begin
Declare @MessageBody varchar(100)
Declare @Backup varchar(100)
Declare @Userid varchar(100)
select @MessageBody=I.ConfirmationCode, @Backup = F.IRSBackup, @UserId = F.UserId from Inserted
I LEFT JOIN ABC_DB_OnlineForms F ON I.FormId = F.FormId
exec msdb.dbo.sp_send_dbmail @profile_name = 'Gmail',
@recipients = 'ABC@abc-inc.com',
@subject='An ABC Online App Has Been Completed',
@body=@MessageBody
End

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-27 : 21:16:30

try this
@body=@MessageBody + CHAR(10) + @UserID + CHAR(10) + @Backup

Herkamer333
Starting Member

6 Posts

Posted - 2013-10-28 : 11:14:06

I get 'invalid syntax near +' error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 13:43:59

Are you trying to send a single mail involving all user details or is your attempt to sent individual mails to each user? If latter you need a loop or cursor based logic.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Herkamer333
Starting Member

6 Posts

Posted - 2013-10-28 : 15:16:16

I only want the userid and IRSBackup from the record in ABC_DB_OnlineForms plus the ConfirmationCode from ABC_DB_OnlineFormsConfirm. FormID is the common field between the two tables. This is an alert system so that when a user completes an online app, we get an email with some very basic details about just that user and the recently completed app.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 23:51:31

That means one email per user right? That being case you need a loop inside which you call sp_send_dbmail

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Herkamer333
Starting Member

6 Posts

Posted - 2013-10-29 : 10:40:08

Only one email should be sent out - to ABC@abc-inc.com. The email message is simply an alert that a registered user of our website has completed an online application, as well as provide three pieces of data from the two related tables

userid
IRSBackup
ConfirmationCode

In summary, it's an email to ourselves based on database activity.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-29 : 12:08:49

quote:
Originally posted by Herkamer333

Only one email should be sent out - to ABC@abc-inc.com. The email message is simply an alert that a registered user of our website has completed an online application, as well as provide three pieces of data from the two related tables

userid
IRSBackup
ConfirmationCode

In summary, it's an email to ourselves based on database activity.

Thats fine
But consider cases where INSERTED has more than 1 record ie batch inserts.
in that case you will have multiple records which may have different emailids

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources