SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trigger Troubles		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Herkamer333
Starting Member

6 Posts
Posted - 03/06/2014 :  12:34:54  Show Profile  Reply with Quote
The trigger below has two problems. I am really struggling.

1. Emails sent do not contain @Backup and @UserId in the body, only @ConfirmationCode. And I'd like the email the recipient sees to have three lines and specify the field names like this-

Confirmation Code: @ConfirmationCode
Subject to Backup Withholding?: @Backup
Website User ID: @UserId

2. Emails aren't sent at all if I try to specify two recipients the way I have.


CREATE TRIGGER SendEmail
ON [ABCJun28].[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 ADF_DB_OnlineForms F ON I.FormId = F.FormId
exec msdb.dbo.sp_send_dbmail @profile_name = 'Gmail',
@recipients = 'ABC@abc-inc.com',
@recipients = 'DEFwebmaster@def-inc.com',
@subject='An ABC Online App Has Been Completed',
@body=@MessageBody
End
Edited by - Herkamer333 on 03/06/2014 12:36:03

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts
Posted - 03/06/2014 :  12:50:37  Show Profile  Reply with Quote
I'd suggest that you don't send emails from a trigger. Rather, use the trigger to insert into a "mail queue" table and have another process that sends the emails.

That aside,

1. You aren't seting the Backup or User ID to anything in the Bodyy message. Maybe something like this:
select 
	@MessageBody=
	'Confirmation Code: ' + I.ConfirmationCode + NCHAR(13) + NCHAR(10)
	+ 'Subject to Backup Withholding?: ' + F.IRSBackup + NCHAR(13) + NCHAR(10)
	+ 'Website User ID: ' + F.UserId 
from Inserted I 
LEFT JOIN ADF_DB_OnlineForms F 
ON I.FormId = F.FormId
Note, that you may need to handle NULL values depending on your columns nullability.

2. The Recipients is supposed to be a semicolon-delimited list of e-mail addresses. So specifying the same parameter more than once won't work.
Edited by - Lamprey on 03/06/2014 12:51:32
Go to Top of Page

Herkamer333
Starting Member

6 Posts
Posted - 03/06/2014 :  14:32:59  Show Profile  Reply with Quote
Modifed trigger is below. Here is what the email recipient currently sees in the body of the message

Confirmation Code: 03062014323
Subject to Backup Withholding?: 1
Form Type: ESA
Website User ID:

Problems I need to solve-

1. Website User ID is blank even though the field is never NULL
2. User should only see the Subject to Backup Withholding field if Form Type = Individual or if Form Type = Group
3. Backup witholding data comes out of the database as either a 0 or1. Email should show Y or N instead of 0 or 1.



SET TRIGGER [dbo].[SendEmail]
ON [ABCJun28].[dbo].[ABC_DB_OnlineFormsConfirm]
AFTER INSERT
AS
Begin
Declare @MessageBody varchar(100)
Declare @Backup varchar(100)
Declare @FormType varchar(100)
Declare @userid varchar(100)
select
@MessageBody=
'Confirmation Code: ' + I.ConfirmationCode + NCHAR(13) + NCHAR(10)
+ 'Subject to Backup Withholding?: ' + F.IRSBackup + NCHAR(13) + NCHAR(10)
+ 'Form Type: ' + F.FormType + NCHAR(13) + NCHAR(10)
+ 'Website User ID: ' + F.userid + NCHAR(13)+NCHAR(10)
from Inserted I
LEFT JOIN ABC_DB_OnlineForms F
ON I.FormId = F.FormId
exec msdb.dbo.sp_send_dbmail @profile_name = 'Gmail',
@recipients = 'ABCwebmaster@abc-inc.com',
@subject='An ABC Online App Has Been Completed',
@body=@MessageBody
End
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
467 Posts
Posted - 03/06/2014 :  17:14:09  Show Profile  Reply with Quote
Please try this instead:


ALTER TRIGGER [dbo].[SendEmail]
ON [ABCJun28].[dbo].[ABC_DB_OnlineFormsConfirm]
AFTER INSERT 
AS 
SET NOCOUNT ON
EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'Gmail', 
    @recipients = 'ABCwebmaster@abc-inc.com;DEFwebmaster@def-inc.com', --';other_recipient@other_address.com;...'
    @subject = 'An ABC Online App Has Been Completed',
    @body = '',
    @query = '
        select 
	        ''Confirmation Code: '' + I.ConfirmationCode + NCHAR(13) + NCHAR(10)
	        + ''Subject to Backup Withholding?: '' + ISNULL(F.IRSBackup, ''?'') + NCHAR(13) + NCHAR(10)
	        + ''Form Type: '' + ISNULL(F.FormType, ''?'') + NCHAR(13) + NCHAR(10)
	        + ''Website User ID: '' + ISNULL(F.userid, ''?'') + NCHAR(13)+NCHAR(10)
        from Inserted I 
        LEFT JOIN ABC_DB_OnlineForms F 
        ON I.FormId = F.FormId
        ',
    @attach_query_result_as_file = 0
GO
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000