深入了解SQL Server 2005 中的 COLUMNS_UPDATED函數

概述

COLUMNS_UPDATED函數能夠出現在INSERT或UPDATE觸發器中AS關鍵字後的任何位置，用來訓示表或視圖中有哪些列已被插入或者更新。它通常和IF語句一起使用，進而可以根據不同的結果，促使觸發器執行不同的操作。是以在DML觸發器中，COLUMNS_UPDATED函數是一個非常重要且有用的函數。

不同于UPDATE函數，COLUMNS_UPDATED函數可以工作在多個列中,它使用位元組中的位（Bit）辨別列是否已被修改（也就是采用二進制的方式），而不是在參數中傳遞列名。我們都知道，1位元組（BYTE）=8比特(Bit)，是以僅僅使用COLUMNS_UPDATED函數，則隻能夠辨別一個表或視圖的前八列是否被修改。如果表的列多于八列，那麼必須和SUBSTRING函數一起搭配使用，這将在後文中進行詳細讨論。

對COLUMNS_UPDATED函數的原理分析

COLUMNS_UPDATED函數傳回類型為varbinary，這是一種二進制類型，它可以表示一個或多個位元組，用來映射相關聯表的列。是以COLUMNS_UPDATED函數傳回一個或多個由左向右排序的位元組，這取決于要驗證更新的表是否擁有八個以上的列。最左側位元組表示列的序号為1至8的列，越偏向右側的位元組，其所代表的列的序号也就越大。而列序号的排序是在建立表時，由列被定義的順序所決定的。在每個位元組的内部，越偏向左側，列的序号越大，而左側位元組所代表的列的序号總小于右側位元組的。請允許我用一個簡單的示例來闡述上述過程：

我們假設有一張表TriggerDemo，它擁有24個列，分别為COL1、COL2、COL3直至COL24。那麼COLUMNS_UPDATED函數就會傳回3個位元組，在最左側的位元組内由右向左表示COL1至COL8，中間的位元組内由右向左表示COL9至COL16，最右側的位元組内由右向左表示COL17至COL24。

正如我之前所說，1位元組中的8比特就可以表示一張表的前八列，如果某一位為1，那麼它所表示的列被标記為已更新。反之為0，那麼就是未更新。如果多個列被更新，那麼就會存在有多個位被設定為1的情況。例如00001010就表示第二列和第四列已經被更新。

使用COLUMNS_UPDATED函數測試前八列

對于位（Bit）要說的是，第一個位稱為Bit0（而不是Bit1），是以1個位元組是由Bit0至Bit7共八個位所組成的。有幾個列被更新，COLUMNS_UPDATED函數就會傳回一個所對應的位設定為1的位元組。如果我們要想知道第二列和第四列是否被更新，就應該構造出一個位元組，并将Bit1和Bit3設定為1（00001010），然後需要将這個位元組轉換成十進制10，再和COLUMNS_UPDATED函數傳回的位元組進行按位邏輯與（&）操作，&運算的結果如果是10，就能夠保證第二列和第四列已經同時被更新。再結合使用不同的比較運算符（<、>、=），處理各種各樣的可能性，你就會真切體會到COLUMNS_UPDATED函數的功能是多麼的強大了。好了，為了便于了解，請看一個完整的代碼示範：

CREATE TRIGGER updDataTrigger

ON TriggerDemo

AFTER UPDATE AS

--測試第二列和第四列是否被同時更新

IF COLUMNS_UPDATED() & 10 =10

BEGIN

…………

END

--是否第二列或者第四列或者第二列和第四列同時被更新

ELSE IF COLUMNS_UPDATED() & 10 > 0 AND COLUMNS_UPDATED()&10<=10

--測試第二列或者第四列是否已被更新

ELSE IF COLUMNS_UPDATED() & 10 <10 AND COLUMNS_UPDATED() & 10>0

使用COLUMNS_UPDATED函數測試八個以上的列

一旦表擁有八個以上的列，COLUMNS_UPDATED函數将會傳回多個位元組。這時我們就需要借助SUBSTRING函數來截取一個特定的位元組，它可以從COLUMNS_UPDATED函數傳回的多個位元組中任意截取一個我們所想要的。

還記得我們前面的那張虛拟表TriggerDemo嗎？(就是擁有24個列的那個) 如果我們想測試它的第二列、第十列以及第二十四列是否已被更新，我們就需要使用SUBSTRING函數來截取到分别代表這些列的位元組，再重複前面測試前八列方法的步驟。我們參見一下代碼：

--測試第二列、第十列以及第二十四列是否同時被更新

IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&2=2)

AND (SUBSTRING(COLUMNS_UPDATED(),2,1)&2=2)

AND(SUBSTRING(COLUMNS_UPDATED(),3,1)&128=128)

總結

本文詳細講解了該如何使用COLUMNS_UPDATED函數，首先介紹了它的基本工作原理，然後對于測試前八列和八個以上的列這兩種情況，詳述了運用COLUMNS_UPDATED函數的兩種不同方法。為了便于了解它的功能強大，還插入了相關的代碼片段和引入了一個虛拟的表TriggerDemo。通過這種方式，我們就能夠深刻體會到為什麼它是UPDATE觸發器必不可少的助手。最後祝願大家程式設計愉快。