MSSQL Trigger 偵測欄位修改

2019-11-28 09:47 PM

在 MSSQL 上寫 Trigger，我想減少非必要的資料庫動作，故計劃在執行 Trigger 邏輯前進行過濾，鎖定特定欄位有異動才執行。原本想從 INSERTED、DELETED 資料表取出資料比對，不料目標欄位型別是超級古老的 NTEXT (SQL2005 起已被 NVARCHAR(MAX) 取代) 無法由 INSERTED 取得。另覓解法的過程，因禍得福學到新東西。

SQL 2014 起新增 COLUMNS_UPDATED() 函數，呼叫時會傳回 VARBINARY() 二進位旗標，第一欄被設定(UPDATE ... SET)為 0x01，第二欄設定為 0x02，若第一、三欄則是 0x05，以此類推。要比對某一欄是否被設定(註：只要有寫入值，修改前後值未變動也算)，可使用 COLUMNS_UPDATED() & 4 > 0 偵測。若資料表有八欄以上，則 VARBINARY() 會有多個 Byte，可用 SUBSTRING(COLUMNS_UPDATE(), n, 1) 取出第 n 個 Byte 進行計算。

依此原則，我建了 T1 (有 C1..C4 四個欄位)、T2 (有 C1..C9 九個欄位)兩個測試資料表，並加入以下 TRIGGER 偵測第三欄是否被修改：

CREATE TRIGGER TR_T1_UpdateTrigger
   ON  T1
   AFTER INSERT,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	PRINT COLUMNS_UPDATED()
    IF COLUMNS_UPDATED() & 4 > 0
	BEGIN
		PRINT 'Col3 IS SET'
	END
END
GO

CREATE TRIGGER TR_T2_UpdateTrigger
   ON  T2
   AFTER INSERT,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	PRINT COLUMNS_UPDATED()
    IF SUBSTRING(COLUMNS_UPDATED(), 1, 1) & 4 > 0
	BEGIN
		PRINT 'Col3 IS SET'
	END
END
GO

實測結果如下：

要取得欄位序號，可使用以下查詢：

SELECT TABLE_NAME, COLUMN_NAME,  
    COLUMNPROPERTY(
		OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),  
    COLUMN_NAME, 'ColumnID') AS COLUMN_ID  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = 'T1';

不過，以欄位順序偵測必須嚴防 Schema 調整欄位增刪或順序改變導致失敗。想鎖定特定欄位的異動，還有個更簡單的方法 UPDATE()。

將 Trigger 改寫用 IF UPDATE(Col3) 檢測 Col3 是否被設定：

CREATE TRIGGER TR_T1_UpdateTrigger
   ON  T1
   AFTER INSERT,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
    IF UPDATE(Col3)
	BEGIN
		PRINT 'Col3 IS SET'
	END
	ELSE
	BEGIN
		PRINT 'Col3 IS NOT SET'
	END
END

測試成功!