sql to maintain the history of transactional table

Question

I want to write sql to maintain the history of these transaction as to capture the start status and end status that are colored so can be inserted in other table

the above image shows a self join of the same table suing row_number as a.rn+1 = b.rn

Answer 1

Rather than building this yourself, my advise is turn on Change Data Capture for the table. Now Sql Server will keep track of this for you.

Note that this feature currently requires Enterprise Edition, but starting with 2016 sp1 it will be included in Standard Edition.