SQL Server 2012：插入和触发器之间的DATETIME差异 [英] SQL Server 2012: DATETIME discrepancies between inserts and triggers

问题描述

我们遇到了一个非常奇怪的问题，即调用 GETUTCDATE（）时，第二个语句中的返回值比第一个语句稍早。我们的情况如下：

We are having a very strange problem whereby when calling GETUTCDATE() the returned value is very slightly earlier in the second statement than the first. The scenario we have is the following:

1. 我们将其插入到表中以跟踪用户的当前状态，该表具有触发器在该表上，将使用 DATETIME 字段将过去的状态插入到用户的关联历史记录表中，并且插入调用 GETUTCDATE（）

1. We insert into a table to track a users current status, this table has a trigger on which inserts into an associated history table of the users past statuses with a DATETIME field and the insert calls GETUTCDATE()

完成此操作后，我们将插入另一个具有关联记录的表，以调用 GETUTCDATE（）

Once this is done we have another table with an associated record we insert into calling GETUTCDATE() which tracks which part updated the user status.

因此流程如下：

1. 插入 UserStatuses


2. 在触发触发器将UserStatuses 表插入 UserStatusesHistory 调用 GETUTCDATE（）


3. 然后我们将其插入 OwningStatuses 表中，调用 GETUTCDATE（）

1. Insert into UserStatuses
2. Trigger fires on UserStatuses table inserting into UserStatusesHistory calling GETUTCDATE()
3. Then we insert into OwningStatuses table calling GETUTCDATE()

我们发现在某些情况下 UserStatusesHistory 中的 DATETIME 在 OwningStatuses 表的 DATETIME 之后。

We are finding that on some occasions the DATETIME on the UserStatusesHistory is after the DATETIME on the OwningStatuses table.

在 GETUTCDATE（之后，在 OwningStatuses 上的 GETUTCDATE（）被调用时，情况如何？ ）用于 UserStatusesHistory 表？

How can this be when the GETUTCDATE() on the OwningStatuses is called after the GETUTCDATE() for the UserStatusesHistory table?

在某些情况下触发器是异步运行的吗？ （我不敢相信，因为这与我已阅读的所有内容都存在，并且我们没有使用任何服务代理）。

In some scenarios are triggers run asynchronously? (I can't believe this as it's against everything I have read and we are not using any service brokers).

在某些情况下，可能在过程开始时缓存了 GETUTCDATE（），并且该缓存的值

Is it possible the GETUTCDATE() is cached at the beginning of the procedure on some occasions and this cached value isn't carried into the trigger?

推荐答案

由于SQL的声明性，数据库引擎可能可以自由选择以它认为合适的任何顺序评估SQL语句的各个部分（只要它不影响语义）。您的建议 GETUTCDATE（）可能被缓存，这是一个合理的建议。

Due to the declarative nature of SQL, the database engine might take the liberty to evaluate parts of a SQL statement in any order it sees fit (as long as it doesn't affect semantics). Your suggestion that GETUTCDATE() might be cached, is a plausible one.

我知道这不能回答您的问题题。但是，无论SQL2012中GETUTCDATE的实现如何，在将来的版本中它都可能会发生变化。因此，请避免依赖它，否则将来的升级可能会成为真正的痛苦。以不依赖任何有关评估顺序的假设的方式实施您的逻辑。

I know this does not answer your question. But whatever the implementation of GETUTCDATE in SQL2012, it might well change in a future version. So avoid relying on it, or future upgrades could become a real pain. Implement your logic in a way that does not rely on any assumptions regarding evaluation order.

在您的特定情况下，我看到一些可能的解决方案。

In your specific case, I see a few possible solutions.

1. 如果您对 OwningStatuses 的时间稍晚于 code> UserStatusesHistory ，那么如果将第三步作为单独的批处理发送到SQL Server，则可能已经有所帮助。


2. 交换步骤2和3 ;并让触发器查询 OwningStatuses 而不是自己确定日期。


3. 使用触发器停止；出于多种原因考虑这一点。

1. If you have no problem with OwningStatuses having a time slightly later than UserStatusesHistory, then it might already help if you send the third step off to SQL Server as a separate batch.
2. Swap steps 2 and 3; and let the trigger query OwningStatuses instead of making up its own date.
3. Stop using triggers; there is more than one reason to consider this.

这篇关于SQL Server 2012：插入和触发器之间的DATETIME差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！