create database changetracking
go
use changetracking
ALTER DATABASE changetracking
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
go
CREATE TABLE Orders (OrderID     BIGINT not null Primary key,
                     CustID      BIGINT not null,
                     OrderStatus VARCHAR(20),
                     OrderDTM    DATETIME,
                     UpdateDTM   DATETIME)
 
ALTER TABLE Orders
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
go
INSERT INTO Orders
VALUES (1, 5, 'Ontvangen', GETDATE(), GETDATE())
select * from orders
SELECT * 
FROM CHANGETABLE(CHANGES Orders, NULL) CT
go
declare @laatste_versie bigint 
set @laatste_versie = CHANGE_TRACKING_CURRENT_VERSION()

select @laatste_versie

UPDATE Orders
SET OrderStatus = 'Klaar',
    UpdateDTM   = DATEADD(dd, 30,GETDATE())
WHERE ORDERID   = 1
select * from orders
SELECT * 
FROM CHANGETABLE(CHANGES Orders, @laatste_versie) CT

SELECT O.OrderID, OrderStatus, UpdateDTM 
  FROM Orders O
  JOIN CHANGETABLE(CHANGES Orders, @laatste_versie) CT
    ON O.OrderID = CT.OrderID
   AND CT.SYS_CHANGE_OPERATION = 'U'
 WHERE CHANGE_TRACKING_IS_COLUMN_IN_MASK( COLUMNPROPERTY( OBJECT_ID( 'Orders' ),'OrderStatus', 'COLUMNID' ), CT.SYS_CHANGE_COLUMNS ) = 1


use master
drop database changetracking