Trigger Troubles - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger Troubles

Author

Topic

Herkamer333
Starting Member

6 Posts

Posted - 2014-03-06 : 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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-06 : 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.

Herkamer333
Starting Member

6 Posts

Posted - 2014-03-06 : 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

550 Posts

Posted - 2014-03-06 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources