SQL DB Trigger - mutiple column changes

Question

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
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @tableHTML NVARCHAR(MAX) = N'';
    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,  
            @APPDate = i.DATE_OF_APPLICATION, @OldApp = d.DATE_OF_APPLICATION,
            @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))

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])
BEGIN
[trigger actions here]
END

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