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))

• LowlyDBA - John M: 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 :(
• user2634333: Read below question stackoverflow.com/questions/253849/… Here is your answer
• Angela: We are using SQL server 2017 and there could be 20 changes per ID per day (max).

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