C# 在 SQL 中检查行是否已更新的最佳方法

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

提问by J.W.

I have an update statement which updates a table. And there is a column that records the last modified time. If data in a specific row 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,

谢谢，

采纳答案by Seb

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.

如果数据根本没有改变，则无需不必要地打扰 DB 层。

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

简而言之，如果数据没有改变，就不要发送 UPDATE 语句。

回答by casperOne

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 ANYof them are not equal, then the update should take place.

如果您想抢先执行此操作，我能想到的唯一方法是修改更新语句的 WHERE 子句以比较现有值与新值（对于每个值）。如果它们中的任何一个不相等，则应该进行更新。

回答by brian-brazil

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.

一种方法是启动一个事务，选择该行的内容并将其与您要更新的内容进行比较。如果它们不匹配，则执行更新并结束事务。如果它们匹配，则回滚事务。

回答by Otávio Décio

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.

这时候 DAL 就派上用场了。它跟踪所有列，因此如果没有更改，那么我什至不会向数据库发送 UPDATE 语句。

回答by BradC

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:

或者您是否对所有行发出更新语句，但对于大多数行，它只是将其设置为已有的值？不要那样做。排除那些不会在您的 where 子句中修改的行：

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

回答by Rob Schripsema

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.

您可以在 T-SQL 中编写 INSTEAD OF UPDATE 触发器，您可以在其中执行上面在 DAL 层中建议的操作——比较现有记录中的值与更新语句中的值，然后应用或不应用更新. 您可以使用触发器中的 Columns_Updated() 函数来查看是否已更新任何内容，并相应地进行。

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.

从机器的角度来看，它不是特别有效，但是您可以编写一次，无论哪个应用程序、存储过程或其他进程试图更新记录，它都会处理这种情况。

回答by James Piggot

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?

这取决于您是否可以控制数据。上面的 Seb 说你应该在更新之前检查旧数据和新数据是正确的。但是如果数据不在您的控制之下呢？

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.

不知道是否有任何 SQL 功能可以检测更新是否实际更改了任何数据。

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.

SQL 中有多种方法可以检测更新语句中包含了多少行。不知道有什么方法可以检测更新语句是否实际更改了任何数据，这很有趣。

回答by IsmailS

If you are using sql 2005/2008 then you can do as follows in the stored procedure.

如果您使用的是 sql 2005/2008，那么您可以在存储过程中执行以下操作。

update  newTable 
set     readKey='1'
output  inserted.id,
        inserted.readKey as readKey,
        deleted.readKey as prevReadKey
into @tempTable
where id = '1111'

Then you can select from @tempTable to verify if the prevReadKey and readKey has similar value if both has similar value you can reset your last modified datetime.

然后您可以从@tempTable 中选择以验证 prevReadKey 和 readKey 是否具有相似的值，如果两者具有相似的值，您可以重置上次修改的日期时间。

This way you don't have to fire multiple queries on the table in the case when a value is actually changing. But yes in the case when the value is not changing, this will be firing two update statements where none is required. This should be OK if those cases are rare.

这样您就不必在值实际更改的情况下在表上触发多个查询。但是，在值不变的情况下，这将触发两个不需要的更新语句。如果这些情况很少见，这应该没问题。

P.S. NOTE:- The query given might be syntactically wrong as it is not tested. But this is the way your problem can be solved. I have done it in following way using OUTPUT clause with Merge statement in one of my project and it can be done with update statement too. Here is the reference of OUTPUT Clause

PS 注意：- 给出的查询可能在语法上是错误的，因为它没有经过测试。但这是可以解决您的问题的方法。我在我的一个项目中使用 OUTPUT 子句和 Merge 语句以下列方式完成了它，它也可以用更新语句完成。这是OUTPUT 子句的参考