我想知道哪些列真正发生了变化

Question

我有一个在 SQLCLR 程序集中实现的后触发器。在其中我想知道哪些列已真正更新（并且它们的值已更改）。

不幸的是，SqlContext.TriggerContext.IsUpdatedColumn 返回 true，即使列值仍然相同。我猜想，这只是因为由不太智能的服务器应用程序准备的 SQL 查询会重写所有列，即使其中一些列尚未被用户更改。

第二个问题是某些列具有 ntext 类型，因此我什至无法从 INSERTED 伪表中选择它们（MS SQL Server 不允许从 INSERTED 中选择具有 ntext 类型的字段）。这就是为什么现在我使用以下查询 SELECT 更改的行：

SELECT * FROM [dbo].[MyTable] WHERE [id] IN (SELECT [id] FROM INSERTED)

我应该做什么来了解哪些列不仅被更新，而且被更改？

现在我有一个简单的想法：创建另一个触发器，BEFORE，并从内部保存更新的行。然后，当执行 AFTER 触发器时，比较列值。这个想法是我能做的最好的事情吗？如果是这样，在 BEFORE 和 AFTER 触发器之间保留更改的行的最佳位置是什么？由于我关闭了上下文连接（也许只是不关闭？），因此在执行 AFTER 触发器之前将删除临时表。

Answer 1

好的，现在我已经解决了这个问题。

首先，我创建了源表的完整副本（数据 + 结构）：

IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'copyTable')
SELECT * INTO copyTable FROM MyTable

然后，我将源表与其触发器开头的副本进行比较：

SELECT A.* FROM MyTable A, copyTable B WHERE
    A.id IN (SELECT [id] FROM INSERTED) AND
    A.id = B.id AND
    A.{0} <> B.{0}

将 {0} 替换为您需要的列。此栏正是您必须了解的栏目，是否更新。就我而言，它是动态定义的，但您可以静态计算所需的所有列。

Et voila - 您只选择了真正更改的行。

最后，在触发器结束时，不要忘记使用新值更新 copyTable：

UPDATE copyTable SET
    id = s.id,
    col1 = s.col1,
    ... all columns you'd like to control ...
FROM MyTable s WHERE
    s.id IN (SELECT [id] FROM INSERTED) AND
    copyTable.id = s.id

也许有更好的解决方案，但这也有效。

问候，