Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trigger Troubles

Posted on 2014-03-06
16
Medium Priority
?
230 Views
Last Modified: 2014-03-14
Work in progress is below. Trying to craft a trigger that emails four pieces of information whenever a record is added to the ABC_DB_OnlineFormsConfirm table. The ConfirmationCode comes from that table, but the other three items come from another table - ABC_DB_OnlineForms.

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. I'm a total amateur covering for an AWOL developer.

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 or 1. Email should show Y or N instead of 0 or 1.
4. I need an additional email recipient - 'def@abc.com'



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
0
Comment
Question by:K_Deutsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 2
16 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39910553
Before you move on any further in my opinion this is wrong because it may hold/delay at least INSERTs no matter how little IO goes in/out ABC_DB_OnlineFormsConfirm table and here's what I usually do.
My trigger will just populate a Queue table with IDs of the row(s) from where I start to collect all information needed to send the emails. I have a SQL Stored proc and a Job calling it periodically that processes my queue then deletes it in a sliding window batches.
In a more serious architecture like I have on one SQL behind e-commerce website the emails are generated and sent via two Strong Mail servers.

This way you disconnect your OLTP table from the tedious process to generate on the fly and send the emails out all in a TRIGGER on INSERT....my 2pennyes...
0
 

Author Comment

by:K_Deutsch
ID: 39910572
It's necessary I shape what I am already doing to meet the requirements only because what you describe is completely out of my grasp. I understand best practice, but this is to cover an immediate need caused by a sustained void in our staff until a more permanent solution is reached. Considering we only get one completed form per week at a maximum, there can't be any harm in the trigger approach right?
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39910587
In that case with one row change per week or even hour that will definitely not hurt - looking to your code now...
0
[Video] Create a Disruption-Free Workspace

Open offices have their challenges. And Sometimes, it's even hard to work at work. It's time to reclaim your office and create a disruption-free workspace. With the MB 660, you can:

-Increase Concentration
-Improve well-being
-Boost Productivity

 
LVL 40

Expert Comment

by:lcohan
ID: 39910609
Please test this one and update the additional email address as needed - all you need to add more is to separate them by ;

slightly updated/fixed...

CREATE 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=
            case when F.FormType = F.userid -- where is the GroupId comming from you mention in the email?
            then
              'Confirmation Code: ' + I.ConfirmationCode + NCHAR(13) + NCHAR(10)
              + 'Subject to Backup Withholding?: '
              + case when F.IRSBackup=1 then 'Y' else 'N' end + NCHAR(13) + NCHAR(10)
              + 'Form Type: ' + F.FormType + NCHAR(13) + NCHAR(10)
              + 'Website User ID: ' + F.userid + NCHAR(13)+NCHAR(10)
            else      
              'Confirmation Code: ' + I.ConfirmationCode + NCHAR(13) + NCHAR(10)
              --+ ' ' + F.IRSBackup + NCHAR(13) + NCHAR(10)
              --+ case when F.IRSBackup=1 then 'Y' else 'N' end + NCHAR(13) + NCHAR(10)
              + 'Form Type: ' + F.FormType + NCHAR(13) + NCHAR(10)
              + 'Website User ID: ' + F.userid + NCHAR(13)+NCHAR(10)
            end
     
      from Inserted I
            INNER JOIN ABC_DB_OnlineForms F
      ON I.FormId = F.FormId

      exec msdb.dbo.sp_send_dbmail @profile_name = 'Gmail',
      @recipients = 'ABCwebmaster@abc-inc.com;def@abc.com;abc@123.com',
      @subject='An ABC Online App Has Been Completed',
      @body=@MessageBody

End
GO
0
 

Author Comment

by:K_Deutsch
ID: 39910680
Email is going to both addresses, so that's good. Here is what the body of message looks like now-

Confirmation Code: 03062014324
Form Type: Individual
Website User ID: todd

Subject to Backup Withholding result of Y or N is not showing.

Current code-

