TRG_ITEM_History

-- Tracking any update or delete on item table

ALTER TRIGGER [dbo].[TRG_ITEM_History]
ON [dbo].[Item]
AFTER UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- ==========================
    -- DELETE Tracking
    -- ==========================
    IF EXISTS (SELECT 1 FROM deleted) AND NOT EXISTS (SELECT 1 FROM inserted)
    BEGIN
        INSERT INTO dbo.ITEM_History
        (
            ActionType, ActionDate, ActionBy, ChangeRemarks,
            ID, Code, HsCode, ItemName, Description,
            ItemGroupID, ItemSubGroupID, ItemTypeID,
            UnitID, BrandID, OriginCountryID,
            Creator, CreationDate, Modifier, ModificationDate
        )
        SELECT
            'DELETE', GETDATE(), ISNULL(d.Modifier, CONVERT(INT, USER_ID())), 'Deleted',
            d.ID, d.Code, d.HsCode, d.ItemName, d.Description,
            d.ItemGroupID, d.ItemSubGroupID, d.ItemTypeID,
            d.UnitID, d.BrandID, d.OriginCountryID,
            d.Creator, d.CreationDate, d.Modifier, d.ModificationDate
        FROM deleted d;
    END


    -- ==========================
    -- UPDATE Tracking (Old Data)
    -- ==========================
    IF EXISTS (SELECT 1 FROM deleted) AND EXISTS (SELECT 1 FROM inserted)
    BEGIN
        INSERT INTO dbo.ITEM_History
        (
            ActionType, ActionDate, ActionBy, ChangeRemarks,
            ID, Code, HsCode, ItemName, Description,
            ItemGroupID, ItemSubGroupID, ItemTypeID,
            UnitID, BrandID, OriginCountryID,
            Creator, CreationDate, Modifier, ModificationDate
        )
        SELECT
            'UPDATE',
            GETDATE(),
            ISNULL(i.Modifier, CONVERT(INT, USER_ID())),

            ISNULL(
                NULLIF(
                    STUFF(
                        (
                            CASE WHEN ISNULL(d.UnitID,0) <> ISNULL(i.UnitID,0)
                                THEN ' & Unit Changed' ELSE '' END
                            +
                            CASE WHEN ISNULL(d.ItemGroupID,0) <> ISNULL(i.ItemGroupID,0)
                                THEN ' & Category Changed' ELSE '' END
                            +
                            CASE WHEN ISNULL(d.ItemSubGroupID,0) <> ISNULL(i.ItemSubGroupID,0)
                                THEN ' & SubCategory Changed' ELSE '' END
                            +
                            CASE WHEN ISNULL(d.ItemTypeID,0) <> ISNULL(i.ItemTypeID,0)
                                THEN ' & Type Changed' ELSE '' END
                            +
                            CASE WHEN ISNULL(d.BrandID,0) <> ISNULL(i.BrandID,0)
                                THEN ' & Brand Changed' ELSE '' END
                            +
                            CASE WHEN ISNULL(d.OriginCountryID,0) <> ISNULL(i.OriginCountryID,0)
                                THEN ' & Origin Country Changed' ELSE '' END
                            +
                            CASE WHEN ISNULL(d.ItemName,'') <> ISNULL(i.ItemName,'')
                                THEN ' & ItemName Changed' ELSE '' END
                            +
                            CASE WHEN ISNULL(d.Code,'') <> ISNULL(i.Code,'')
                                THEN ' & Code Changed' ELSE '' END
                        ),
                        1, 3, ''
                    ),
                    ''
                ),
                'No Change'
            ) AS ChangeRemarks,

            -- OLD DATA STORE
            d.ID, d.Code, d.HsCode, d.ItemName, d.Description,
            d.ItemGroupID, d.ItemSubGroupID, d.ItemTypeID,
            d.UnitID, d.BrandID, d.OriginCountryID,
            d.Creator, d.CreationDate, d.Modifier, d.ModificationDate

        FROM deleted d
        INNER JOIN inserted i ON d.ID = i.ID;
    END
END;