Solved

Trigger Troubles

Posted on 2014-03-06
16
210 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
  • 7
  • 7
  • 2
16 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
In that case with one row change per week or even hour that will definitely not hurt - looking to your code now...
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
"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 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
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
Comment Utility
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 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
Hmmm. That version results in a website error when I go to complete a test application.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now