ALTER 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=
            case when F.FormType = F.userid -- where is the GroupId comming from you mention in the email?
            then
              'Confirmation Code: ' + I.ConfirmationCode + NCHAR(13) + NCHAR(10)
              + 'Subject to Backup Withholding?: '
              + case when F.IRSBackup=1 then 'Y' else 'N' end + NCHAR(13) + NCHAR(10)
              + 'Form Type: ' + F.FormType + NCHAR(13) + NCHAR(10)
              + 'Website User ID: ' + F.userid + NCHAR(13)+NCHAR(10)
            else      
              'Confirmation Code: ' + I.ConfirmationCode + NCHAR(13) + NCHAR(10)
              --+ ' ' + F.IRSBackup + NCHAR(13) + NCHAR(10)
              --+ case when F.IRSBackup=1 then 'Y' else 'N' end + NCHAR(13) + NCHAR(10)
              + 'Form Type: ' + F.FormType + NCHAR(13) + NCHAR(10)
              + 'Website User ID: ' + F.userid + NCHAR(13)+NCHAR(10)
            end
     
      from Inserted I
            INNER JOIN ABC_DB_OnlineForms F
      ON I.FormId = F.FormId

      exec msdb.dbo.sp_send_dbmail @profile_name = 'Gmail',
      @recipients = 'ABCwebmaster@ABC-inc.com;DEF@DEF-inc.com',
      @subject='An BC Online App Has Been Completed',
      @body=@MessageBody

End
GO
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39910764
"Subject to Backup Withholding result of Y or N is not showing."


That is controlled by the CASE as you said above:

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 or 1. Email should show Y or N instead of 0 or 1.


So my code version will send it only if "Form Type = Individual or if Form Type = Group" or in other words "when F.FormType = F.userid"
BTW - where is the GroupId comming from you mention in the detils above?

if you need to show Y/N on the line regardless then use this code below but I thout you meant to show the line "Subject to Backup Withholding" only if F.FormType = F.userid:


ALTER 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?: '
              + case when F.IRSBackup=1 then 'Y' else 'N' end + NCHAR(13) + NCHAR(10)
              + 'Form Type: ' + F.FormType + NCHAR(13) + NCHAR(10)
              + 'Website User ID: ' + F.userid + NCHAR(13)+NCHAR(10)
     
      from Inserted I
            INNER JOIN ABC_DB_OnlineForms F
      ON I.FormId = F.FormId

      exec msdb.dbo.sp_send_dbmail @profile_name = 'Gmail',
      @recipients = 'ABCwebmaster@ABC-inc.com;DEF@DEF-inc.com',
      @subject='An BC Online App Has Been Completed',
      @body=@MessageBody

End
GO
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39910782
Or maybe like this - sorry for misunderstandings but is somewhat difficult to code without having the data:

ALTER 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?: '
              + case when F.IRSBackup=1 and F.FormType = F.userid then 'Y' else 'N' end + NCHAR(13) + NCHAR(10)
              + 'Form Type: ' + F.FormType + NCHAR(13) + NCHAR(10)
              + 'Website User ID: ' + F.userid + NCHAR(13)+NCHAR(10)
     
      from Inserted I
            INNER JOIN ABC_DB_OnlineForms F
      ON I.FormId = F.FormId

      exec msdb.dbo.sp_send_dbmail @profile_name = 'Gmail',
      @recipients = 'ABCwebmaster@ABC-inc.com;DEF@DEF-inc.com',
      @subject='An BC Online App Has Been Completed',
      @body=@MessageBody

End
GO
0
 

Author Comment

by:K_Deutsch
ID: 39910819
There are four form types that are captured in the ABC_OnlineForms table

1. Individual
2. Group
3. ESA
4. IRA

The Subject to backup withholding field isn't relevant to ESA and IRA form types so the line shouldn't appear at all. Individual and Group should show the line with Y or N always.

With that being said, the whole F.FormType=F.userid reference confuses me. It should be more like IF F.FormType = 'Individual' or 'Group' SHOW Backup witholding (Y or N) ELSE do not show Backup.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 39910868
In that case this clarifies thigs a lot so the code should be:

