TSql触发器仅需要在其值已更改的列上触发 [英] TSql Trigger needs to fire only on columns whose values have changed

问题描述

我编写了一个触发器，该触发器需要根据实际更新行中的哪些列对表执行一些不同的工作。我使用

  IF UPDATE（列名）
   
 
 
该部分工作正常。但是，事实证明，代码中的其他部分通过设置每个值（无论该值是否实际更改）来更新行，这会导致触发器触发已更新但其值实际上未更改的部分完全没有
 
 
 
由于更改导致此错误的代码可能不是一种选择，除了在INSERTED和DELETED表之间进行比较（在其中比较）之外，还有一种更简单的方法来防止这种情况发生如果IF更新没有意义）？
解决方案
 
 如果存在（选择* 
从插入的
中我加入了在I.Key = D.Key 
上D所在的位置
 I.Col<> D.Col）
 ... 
  
或使用表变量进行缓存，从而避免重复使用I和D。
 选择
当I.Col1是< D.Col1然后以EL1结束，否则为0，则为I.Col2的
情况。 D.Col2然后以EL2 0结束，作为Col2Diff，
 ... 
从
插入，我加入了删除的D ON I.Key = D.Key 
  
或结合构想来测试所有更改并退出触发器
 
I wrote a trigger that needs to do some different work on a table based on which columns in a row actually updated. I accomplished this using 
IF UPDATE(column-name)
That part works fine. It turns out, however, that there are other parts of the code that update rows by setting every single value whether the value actually changed or not and this causes the trigger to fire for parts that were "updated" but whose values did not actually change at all. 

As changing the code that's causing this is probably not an option, is there an easier way to prevent this other than having to compare between the INSERTED and DELETED tables (in which case the IF UPDATEs are meaningless)?
解决方案
IF EXISTS (SELECT *
          FROM
             INSERTED I JOIN DELETED D ON I.Key = D.Key
          WHERE
             I.Col <> D.Col)
...
or use a table variable to cache thus to avoid repeated use of I and D.
SELECT
    CASE WHEN I.Col1 <> D.Col1 THEN 1 ELSE 0 END AS Col1Diff,
    CASE WHEN I.Col2 <> D.Col2 THEN 1 ELSE 0 END AS Col2Diff,
    ...
FROM
    INSERTED I JOIN DELETED D ON I.Key = D.Key
or combine ideas to test all changes up front and exit the trigger

                        
这篇关于TSql触发器仅需要在其值已更改的列上触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！