Best way to check whether a row has been updated in SQL.

Question

I have an update statement which updates a table. And there is a column records the last modified time. If the row data has not been changed, I don't want to change the last modified date time.

What is the best way to check whether an update statement will change the row of data or not.

Thanks,

Answer 1

Check the old vs. new data in your code instead of doing it in a query.

No need to bother the DB layer unnecessarily if data didn't change at all.

In short, if data didn't change, don't send the UPDATE statement.

Answer 2

If you want to do this preemptively, the only way I can think of that you will do this is to modify the WHERE clause of the update statement to compare the existing value vs the new value (for EVERY value). If ANY of them are not equal, then the update should take place.

Answer 3

One way is to start a transaction, select the contents of the row and compare it to what you're going to update it to. If they don't match, then do the update and end the transaction. If they match, rollback the transaction.

Answer 4

That's when a DAL is handy. It keeps track of all colums so if none changed then I don't even send an UPDATE statement to the database.

Answer 5

Sounds like you are going through a table and modifying some rows, then you want to go BACK through the table a second time and update the timestamp for the rows that were just changed.

Don't do it in two passes. Just update the date/time at the same time as you update whatever other columns you are changing:

UPDATE myTable
SET retailprice = wholesaleprice * 1.10,
    lastmodified = GetDate()
WHERE ...

Or are you issuing an update statement on ALL rows, but for most rows, it just sets it to the value it already has? Don't do that. Exclude those rows that wouldn't be modified in your where clause:

UPDATE myTable
SET retailprice = wholesaleprice * 1.10,
    lastmodified = GetDate()
WHERE retailprice <> wholesaleprice * 1.10

Answer 6

You COULD write an INSTEAD OF UPDATE trigger in T-SQL, where you could do what has been suggested above in the DAL layer -- compare the values in the existing record vs. the values in the update statement and either apply the update or not. You could use the Columns_Updated() function in the trigger to see if anything had been updated, and proceed accordingly.

It's not particularly efficient from the machine's point of view, but you could write it once and it would handle this situation no matter which application, stored procedure or other process was trying to update the record.

Answer 7

It depends on whether you have control of the data or not. Seb above is correct in saying you should check the old data against the new data before doing the update. But what if the data is not under your control?

Say you are a webservice being asked to do an update. Then the only way to check would be to query the existing data and compare it to the new data.

Don't know of any SQL functionality that would detect whether the update has actually changed any data or not.

There are ways in SQL to detect how many rows have been included in an update statement. Don't know of a way to detect whether an update statement actually changed any data, that would be interesting to know.