0

I have two tables TableA and TabelB in SQL Server, there is a foreign key relationship between them, and TableA defined a rowversion column to synchronize things.

It's required to update the rowversion of TableA if the corresponding record in TableB changed. Currently it's implemented with the following SQL code in the TableB update trigger:

UPDATE TableA 
SET Id = Id 
WHERE Id = @id

This change brought really bad performance to my app because of a Table Spool. And update other columns in TableB will trigger different behavior.

So I want to know if there is any other way to update the rowversion in TableA by not updating a TableA column? Any comment is much appreciated.

Dale K
  • 25,246
  • 15
  • 42
  • 71
moming00
  • 1
  • Trying to synchonize rowversions between tables sounds like a bad design choice. What happens when you introduce TableC and TableD, both of which have foreign keys back to TableA? Should all four tables get their rowversion incremented whenever any one record changes? – AlwaysLearning Oct 22 '20 at 08:08
  • No- the `rowversion` columns are **strictly internal** - we can *only* read them - nothing else. No trick, no hack, no workaround - period. – marc_s Oct 22 '20 at 08:23
  • Basically you cannot change the data in table without changing data in it. So no matter what column, you will do an update of row and, I think, get your spool operator. And as above commented, you finally can get a lot of cascading triggers that can bring an infinite cycle of triggers and updates. You can use least(rowversion) on all involved tables to find "not before" pointer and greatest to find "not after". – astentx Oct 22 '20 at 08:23

0 Answers0