'get columns other than primary key from CHANGETABLE function sql-server

I have two table (InvoiceRequests, InvoiceRequestsLineItems), InvoiceRequestsLineItems has InvoiceRequestId as forign key, Qauntity and Rate columns.

My both tables has change tracking enabled.

I'm writing a query that returns the Only the InvoiceRequests that has been changed with there respective Amount(calculated from Qauntity and Rate columns of InvoiceRequestsLineItems).

the scenario is when I update/insert into LineItems I Join the LineItems table and ChangeTable to get the InvoiceRequestId. So I shows customers that 'InvoiceRequest' Amount changed.

But when I delete any LineItems I lost the InvoiceRequestId(foreign key) from that row. Then I cant able to tell the amount has been changed for perticular InvoiceRequests.

Is there any way that ChangeTable function can return other columns(InvoiceRequestId) apart from PrimaryKey(InvoiceRequestLineItemId).

Solution like adding trigger and keep the deleted record in separate table will increase lot of overhead.

Please provide any suggestion, that I can do with minimal changes. Thanks

SELECT 
      CTIRL.INVOICEREQUESTSID [InvoiceRequestId], 
      IR.STATUS [Status],
      ERIL.[InvoiceRequestAmount], 
      CAST(0 as BIT) [Deleted]
    FROM 
    (SELECT [IRS].INVOICEREQUESTSID, CTL.SYS_CHANGE_COLUMNS, CTL.sys_change_operation 
        FROM INVOICEREQUESTLINEITEMS [IRS] 
        JOIN CHANGETABLE( CHANGES dbo.INVOICEREQUESTLINEITEMS, @ctversion) CTL
        ON CTL.INVOICEREQUESTLINEITEMSID = [IRS].INVOICEREQUESTLINEITEMSID
    ) AS CTIRL    
    LEFT JOIN dbo.INVOICEREQUESTS IR 
    ON CTIRL.INVOICEREQUESTSID = IR.INVOICEREQUESTSID 
    LEFT JOIN (
        SELECT 
            IRLI.INVOICEREQUESTSID, 
            SUM(
            IRLI.QUANTITY * IRLI.RATE + COALESCE(IRLI.TAXAMOUNT, 0)
            ) [InvoiceRequestAmount] 
        FROM 
            INVOICEREQUESTLINEITEMS IRLI 
        GROUP BY 
            IRLI.INVOICEREQUESTSID
        ) AS ERIL
    ON ERIL.INVOICEREQUESTSID = RES.InvoiceRequestId 
    WHERE 
      (
        CHANGE_TRACKING_IS_COLUMN_IN_MASK(5, CTIRL.SYS_CHANGE_COLUMNS) = 1 
        OR CHANGE_TRACKING_IS_COLUMN_IN_MASK(7, CTIRL.SYS_CHANGE_COLUMNS) = 1  
        OR CHANGE_TRACKING_IS_COLUMN_IN_MASK(11, CTIRL.SYS_CHANGE_COLUMNS) = 1        
        OR CTIRL.sys_change_operation = 'D' 
        OR CTIRL.sys_change_operation = 'I'
      )


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source