locked
Email Trigger - email body blank RRS feed

  • Question

  • Hi,

    Have created an email trigger that fires when a user records data incorrectly from a business point of view. Appreciate this is not generally recommended but only occurs very rarely - perhaps once or twice a day across the whole company.

    The process worked fine when tested it on the back-end SQL Server database. I entered incorrect data, the trigger fired and an email arrived in my Inbox telling me what a naughty boy I'd been.

    However, when the data is entered through the .net cms front end the email fires but no body is showing in it. There is a title, a 'To' email and a 'CC' email but no message in the email body.

    The trigger code is as follows:

    USE [BO_DB]
    GO
    
    /****** Object:  Trigger [dbo].[Incorrect_AppDate_Data]    Script Date: 11/04/2011 10:00:07 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    CREATE TRIGGER [dbo].[Incorrect_AppDate_Data] ON [dbo].[TAT_TIME] AFTER INSERT 
    
    AS
    
    BEGIN 
    
    DECLARE @ApptDate datetime, 
    @Tran_Date datetime, 
    @Action_Code varchar(6), 
    @Action_Desc VARCHAR(50),
    @Clnt_Matt_Code varchar(20),
    @email varchar(100),
    @msg varchar(max),
    @Time_Uno INT,
    @Case_Code varchar(15),
    @Matter_Uno INT,
    @Matter_name VARCHAR(20),
    @Client_name VARCHAR(50),
    @Rank_Desc VARCHAR(50),
    @tobill_hrs MONEY, 
    @tobill_amt MONEY,
    @Base_rate MONEY,
    @Narrative VARCHAR(250)
    
    SELECT @ApptDate = m._Appointment_date, @Tran_Date = INSERTED.tran_date, 
    @Action_Code = INSERTED.Action_Code, @Clnt_Matt_Code = m.clnt_matt_code
    FROM INSERTED 
    INNER JOIN 
    Tat_time t ON INSERTED.time_uno = t.time_uno
    INNER JOIN
    HBM_matter m  
    ON m.matter_uno = INSERTED.Matter_uno 
    
    IF @Tran_Date < @ApptDate AND (@Action_Code <> '1PRE' or @Clnt_Matt_Code NOT LIKE '%PRE%')
    
    BEGIN
    
    SELECT @email = p.email, @Case_Code = m.client_code, @Time_uno = t.time_uno, @Matter_Uno = m.matter_uno, @Tran_date = t.tran_date, 
    @Action_Desc = a.action_desc, @Rank_Desc = r.rank_desc, @tobill_hrs = t.tobill_hrs, @tobill_amt = t.tobill_amt, 
    @Base_rate = (t.tobill_amt / nullif(t.tobill_hrs, 0)), @Narrative = tx.txt1, @Matter_name = m.matter_name, @Client_name = c.client_name, 
    @ApptDate = m._appointment_date
    FROM INSERTED i
    INNER JOIN tat_time t ON t.time_uno = i.time_uno
    INNER JOIN dbo.HBM_Persnl p ON p.empl_uno = t.entry_empl_uno
    INNER JOIN HBM_matter m ON m.matter_uno = t.Matter_uno
    LEFT JOIN tbl_rank r ON t.rank_code = r.rank_code
    INNER JOIN tal_action a ON a.action_code = t.action_code
    LEFT JOIN tat_text tx ON t.nar_text_id = tx.text_id
    INNER JOIN HBM_Client c ON c.client_uno = m.client_uno
    WHERE m.client_code <> '03RH017'
    and matter_code <> 'LPA'
    
    SET @msg = 'Dear User,
    
    You have recorded time incorrectly on "' + @Case_Code + '".  
    
    The incorrect entry is : Matter Uno: "' + CAST(@Matter_Uno AS VARCHAR(10))+'" ' + 
    					  ' Client Matter Code: "' + @Clnt_matt_code +'" ' + 
    					  ' Time Uno: "' + CAST(@Time_Uno AS VARCHAR(10))+'" ' + 
    					  ' Transaction Date: "' + CAST(@Tran_date AS VARCHAR(20)) + '" ' + 
    					  ' Action Code: "'  + @action_code +'" ' + 
    					  ' Action Description: "' + @Action_Desc +'" ' + 
    					  ' Rank Description: "' + @Rank_Desc +'" ' + 
    					  ' ToBill Hours: "' +  CAST(@tobill_hrs AS VARCHAR(10)) +'" ' + 
    					  ' ToBill Amount: "' + CAST(@tobill_amt AS VARCHAR(10)) +'" ' + 
    					  ' Base Rate: "' + CAST(@Base_rate AS VARCHAR(10)) +'" ' + 
    					  ' Narrative: "' + @Narrative +'" ' + 
    					  ' Matter Name: "' + @Matter_name +'" ' + 
    					  ' Client Name: "' + @Client_name +'" ' + 
    					  ' Appointment Date: "' + CAST(@ApptDate AS VARCHAR(20)) +'"
    
    The time incorrectly recorded relates to work undertaken in the period prior to appointment and should be recorded against the ‘pre-appointment work’ time code and on the ‘pre-appointment file’ in accordance with the firm’s time recording policy (see intranet). 
    
    Please rectify the time entry immediately. Please note that where the time has already been ‘released’, it will be necessary to contact the Finance Helpdesk. The email address is "Helpdesk@triggertrouble.com". 
    
    Regards,
    
    Tech Team' 
     
    -- CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail 
    @recipients = @email,
    @copy_recipients = 'j.smith@triggertrouble.com', 
    @body= @msg,  
    @subject = 'Time Error Email', 
    @profile_name = 'Recording_Errors'
    
    END
    
    ELSE IF   @Tran_Date > @ApptDate AND (@Action_Code = '1PRE' OR @Clnt_Matt_Code LIKE '%.PRE%')
    
    BEGIN 
    
    SELECT @email = p.email, @Case_Code = m.client_code, @Time_uno = i.time_uno, @Matter_Uno = m.matter_uno, @Clnt_Matt_Code = m.clnt_matt_code, 
    @tran_date = t.tran_date, @Action_Code = t.Action_Code, @Action_Desc = a.action_desc, @Rank_Desc = r.rank_desc, @tobill_hrs = t.tobill_hrs, 
    @tobill_amt = t.tobill_amt, @Base_rate = (t.tobill_amt / nullif(t.tobill_hrs, 0)), @Narrative = tx.txt1, @Matter_name = m.matter_name, 
    @Client_name = c.client_name, @ApptDate = m._appointment_date
    FROM INSERTED i
    INNER JOIN tat_time t ON t.time_uno = i.time_uno
    INNER JOIN dbo.HBM_Persnl p ON p.empl_uno = t.entry_empl_uno
    INNER JOIN HBM_matter m ON m.matter_uno = t.Matter_uno
    LEFT JOIN tbl_rank r ON t.rank_code = r.rank_code
    INNER JOIN tal_action a ON a.action_code = t.action_code
    LEFT JOIN tat_text tx ON t.nar_text_id = tx.text_id
    INNER JOIN HBM_Client c ON c.client_uno = m.client_uno
    WHERE m.client_code <> '03RH017'
    and matter_code <> 'LPA'
    
    SET @msg = 'Dear User,
    
    You have recorded time incorrectly on"' + @Case_Code+ '".  
    
    The incorrect entry is : Matter Uno: "' + CAST(@Matter_Uno AS VARCHAR(10))+'" ' + 
    					  ' Client Matter Code: "' + @Clnt_matt_code +'" ' +
    					  ' Time Uno: "' + CAST(@Time_Uno AS VARCHAR(10))+'" ' + 
    					  ' Transaction Date: "' + CAST(@tran_date AS VARCHAR(20)) + '" ' + 
    					  ' Action Code: "'  + @action_code +'" ' + 
    					  ' Action Description: "' + @Action_Desc +'" ' + 
    					  ' Rank Description: "' + @Rank_Desc +'" ' + 
    					  ' ToBill Hours: "' +  CAST(@tobill_hrs AS VARCHAR(10)) +'" ' + 
    					  ' ToBill Amount: "' + CAST(@tobill_amt AS VARCHAR(10)) +'" ' + 
    					  ' Base Rate: "' + CAST(@Base_rate AS VARCHAR(10)) +'" ' + 
    					  ' Narrative: "' + @Narrative +'" ' + 
    					  ' Matter Name: "' + @Matter_name +'" ' + 
    					  ' Client Name: "' + @Client_name +'" ' + 
    					  ' Appointment Date: "' + CAST(@ApptDate AS VARCHAR(20)) +'"
    
    The time incorrectly recorded relates to work undertaken after the date of appointment and should therefore be recorded against a time code other than ‘pre-appointment work’ time code on the appointment file in accordance with the firm’s time recording policy (see intranet).
     
    Please rectify the time entry immediately.  Please note that where the time has already been ‘released’, it will be necessary to contact the Finance Helpdesk. The email address is "Helpdesk@triggertrouble.com". 
    
    Regards,
    
    Tech Team' 
    
    EXEC msdb.dbo.sp_send_dbmail 
    @recipients = @email,
    @copy_recipients = 'j.smith@triggertrouble.com', 
    @body = @msg,  
    @subject = 'Time Error Email', 
    @profile_name = 'Recording_Errors'
    
    END
    
    END
    
    GO
    

    My theory why this is working fine through SQL Server but not when run through the .net cms front-end is that for some reason the .net cms system is not recognising the INSERTED table in the IF statement.

    Not sure how else I could link the insert to the sql in the email body though. If removed INSERTED reference & just called it tat_time how would the SQL in the IF statement know which row was being referred to.

     

     

     

     

     


    Cheers, AndySugs
    Friday, November 4, 2011 3:53 PM

Answers

  • Hi Stephanie, Wrapping the select variables in ISNULL did solve the problem! For whatever reason the employee rank was coming through as NULL (it shouldn't be doing but that's another thread!) Thanks for your help though!
    Cheers, AndySugs
    • Marked as answer by AndySugs Monday, November 14, 2011 1:41 PM
    Monday, November 14, 2011 1:35 PM

All replies

  • Hi AndySugs,

    Does the data inserted into database is the same as when you tested it on the back-end SQL Server database?

    I would recommend you use SQL Server Profiler to trace the trigger execution for further analysis. Please select the SP:StmtCompleted event beneath the Stored Procedures on the Events Selection when you check Show all events option.

    For more information: Tracing Trigger Execution (and code path) using SQL Profiler.

    Best Regards,
    Stephanie Lv


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, November 8, 2011 6:12 AM
  • Hi Steph,

    Thanks for your help. Totally agree that best bet would be running trace but it's been a while since I've used profiler & so was gonna brush up on old notes/books (Am just waiting for the impossible to happen - a couple of hours of uninterrupted peace and quiet!). On another forum someone suggested that could be because some of the data entered is null and should wrap all variables with Coalesce/Isnull. Do you think this could possibly be the cause?

     


    Cheers, AndySugs
    Tuesday, November 8, 2011 6:38 PM
  • Hi AndySugs,

    The scenario is possible but I cannot confirm this is the cause to your problem here. Also it is an appropriate suggestion to avoid this type of issue. If you dig into trigger by tracing, you can find whether it the cause of not.

    Best Regards,
    Stephanie Lv


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, November 9, 2011 2:05 AM
  • Hi Stephanie, Wrapping the select variables in ISNULL did solve the problem! For whatever reason the employee rank was coming through as NULL (it shouldn't be doing but that's another thread!) Thanks for your help though!
    Cheers, AndySugs
    • Marked as answer by AndySugs Monday, November 14, 2011 1:41 PM
    Monday, November 14, 2011 1:35 PM