Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trigger Troubles

Posted on 2014-03-06
16
Medium Priority
?
234 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 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
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…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

963 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