How to update timestamp sql server column without changing record data

Question

I have a sql server table with a timestamp column. Is there a way to force the timestamp column to change without actually updating the record?

The reason I'm asking is because I want the record's timestamp to change when the record is inserted / updated / deleted in the child table.

Timestamp may not be the best way to do this. If not, what should I do? I do not want to use datetime, because I do not feel that this is a good way of version control. I do not want to use an integer because I do not want to read the value to increase it.

Suggestions?

Answer 1

I do not want to use an integer because I do not want to read the value to increase it.

UPDATE Table SET IntColumn = IntColumn + 1 

Although this technically requires reading, I do not see any problems with it.

You can always just upgrade to the same value:

 UPDATE Table SET SomeColumn = SomeColumn 

which will also run the rowversion update.

ADDITION: you can make a view with the maximum number of rows in child elements:

 SELECT Parent.*, MaxChildRowVersion as ChildVersion FROM Parent JOIN ( SELECT ParentId, MAX(RowVersion) as MaxChildRowVersion FROM Child GROUP BY ParentId ) as Child ON Parent.ParentId = Child.ParentId 

But no, you cannot directly update the rowversion column (although you can implement your own updatable using @@ DBTS, binary (8) and INSTEAD OF ... triggers)

Could you give examples of your last moment? That sounds promising.

No, actually it is not.;) It works too much when you can just update a single value or use a view. These are 2 easy options.

But to be complete, the binary (8) column with the default @@ DBTS (which returns the database version number) and the AFTER UPDATE trigger, which also updates the binary column for the new @@ DBTS, will give you a pseudo-noise type that You can update manually. This would not be faster or better than just updating some other column to the same value.

Answer 2

Ronnie

First of all, the timestamp syntax is deprecated by Microsoft, so you need to use rowversion , and the second rowversion always associated with the string.

From the documentation:

rowversion

Each database has a counter that increments for each insert or update operation that is performed on a table containing a rowversion column in the database. This counter is a rowversion database.

However, since it is implemented on an SQL server, you need to use triggers to achieve what you want.

Something like that:

 CREATE TRIGGER tgUpdateParentRowVersion ON ChildTable FOR INSERT, DELETE, UPDATE AS BEGIN /* * The updates below force the update of the parent table rowversion */ UPDATE ParentTable SET ChildUpdate = ChildUpdate + 1 FROM ParentTable a JOIN inserted i on a.pkParentTable = i.fkParentTable UPDATE ParentTable SET ChildUpdate = ChildUpdate - 1 FROM ParentTable a JOIN deleted d on a.pkParentTable = d.fkParentTable END 

Answer 3

Have you studied the use of triggers ? It seems more natural for what you need.

I worked on an application that had timestamps in all directions; exactly what we had to do with them turned out to be pretty elusive, so we tested them out of the circuit. You can read the timestamp in the objects created from the data, and check if it changed when you switch to updates and rollbacks, if you see that the data is dirty, but that there are a lot of templates to get a big win (at least in the domain in which I worked).

Answer 4

What I'm going to do until a better idea comes up is to add a DateTime column to the parent table DateChildrenChanged.

I will add a trigger to the child table, which will set the DateChildrenChanged column to the current DateTime, and when that happens, the timestamp column in the parent table will also be updated.

This way I use the timestamp column for version control.

I am still very open to ideas.