ALTER 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=
            case when ltrim(rtrim(F.FormType)) IN('Individual','Group')
            then
              'Confirmation Code: ' + I.ConfirmationCode + NCHAR(13) + NCHAR(10)
              + 'Subject to Backup Withholding?: '
              + case when F.IRSBackup=1 then 'Y' else 'N' end + NCHAR(13) + NCHAR(10)
              + 'Form Type: ' + F.FormType + NCHAR(13) + NCHAR(10)
              + 'Website User ID: ' + F.userid + NCHAR(13)+NCHAR(10)
            else      
              'Confirmation Code: ' + I.ConfirmationCode + NCHAR(13) + NCHAR(10)
              + 'Form Type: ' + F.FormType + NCHAR(13) + NCHAR(10)
              + 'Website User ID: ' + F.userid + NCHAR(13)+NCHAR(10)
            end
      from Inserted I
            INNER JOIN ABC_DB_OnlineForms F
      ON I.FormId = F.FormId

      exec msdb.dbo.sp_send_dbmail @profile_name = 'Gmail',
      @recipients = 'ABCwebmaster@ABC-inc.com;DEF@DEF-inc.com',
      @subject='An BC Online App Has Been Completed',
      @body=@MessageBody

End
GO
0
 

Author Comment

by:K_Deutsch
ID: 39910951
So close...the IRA and ESA outcomes test clean, but FormType=Individual has cut off Website User ID field name and no data as follows-

Confirmation Code: 03062014331
Subject to Backup Withholding?: Y
Form Type: Individual
Website Us

Same for FormType=Group, except user id is cut off in a different way.

Confirmation Code: 03062014329
Subject to Backup Withholding?: Y
Form Type: Group
Website User I
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39911144
You can't safely use a single @variable for a trigger.  A trigger in SQL Server fires only once, no matter how many rows were updated.  Thus, you must do *set processing* even in triggers (or, yikes, use a cursor, which is pure evil for performance).



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
0
 

Author Comment

by:K_Deutsch
ID: 39911212
Hmmm. That version results in a website error when I go to complete a test application.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39911315
I don't have any way to test it.

First, run a version of the SELECT by itself to make sure it's valid:

 select top (100)
              '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
        FROM ABC_DB_OnlineForms F
        --ON I.FormId = F.FormId
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39920996
Try this one:

ALTER TRIGGER [dbo].[SendEmail]
ON [ABCJun28].[dbo].[ABC_DB_OnlineFormsConfirm]
AFTER INSERT
AS
Begin
      Declare @MessageBody varchar(8000)
      Declare @Backup varchar(1000)
      Declare @FormType varchar(1000)
      Declare @userid varchar(1000)
      select
            @MessageBody=
            case when ltrim(rtrim(F.FormType)) IN('Individual','Group')
            then
              'Confirmation Code: ' + I.ConfirmationCode + NCHAR(13) + NCHAR(10)
              + 'Subject to Backup Withholding?: '
              + case when F.IRSBackup=1 then 'Y' else 'N' end + NCHAR(13) + NCHAR(10)
              + 'Form Type: ' + F.FormType + NCHAR(13) + NCHAR(10)
              + 'Website User ID: ' + F.userid + NCHAR(13)+NCHAR(10)
            else      
              'Confirmation Code: ' + I.ConfirmationCode + NCHAR(13) + NCHAR(10)
              + 'Form Type: ' + F.FormType + NCHAR(13) + NCHAR(10)
              + 'Website User ID: ' + F.userid + NCHAR(13)+NCHAR(10)
            end
      from Inserted I
            INNER JOIN ABC_DB_OnlineForms F
      ON I.FormId = F.FormId

      exec msdb.dbo.sp_send_dbmail @profile_name = 'Gmail',
      @recipients = 'ABCwebmaster@ABC-inc.com;DEF@DEF-inc.com',
      @subject='An BC Online App Has Been Completed',
      @body=@MessageBody

End
GO
0
 

Author Comment

by:K_Deutsch
ID: 39921725
No errors when updating the trigger, but when I complete a test application no email is received at all. Doesn't show as a sent item from Gmail either.
0
 

Author Closing Comment

by:K_Deutsch
ID: 39930079
Using Icohan's last suggestion, it works except for the userid info, which frankly I can live without. So this resolves the issue. Thanks Icohan.
0

Featured Post

Take our survey for a chance to win!

As a valued customer of Targus, we’d like to ask you a few questions about us. As thanks, you will be automatically entered for a chance to win a $500 VISA gift card. To enter, just complete the survey by September 15, 2017.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you are having problems installing printer drivers, or if documents repeatedly get stuck in the print queue even after re-installing the printer drivers, then follow these steps to solve the problems. Please note that the steps are shown both for…
It started not too long ago. It was at first annoying. My keystrokes seemed to be randomly generated, not the ones I typed on the keyboard. For some reason this only happened in certain applications (especially browsers such as IE11, Firefox and Chr…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question