SQL Server Forums - Trigger Troubles

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums

Profile | Active Topics | Members | Search | Forum FAQ

Username:	Password:
Save Password
Forgot your Password?

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger Troubles

Reply to Topic

Printer Friendly

Author

Topic

Herkamer333
Starting Member

6 Posts

Posted - 03/06/2014 : 12:34:54

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

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

Herkamer333
Starting Member

6 Posts

Posted - 03/06/2014 : 14:32:59

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

ScottPletcher
Aged Yak Warrior

USA
550 Posts

Posted - 03/06/2014 : 17:14:09

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

Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.03 seconds.