CustomFieldHistoryTracking/ContractTrigger.sql

CREATE OR ALTER TRIGGER trg_AfterInsert_JobSTATUS_FieldHistoryTracker
ON FieldHistoryTracker
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- Update old records to set Active = 0 (False)
    UPDATE w
    SET w.Active = 0
    FROM FieldHistoryTracker w
    INNER JOIN inserted i 
        ON w.CompanyID = i.CompanyID
        AND w.RelatedKey = i.RelatedKey
        AND w.FieldCode = i.FieldCode
        where w.FieldCode in ('JOBSTATUS', 'INPROG', 'DISP')
	and w.Id != i.Id
        and i.Import = 0
        AND w.Active = 1;
End;

GO

CREATE OR ALTER TRIGGER _trg_Attention_After_Update_Insert_ContractKvExt
ON ContractKvExt
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,
        'Contract',
        c.ContractCD,
        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 ContractKvExt cke 
         ON i.CompanyID = cke.CompanyID 
        AND i.RecordID  = cke.RecordID
        AND i.FieldName = cke.FieldName
    JOIN Contract 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 ('JOBSTATUS', 'INPROG')
      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;