[T-SQL] Trigger 觸發程序

- 6月 06, 2018

Trigger 觸發程序

功能：攔截資料表中發生的INSERT、DELETE、UPDATE事件。
(資料庫的檯面下交易，因為看不到它的運作。)

語法：

Create Trigger tr_trigger_name --觸發程序的名稱通常前面會加個tr之類的字做識別
On userinfo --攔截的目標資料表
For INSERT, UPDATE, DELETE --看要攔截的事件是什麼，可以只寫一個。
AS
  //Trigger觸發後要做的事情寫這邊

老師的提醒：
**Trigger的建立一定要寫文件做紀錄。

**Trigger也可設定成攔截A資料表中的事件，然後自動完成某件事情至B資料表。

**當For後面攔截三個事件，相對的AS後面執行的程式碼也會較複雜。初學可將三個事件拆成三個Tr寫。

範例：若Userinfo中新增一筆資料，將此事件自動記錄到log資料表中。

Create trigger tr_userinfo_log
On userinfo
For insert
AS
  Declare @uid nvarchar(50)  --設定
  Declare @cname nvarchar(50)

  --停止計算SQL影響的資料列數 (系統預設會自動計算並顯示在SSMS訊息視窗)
  SET NOCOUNT ON

  Select @uid = uid, @cname = cname from INSERTED  --從INSERTED資料表中提取新增或修改的那筆資料
  /*INSERTED資料表是系統自動產生的表，裡面存放新增或修改的那一整筆資料。*/
  Insert into log(body) values('資料表USERINFO中新增'+'@uid'+','+'@cname'+資料) --把那筆資料寫入資料表log裡

補充說明： Declare是T-SQL中的變數宣告。變數前面一律加@符號。(兩個@符號是全域變數，通常是系統變數。如：@@ERROR) 另外，Declare後面的資料型態要與對應的資料表內的資料型態相同，字串大小最少要和資料表內的資料型態同，不可小於該數字。

SET NOCOUNT ON：SQL預設在執行INSERT/UPDATE/DELETE之類的指令後，系統會自動計算資料異動的筆數。若為多人使用的資料庫，因其系統較繁忙，為了提高效率可將設定NOCOUNT ON，節省運算時間。通常使用TRIGGER時都會關，但也可不關。

INSERTED：是保留字也相當於一個內建的資料表，新插入的資料會同時間存放到這個資料表。

DELETED：是保留字也相當於一個內建的資料表，但代表的是刪除的資料會同時間存放到這個資料表。

資料表log：語法內的log是在設定這個trigger前，預先建置起來的資料表，內容為對應USERINFO，所以有先設定UID、CNAME等USERINFO的欄位。另外，還有在log資料表中新增一個DD欄位設定getdate()，使其自動抓取資料輸入的時間。

區別INSERT、DELETE、UPDATE (使用if else)

範例：

CREATE TRIGGER tr_log_userinfo
ON UserInfo
FOR INSERT, DELETE, UPDATE
AS
    IF exists(SELECT * FROM deleted) and exists(SELECT * FROM inserted)
     Begin
        print('修改資料')
     End
    ELSE IF exists(SELECT * FROM deleted) and not exists(SELECT * FROM inserted)
        print('刪除資料')

    ELSE IF not exists(SELECT * FROM deleted) and exists(SELECT * FROM inserted)
        print('新增資料')

    ELSE
        print('無資料變動')

進階範例：以記錄到log的trigger為原型，若要區別INSERT、DELETE、UPDATE，可改成以下。

--修改原本的trigger,增加update&delete，更新回寫到Log檔的指令。
create trigger tr_log_userinfo
ON userinfo
for insert, delete, update
as 
  declare @uid varchar(10)
  declare @cname nvarchar(50)
                             
  Set nocount on --停止計算SQL影響的資料列數
  if exists(select * from deleted) and exists(select * from inserted)
     begin
          select @uid = uid, @cname = cname from deleted
          insert into log(body) values('在資料表Userinfo中刪除' + @uid +',' + @cname + '資料')
          select @uid = uid, @cname = cname from inserted
          insert into log(body) values('在資料表Userinfo中更新' + @uid +',' + @cname + '資料')
          print('修改資料')
     end
  else if exists(select * from deleted) and not exists(select * from inserted)
     begin
          select @uid = uid, @cname = cname from deleted
          insert into log(body) values('在資料表Userinfo中刪除' + @uid +',' + @cname + '資料')
          print('刪除資料')
     end
  else if not exists(select * from deleted) and exists(select * from inserted)
     begin
          select @uid = uid, @cname = cname from inserted
          insert into log(body) values('在資料表Userinfo中新增' + @uid +',' + @cname + '資料')
          print('新增資料')
     end

補充解釋：
UPDATE SQL Command 攔截= deleted & inserted 同時有資料 [舊的被刪除資料會記錄在deleted, 新的新增資料會在inserted資料表。]

DELETE = deleted 有資料，inserted 沒資料

INSERT = deleted 無資料；inserted 有資料
都沒有 = 無資料異動

T-SQL語法 (IF......else if)
**若if與else間超過一行，要用Begin&End夾住，否則語法錯誤。
**Print只是在SQL執行的訊息欄中可以看到，純粹除錯用。

UPDATE() Function

功能：
檢查使用者到底更新了哪個欄位。在trigger中，可利用條件判斷使trigger針對某個欄位觸發。

微軟文件上面的說明：傳回一個布林值，用來指出是否在資料表或檢視的指定資料行上嘗試了 INSERT 或 UPDATE。

範例：若新密碼與帳號一樣，則讓密碼更新失敗。

CREATE TRIGGER tr_userinfo_pwd ON UserInfo FOR UPDATE
AS
    DECLARE @uid NVARCHAR(50)
    DECLARE @pwd NVARCHAR(50)
    IF update(pwd)
    BEGIN
       SELECT @uid = uid, @pwd = pwd FROM inserted
       IF @pwd = @uid
       BEGIN
           print ('密碼不可與帳號一樣')
           ROLLBACK
       END
    END

建立Trigger要避免的情況：遞迴(Recursive)

若發生遞迴的情況，在資料量龐大的狀態下，容易不清楚到底修改或更動了哪些資料，在資料庫維護上會非常困難。因此，在建立trigger時一定要避免遞迴情況的發生！

直接遞迴
修改 Table_1 資料 -> trigger -> 修改 Table_1 資料

間接遞迴
修改 TableA 資料 -> trigger1 -> 修改 TableB 資料
修改 TableB 資料 -> trigger2 -> 修改 TableA 資料

老師的提醒：
**SQL Server只能擋掉直接遞迴，但無法阻擋間接遞迴。
設定方式如下：資料庫_右鍵_屬性_其他_遞迴觸發程序已啟用_True/false (True: 達到32層自動停止。/False: 會一直執行。)

**建立trigger時要有原始文件或規劃的文件，避免有遞迴情況發生。

**SQL自動設定在遞迴中只要執行到 rollback 指令，遞迴到此結束。

**SQL遞迴層級最大可以到32層！

搜尋此網誌

文組生的轉職日記