Solved

Trigger Troubles

Posted on 2014-03-06
16
220 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
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 39

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 39

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 39

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 39

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 39

Accepted Solution

by:
lcohan earned 500 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 69

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 69

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 39

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 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