在 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

測試成功!

使用 COLUMNS_UPDATED() 及 UPDATE() 雖然無法區別資料是否被更改(修改前後內容改變),但因為耗用資源少,很適合當成第一道過濾防線,在其他無關欄位常被更新的情境仍能提升不少效能。

【後記】以上這些測試是用 SQL LocalDB 跑的,SSMS 要連 LocalDB 可在主機名稱輸入 (localdb)\MSSQLLocalDB。

Tips of using COLUMNS\_UPDATED() and UPDATE() toe detect column change in MSSQL triggers.


Comments

Be the first to post a comment

Post a comment


59 - 58 =