如何在沒有遞歸的情況下使用觸發器更新同一行上的列？

SQL 伺服器 2012。

我需要更新專欄

$$ LastUpdated $$每當我的表中的記錄發生更改時，都會使用目前日期和時間。目前我有：

CREATE TRIGGER Trig_LastUpdated ON Contact AFTER UPDATE
AS
SET NOCOUNT ON

UPDATE ct
SET LastUpdated = GETDATE()
FROM Contact ct
INNER JOIN Inserted i
   ON ct.IDContact = i.IDContact

但這是遞歸的，我不希望這樣，導致死鎖和其他怪異。我無法全域關閉遞歸觸發器。我看到 INSTEAD OF 觸發器是非遞歸的，但是如果我這樣做，我必須檢查 Inserted 中的所有其他列以查看它是否已更新，或者 SQL Server 會為我處理嗎？最好的方法是什麼？

正如@ypercubeᵀᴹ 在評論中指出的那樣，如何在 SQL Server 2008 R2 表中添加“最後更新”列？有答案。檢查列是否已更新只是跳過邏輯並按我期望的方式工作。當使用者手動更新列時，他們會進行更新，並且不會導致遞歸。出於某種原因，我認為它會完全停止更新，但顯然情況並非如此。

鑑於您無法禁用遞歸觸發器，下一個最佳選項是：

1. 讓觸發器使用TRIGGER_NESTLEVEL函式檢測它的深度。如果它不是堆棧中的第一個觸發器執行，則在觸發器的開頭使用它來簡單地退出。類似於以下內容：

IF (TRIGGER_NESTLEVEL() > 1)
BEGIN
 -- Uncomment the following PRINT line for debugging
 -- PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
 RETURN;
END;

這將需要進行一些測試，以了解它如何受到另一個觸發器完成的初始插入的影響（以防萬一成為問題，但可能不會）。如果在被另一個觸發器呼叫時它沒有按預期工作，那麼嘗試設置一些參數給這個函式。有關詳細資訊，請參閱文件（上面連結）。 2. 使用SET CONTEXT_INFO在基於會話的“上下文資訊”中設置一個標誌。上下文資訊是VARBINARY(128)存在於會話級別的值，並在被覆蓋或會話結束之前保持其值。可以通過使用CONTEXT_INFO函式或context_info從以下任一 DMV 中選擇列來檢索該值：sys.dm_exec_requests和sys.dm_exec_sessions。

您可以將以下內容放在觸發器的開頭：

IF (CONTEXT_INFO() = 0x01)
BEGIN
 -- Uncomment the following PRINT line for debugging
 --PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
 RETURN;
END;
ELSE
BEGIN
 -- Uncomment the following PRINT line for debugging
 --PRINT 'Initial call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
 SET CONTEXT_INFO 0x01;
END;

如果您出於其他原因已經在使用上下文資訊，則此選項效果不佳。但是，任何使用 SQL Server 2016 的人都可以使用SESSION_CONTEXT，這是一組新的基於會話的鍵值對。

這些方法中的任何一種都比使用更可靠，IF NOT UPDATE(LastUpdated)因為該UPDATE(column_name)函式只能告訴您該列是否在SET子句中。它無法告訴您該值是否已更改，或者它是否已更改為GETDATE()您期望/想要的“目前”值。意思是，以下所有語句都繞過了觸發器的預期效果（即確保該LastUpdated列具有修改的實際日期和時間）：

UPDATE ct
SET    ct.LastUpdated = ct.LastUpdated
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = '1900-04-01`
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = 1
FROM   Contact ct
WHERE  ...

UPDATE ct
SET    ct.LastUpdated = GETDATE() + 90
FROM   Contact ct
WHERE  ...

最安全的方法可能是使用TRIGGER_NESTLEVEL()（選項 1）並傳入參數以僅檢查此特定觸發器，以便由於INSERT來自另一個觸發器的呼叫而不會對其產生不利影響：

TRIGGER_NESTLEVEL( @@PROCID , 'AFTER' , 'DML' )

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