如何以Output子句製作模擬DML Trigger

如何以Output子句製作模擬DML Trigger

實務上我們會在更新某個資料表的時候,記錄修改前後的資料,要達到這個需求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

image

接著撰寫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

測試結果:

image

 

接著筆者以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相同,如下圖所示:

image

 

【參考資料】