為什麼觸發器阻止更新？

我有一個每週執行的腳本。它更新了我數據庫中的多個表。結果是一條語句嘗試更新包含 250,000 行的表上的 150,000 行。這週在我在該表上引入觸發器後失敗了。

觸發器正在審計該表上的一列 - 如果該列中的值發生更改，則將一條記錄插入到具有相同名稱但在“審計”模式中的表中。

ALTER trigger [dbo].[tau_myTableName] on [dbo].[myTableName]
   after update
   as

   if exists (
       select 1 from inserted i join deleted d on i.primaryKeyId = d.primaryKeyId
       where   isNull(i.someColumn,-1) <> isNull(d.someColumn,-1)
       )

       insert into audit.myTableName (transaction_type, transaction_user, primaryKeyId, someColumn)
       select
       'U', SYSTEM_USER, primaryKeyId, someColumn
       from inserted;

掛起的更新聲明是：

UPDATE  myTableName
   SET     unrelatedEmailAddressColumn= 'someone@someemail.com' 
   WHERE   unrelatedEmailAddressColumn IS NOT NULL AND unrelatedEmailAddressColumn <> ''

為什麼該更新應該掛起？

啟用觸發器後，腳本不會在 20 分鐘內完成。在此之前的聲明一直存在。當我禁用觸發器時，腳本會在一分鐘內完成。該腳本在兩個數據庫上執行 - 如果我在第二個數據庫上禁用觸發器，則第一個完成，第二個掛起。這是一個使用者最少的測試環境，該腳本在還原後立即執行，並且sp_who2不會顯示任何意外連接。

觸發器應插入零行，因為它正在審核未由語句更新的列。也就是說，我發現在該表上更新後存在第二個觸發器。第二個觸發器更新正在更新的任何行中的列。這不應該衝突，但我沒有明確設置觸發順序，所以我將設置一個順序或合併觸發器然後再次測試。順便說一句，我從腳本文件中提取了更新語句，並在 SSMS 中只執行單個更新，並具有相同的掛起效果。

我建議盡可能使用此觸發器模板。當然，根據您的需要進行更改。

如果 DML 不引用觸發器關心的列，它會使用 IF UPDATE(column) 進行短路。它還使用 EXCEPT 子句作為我發現比較 INSERTED 和 DELETED 表的最快方法。只需向列表中添加更多列即可獲得更多比較，而無需擔心 NULL/非空值。

只需添加一個 IF UPDATE 塊就可以立即解決您的問題，只需對觸發邏輯進行最少的更改。

至於為什麼你的聲明掛了；INSERTED 和 DELETED 觸發器是沒有索引的堆。這使得在主鍵上加入它們是一項昂貴的操作，更不用說相關列的比較了。您可以使其盡可能快（如下所示），但避免它看似掛起的最佳選擇是批量處理您的語句。一次執行 5,000 行以避免對 tempdb 造成不必要的大壓力。

隨機附註；我們更進一步，我們沒有像這樣的審計觸發器，但是我們使用 INSTEAD OF INSERT/DELETE 來強制在我們的應用程序中使用軟刪除。但是，當我們確實需要硬刪除記錄時，我們有另一個短路檢查上下文資訊中的特殊二進制值，然後不會針對該值觸發。你也可以實現類似的東西。為應用程序保留觸發器，但是像大表維護這樣的特殊語句可能只會發出正確的上下文資訊，觸發器會忽略該語句。然後，您需要處理觸發器應該自己執行的邏輯，但觸發器是蠻力的。

ALTER TRIGGER trgMyTriggerName ON dbo.MyTableName 
AFTER UPDATE
AS
BEGIN

   /** Inside triggers, UPDATE(column) checks to make sure that the update statement 
       actually referenced the column.  NOTE that this does not mean the value actually 
       changed, just that the update (or whatever DML) statement referenced the column.

       If you use an ORM like entity framework then the whole row gets updated all the time
       anyways, regardless of if the value changed.  still, this is a good short circuit to
       prevent unnecessary logic from being executed.
       **/
   IF UPDATE(MyAuditColumn)
   BEGIN

       ;WITH CTE_Changed AS
           (
           SELECT I.PrimaryKeyID, I.AuditColumn
           FROM INSERTED AS I
           EXCEPT
           SELECT D.PrimaryKeyID, D.AuditColumn
           FROM DELETED AS D
           )
       INSERT INTO audit.MyTableName
           (transaction_type, transaction_user, primaryKeyId, someColumn)
       SELECT 'U', SYSTEM_USER, C.PrimaryKeyID, C.AuditColumn
       FROM CTE_Changed AS C

   END



END

我認為整個劇本現在很重要，

你也應該使用 Try Catch, Begin Trans。

觸發腳本可以重寫如下，原因有兩個。

首先，邏輯錯誤

第二個<>操作員通常很慢

       insert into audit.myTableName (transaction_type, transaction_user, primaryKeyId, someColumn)
       select
       'U', SYSTEM_USER, primaryKeyId, someColumn
       from inserted i
where Not Exists(select 1 from deleted d where  i.primaryKeyId = d.primaryKeyId
       where   isNull(i.someColumn,-1) = isNull(d.someColumn,-1)
)

請注意<>，我如何使用Not Exists

引用自：https://dba.stackexchange.com/questions/228801