Solved

Trigger Troubles

Posted on 2014-03-06
16
221 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

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.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

860 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