積沙成塔

在DML Trigger中若想判斷某些資料行是否更新可使用Update方法來達到，該方法會回傳布林值，當回傳值為真時表示該欄位嘗試被更新或新增，筆者以論壇上網友提出的問題來當範例。假設希望更新UserInfo資料表的Name資料行時，同時更新ModiDate資料行，更新非Name資料行時則不更新ModiDate欄位。

• 以下程式碼示範建立測試資料：

   1:  use Northwind

   2:  go

   3:   

   4:  --建立測試資料表

   5:  if exists (select * from sys.objects where type = 'U' and name = 'UserInfo')

   6:      drop table UserInfo

   7:  go

   8:   

   9:  create table UserInfo

  10:  (

  11:  ID varchar(10) primary key

  12:  ,Name varchar(10)

  13:  ,Addr varchar(10)

  14:  ,Tel varchar(10)

  15:  ,ModiDate datetime

  16:  )

  17:  go

  18:   

  19:  --建立測試資料

  20:  insert into UserInfo values ('001','User1','Addr1','12345678',GETDATE())

  21:  select * from UserInfo

  22:  go

• 以下程式碼範例示範建立DML Trigger，並利用Update方法來判斷是否更新Name資料行，當更新Name資料行時把ModiDate資料行以系統日期加上一年，來強調ModiDate確實已被修改：

   1:  --建立DML TRIGGER

   2:  create trigger tr_UpdateUserInfo on dbo.UserInfo

   3:  after UPDATE

   4:  as

   5:   --傳回一個布林值，用來指出是否在資料表或檢視的指定資料行上嘗試了

   6:  --  INSERT 或 UPDATE。Transact-SQL INSERT 或 UPDATE 觸發程序主體內的任何位置，

   7:  --都可以利用 UPDATE() 來測試觸發程序是否應該執行特定動作。

   8:    if update(Name)

   9:      update UserInfo

  10:      set ModiDate = DATEADD(year,1,GETDATE())

  11:      where ID = (select ID from inserted)

  12:  go    

• 以下程式碼示範測試tr_UpdateUserInfo DML Trigger：

   1:  --更新address欄位ModiDate不會被更新

   2:  update UserInfo

   3:  set Addr = '123'

   4:  where ID = '001'

   5:   

   6:  select * from UserInfo

   7:   

   8:  --更新Name欄位ModiDate被加了一年

   9:  update UserInfo

  10:  set Name  = 'User2'

  11:  where ID = '001'

  12:   

  13:  select * from UserInfo

測試結果如下圖所示：

 

【參考資料】

http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/43375164-12c9-4b35-853a-2339e86267ad

http://msdn.microsoft.com/zh-tw/library/ms187326.aspx