SQL RowVersion number columns for change tracking problem

I've got a situation where I am using a RowVersion columns and a Binary(8) columns to track whether a row has been changed.

Ideally whenever:

RowVersion != Binary(8)

Then there has been a change in that record. The real problem with this is that I cannot find a good method to set the two columns to equal. If I update the Binary field, the update query increments the RowVersion f开发者_高级运维ield for that record. I'v messed with optimistically incrementing the Binary field, and it almost works. The key is I have to increment the Binary field by the total number of records that the UPDATE query will affect. Any idea on how to pause the rowversion, or determine what it will be at the end of an update statement to use the value IN the update statement?

For clarity, here's an example of what will work to make the two fields match:

    UPDATE [table] SET BinaryField = MyRowVersion + 
(SELECT COUNT(*) FROM [table] WHERE (MyRowVersion != BinaryField)) 
WHERE (MyRowVersion != BinaryField)

I'm not very sure that there is no better way to do this, but here's an option:

1.Create another table with only the PK of your table and a binary(8) field.

2.Copy the rowversion from the rows of your table to the respecting rows at the second table (at the time points you want).

3.Then, when you can later compare these two fields (rowversion, binary(8)).

--- 1 ---
CREATE TABLE MyTest 
( myKey INT PRIMARY KEY
, myData INT
, RV rowversion
) ;

CREATE TABLE MyTestCheck
( myKey INT PRIMARY KEY
, RVcheck binary(8)
, FOREIGN KEY (myKey) REFERENCES MyTest(myKey)
) ;

--- 2 ---
UPDATE MyTestCheck 
SET RVcheck = RV
FROM MyTest 
WHERE MyTest.myKey = MyTestCheck.myKey ;

INSERT INTO MyTestCheck
  SELECT myKey, RV
  FROM MyTest
  WHERE myKey NOT IN
    ( SELECT myKey
      FROM MyTestCheck
    ) ;

--- 3 ---
SELECT m.*, m2.RVcheck 
FROM MyTest AS m
  LEFT JOIN MyTestCheck AS m2
    ON m2.myKey = m.myKey 
WHERE m2.RVcheck <> m.RV          --- updates since last time procedure2 run
   OR m2.RVcheck IS NULL ;        --- inserts since  ...

You could check for deletes, using a FULL JOIN and having dropped the Foreign Key constraint.

I would use binary field on separate table that holds last rowversion value and put a after trigger on the table to sync those. Then join these two tables and compare them if they are same.