I need to trigger off 17 different columns and email the change from what it was to what it is changed to. The trigger below works but I feel like this is not the correct way to achieve this. Also, the email should only include fields that were changed. With this design all fields are in the email with "new" and "old" values even if the fields match. How do I remove if the values match?

ALTER TRIGGER [dbo].[Field_Change_Trigger]
ON [dbo].[Loan_Details]
after update
    DECLARE @subject varchar(255);
    DECLARE @recipients varchar(255);
    DECLARE @ID int;
    DECLARE @Status varchar(50);
    DECLARE @LoanAmount decimal(16,2);
    DECLARE @OldAmount decimal(16,2);
    DECLARE @APPDate date;
    DECLARE @OldApp date;
    DECLARE @FixedPymt decimal(16,2);
    DECLARE @OldPymt decimal(16,2);

    select  @LoanAmount = i.LOAN_AMOUNT, @OldAmount = d.LOAN_AMOUNT,  
            @FixedPymt = i.FIXED_PYMT_AMOUNT, @OldPymt = d.FIXED_PYMT_AMOUNT
     from inserted i inner join deleted d on i.LOAN_ID = d.LOAN_ID

SET @tableHTML =

(SELECT   'Amount Changes:'   +  char(9) +'New: '+ convert(varchar(10),@LoanAmount)  + '  '  +  char(9) + 'Old: ' + CONVERT(varchar(10),@OldAmount) + '  ' + char(10) +
        + 'Application Date:' +  char(9) +'New: '+ CONVERT(varchar(50),@AppDate)     + '  '  +  char(9) + 'Old: ' + CONVERT(varchar(50),@OldApp)    + '  ' + char(10) +
        + 'Fixed Pymt Amount:'+  char(9) +'New: '+ CONVERT(varchar(50),@FixedPymt)   + '  '  +  char(9) + 'Old: ' + CONVERT(varchar(50),@OldPymt)   + '  ' + char(10))
  • 1
    Welcome Angela! Some background info first would be helpful: What version of SQL Server are you using? How often do you expect this table to be updated? Triggers can end up causing some pretty bad performance if used incorrectly and the idea of sending an email from one makes me nervous :( Sep 28, 2018 at 22:26
  • Read below question stackoverflow.com/questions/253849/… Here is your answer Sep 29, 2018 at 9:20
  • We are using SQL server 2017 and there could be 20 changes per ID per day (max).
    – Angela
    Oct 1, 2018 at 20:43

1 Answer 1


Since you only want to fire the trigger if certain fields are updated, you will probably want to wrap the content of the trigger with

IF UPDATE([field name])
[trigger actions here]

That at least will limit the number of executions. I agree with the cautious statement by @LowlyDBA but since this will only fire 20 or so times a day, it's doubtful it would be a problem.

Once you've avoided unnecessary executions, I believe simplifying rest of the email contents in your SET statement can be resolved using a case statement. Something like

CASE WHEN i.value = d.value THEN '' ELSE 'email string value' END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.