積沙成塔

實務上我們會在更新某個資料表的時候，記錄修改前後的資料，要達到這個需求DML Trigger會是不錯的選擇，實作步驟如下：

【DML Trigger】

首先先建立tab1和tab2兩個資料表，其中tab1當作是主要的交易資料表，tab2則是用來記錄修改前後的異動資料，程式碼如下：

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

   2:      drop table tab1

   3:  go

   4:   

   5:  create table tab1

   6:  (

   7:      id    varchar(10)

   8:      ,name nvarchar(10)

   9:      ,tel1    varchar(10)

  10:      ,tel2 varchar(10)

  11:  )

  12:  go

  13:   

  14:  insert into tab1 values ('01','AAA','4545454','7877855'),('02','BBB','6556656','9831825')

  15:  go

  16:   

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

  18:      drop table tab2

  19:  go

  20:  --表2

  21:  create table tab2

  22:  (

  23:      id    varchar(10)

  24:      ,name nvarchar(10)

  25:      ,tel1    varchar(10) -- 更改前TEL1

  26:      ,tel11    varchar(10) --更改後TEL1  

  27:      ,tel2 varchar(10) -- 更改前TEL2

  28:      ,tel21 varchar(10) --更改後TEL2

  29:  )

  30:  go

  31:   

  32:  if exists (select * from sys.objects where type = 'TR' and name = 'tr_update_tab1')

  33:      drop trigger tr_update_tab1

  34:  go

接著撰寫DML Trigger來捕捉tab1被update時，要將變更前後的TEL記錄到tab2，程式碼如下：

   1:  if exists (select * from sys.objects where type = 'TR' and name = 'tr_update_tab1')

   2:      drop trigger tr_update_tab1

   3:  go

   4:   

   5:  create trigger tr_update_tab1

   6:  on tab1

   7:  after update

   8:  as

   9:      insert into tab2

  10:      select d.id,d.name,d.tel1,case when i.tel1 = d.tel1 then '未修改' else i.tel1 end

  11:              ,d.tel2,case when i.tel2 = d.tel2 then '未修改' else i.tel2 end

  12:      from deleted d

  13:      inner join inserted i

  14:      on d.id = i.id

  15:   

  16:  go

以下列程式碼測試DML Trigger是否發揮功用，分別針對只修改TEL1、TEL2及同時修改TEL1和TEL2三種狀況來進行測試：

   1:  select *

   2:  from tab1

   3:  select *

   4:  from tab2

   5:   

   6:  update tab1 

   7:  set tel1 = '11111'

   8:  where id = '01'

   9:   

  10:  update tab1 

  11:  set tel1 = 'aaa'

  12:  ,tel2 = 'bbb'

  13:  where id = '02'

  14:   

  15:  update tab1 

  16:  set tel2 = 'xxx'

  17:  where id = '01'

  18:   

  19:  select *

  20:  from tab1

  21:  select *

  22:  from tab2

測試結果：

 

接著筆者以Output子句來模擬DML Trigger。

【 Output子句】

以下列Output子句來模擬DML Trigger的效果，程式碼如下：

   1:  select *

   2:  from tab1

   3:  select * 

   4:  from tab2

   5:   

   6:  update tab1 

   7:  set tel1 = '11111'

   8:  output deleted.id,deleted.name

   9:             ,deleted.tel1

  10:             ,deleted.tel2

  11:             ,case when inserted.tel1 = deleted.tel1 then '未修改' else inserted.tel1 end

  12:            ,case when inserted.tel2 = deleted.tel2 then '未修改' else inserted.tel2 end 

  13:  into tab2

  14:  where id = '01'

  15:   

  16:  update tab1 

  17:  set tel1 = 'aaa'

  18:  ,tel2 = 'bbb'

  19:  output deleted.id,deleted.name

  20:             ,deleted.tel1

  21:             ,deleted.tel2

  22:             ,case when inserted.tel1 = deleted.tel1 then '未修改' else inserted.tel1 end

  23:            ,case when inserted.tel2 = deleted.tel2 then '未修改' else inserted.tel2 end 

  24:  into tab2

  25:  where id = '02'

  26:   

  27:  update tab1 

  28:  set tel2 = 'xxx'

  29:  output deleted.id,deleted.name

  30:             ,deleted.tel1

  31:             ,deleted.tel2

  32:             ,case when inserted.tel1 = deleted.tel1 then '未修改' else inserted.tel1 end

  33:            ,case when inserted.tel2 = deleted.tel2 then '未修改' else inserted.tel2 end 

  34:  into tab2

  35:  where id = '01'

  36:   

  37:  select *

  38:  from tab1

  39:  select * 

  40:  from tab2

執行結果與使用DML Trigger相同，如下圖所示：

【參考資料】

• OUTPUT 子句 (Transact-SQL)
• CREATE TRIGGER (Transact-SQL)