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