將有關誰刪除記錄的資訊傳遞到刪除觸發器

在設置審計跟踪時，我可以毫無問題地跟踪誰在表中更新或插入記錄，但是，跟踪誰刪除記錄似乎更成問題。

我可以通過在插入/更新欄位“UpdatedBy”中包含來跟踪插入/更新。這允許 INSERT/UPDATE 觸發器通過 訪問“UpdatedBy”欄位inserted.UpdatedBy。但是，使用 Delete 觸發器不會插入/更新數據。有沒有辦法將資訊傳遞給 Delete 觸發器，以便它知道誰刪除了記錄？

這是一個插入/更新觸發器

ALTER TRIGGER [dbo].[trg_MyTable_InsertUpdate] 
ON [dbo].[MyTable]
FOR INSERT, UPDATE
AS  

INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges) 
VALUES (inserted.ID, inserted.LastUpdatedBy)
FROM inserted 

使用 SQL Server 2012

有沒有辦法將資訊傳遞給 Delete 觸發器，以便它知道誰刪除了記錄？

是的：通過使用一個非常酷（且未被充分利用的功能）稱為CONTEXT_INFO. 它本質上是存在於所有範圍內且不受事務約束的會話記憶體。它可用於將資訊（任何資訊——好吧，任何適合有限空間的資訊）傳遞給觸發器以及在子程序/執行呼叫之間來回傳遞。我以前曾在完全相同的情況下使用過它。

• 上下文資訊是一個 VARBINARY(128)
• 設置方式：SET CONTEXT_INFO
• 通過：CONTEXT_INFO()

使用以下內容進行測試，看看它是如何工作的。請注意，我正在轉換CHAR(128)為CONVERT(VARBINARY(128), ... VARCHAR這是為了強制空白填充，以便在取出它時更容易轉換回它，CONTEXT_INFO()因為它VARBINARY(128)是用 s 右填充0x00的。

SELECT CONTEXT_INFO();
-- Initially = NULL

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                           CONVERT(CHAR(128), 'I deleted ALL your records! HA HA!')
                         );
SET CONTEXT_INFO @EncodedUser;

SELECT CONTEXT_INFO() AS [RawContextInfo],
      RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())) AS [DecodedUser];

結果：

0x492064656C6574656420414C4C20796F7572207265636F7264732120484120484121202020202020...
I deleted ALL your records! HA HA!

把它們放在一起：

1. 應用程序應該呼叫一個“刪除”儲存過程，該過程傳入正在刪除記錄的使用者名（或其他）。我假設這已經是正在使用的模型，因為聽起來您已經在跟踪插入和更新操作。
2. “刪除”儲存過程執行以下操作：

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                           CONVERT(CHAR(128), @UserName)
                         );
SET CONTEXT_INFO @EncodedUser;

-- DELETE STUFF HERE

3. 審核觸發器執行以下操作：

-- Set the INT value in LEFT (currently 50) to the max size of [UserWhoMadeChanges]
INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges) 
  SELECT del.ID, COALESCE(
                    LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50),
                    '<unknown>')
  FROM DELETED del;

4. 請注意，正如@SeanGallardy 在評論中指出的那樣，由於其他程序和/或臨時查詢從該表中刪除記錄，有可能：

• CONTEXT_INFO尚未設置，仍然是NULL：

出於這個原因，我更新了上面INSERT INTO AuditTable的內容以使用 aCOALESCE來預設值。或者，如果您不想要預設值並需要名稱，那麼您可以執行以下操作：

DECLARE @UserName VARCHAR(50); -- set to the size of AuditTable.[UserWhoMadeChanges]
SET @UserName = LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50);

IF (@UserName IS NULL)
BEGIN
   ROLLBACK TRAN; -- cancel the DELETE operation
   RAISERROR('Please set UserName via "SET CONTEXT_INFO.." and try again.', 16 ,1);
END;

-- use @UserName in the INSERT...SELECT

`CONTEXT_INFO`已設置為*不是*有效使用者名的值，因此可能超出`AuditTable.[UserWhoMadeChanges]`欄位的大小： 


�於這個原因，我添加了一個`LEFT`功能來確保抓取出來的任何東西`CONTEXT_INFO`都不會破壞`INSERT`. 如程式碼中所述，您只需將 設置為欄位`50`的實際大小。`UserWhoMadeChanges`




---


**SQL SERVER 2016 及更高版本的更新**


SQL Server 2016 添加了此每會話記憶體的改進版本：會話上下文。新的會話上下文本質上是一個鍵值對的雜湊表，其中“鍵”的類型為`sysname`(ie `NVARCHAR(128)`)，“值”的類型為`SQL_VARIANT`。意義：


1. 現在有一個價值分離，因此不太可能與其他用途發生衝突
2. 您可以儲存各種類型，不再需要擔心取回值時的`CONTEXT_INFO()`奇怪[行為](https://sqlquantumleap.com/2018/04/09/why-doesnt-context_info-return-the-exact-value-set-by-set-context_info/)via
3. 您可以獲得*更多*空間：每個“值”最多 8000 字節，所有鍵總共最多 256kb（相比之下，最大 128 字節`CONTEXT_INFO`）


有關詳細資訊，請參閱以下文件頁面：


* [sp_set_session_context](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-session-context-transact-sql)
* [SESSION_CONTEXT](https://docs.microsoft.com/en-us/sql/t-sql/functions/session-context-transact-sql)

您不能那樣做，除非您希望記錄 SQL 伺服器使用者 ID 而不是應用程序級別的使用者 ID。

您可以通過使用名為 DeletedBy 的列並根據需要進行設置來進行軟刪除，然後您的更新觸發器可以進行真正的刪除（或存檔記錄，我通常會在可能和合法的情況下避免硬刪除）以及更新您的審計跟踪. 要以這種方式強制刪除，請定義on delete引發錯誤的觸發器。如果您不想在物理表中添加列，則可以定義一個添加列的視圖並定義instead of觸發器來處理更新基表，但這可能是多餘的。

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