CustomFieldHistoryTracking/InvoiceTrigger.sql

CREATE OR ALTER TRIGGER _trg_Attention_After_Update_Insert_InvoiceKvExt
ON ARRegisterKvExt
AFTER UPDATE, INSERT
AS
BEGIN
    INSERT INTO FieldHistoryTracker (
        CompanyID,
        FieldCode,
        KeyType,
        RelatedKey,
        UDF,
        Active,
        Import,
        FieldDescription,
        FieldValueCode,
        FieldValueText,
        AppliedDate,
        AppliedByUserID,
        AppliedByUserName,
        LastModifiedDateTime,
        LastModifiedByName,
        LastModifiedByID
    )
    SELECT
        c.CompanyID, 
        cd.AttributeID,
        'Invoice',
        c.RefNbr ,
        1,  -- UDF
        1,  -- Active
        0,  -- Import
        ca.Description,
        cd.ValueID, --Was not attributeID
        cd.Description,
        c.LastModifiedDateTime,
        c.LastModifiedByID,
        u.FullName,
        c.LastModifiedDateTime,
        u.FullName,
        c.LastModifiedByID
    FROM INSERTED i
    LEFT JOIN DELETED d
           ON d.CompanyID = i.CompanyID
          AND d.RecordID  = i.RecordID
          AND d.FieldName = i.FieldName
    JOIN ARRegisterKvExt cke 
         ON i.CompanyID = cke.CompanyID 
        AND i.RecordID  = cke.RecordID
        AND i.FieldName = cke.FieldName
    join ARRegister c
         ON c.CompanyID = cke.CompanyID 
        AND c.NoteID    = cke.RecordID
    JOIN CSAttributeDetail cd 
         ON cd.CompanyID    = cke.CompanyID
        AND cke.ValueString = cd.ValueID
    JOIN CSAttribute ca 
         ON ca.CompanyID   = cd.CompanyID
        AND ca.AttributeID = cd.AttributeID
    JOIN Users u 
         ON c.CompanyID = u.CompanyID
        AND u.PKID      = c.LastModifiedByID
    WHERE cd.AttributeID IN ('DISP')
      AND (
          -- 1) If there's no 'old' row (d.FieldName IS NULL), it's a new insert
          d.FieldName IS NULL
          OR
          -- 2) If there's an old row, check if something truly changed
          REPLACE(d.FieldName,'Attribute','') <> REPLACE(i.FieldName,'Attribute','')
          OR d.ValueString <> i.ValueString
      );
END;