###### tags: `SQL`
# Trigger 觸發器

### 定義

觸發器（Trigger）是針對某個表或檢視([View](https://ithelp.ithome.com.tw/questions/10122148))所編寫的特殊儲存過程，在資料庫伺服器發生事件時(建立 DML、DDL 或登入等)，會自動執行的特殊預存程序。不能被顯式地呼叫。

## SQL Server Trigger語法
[DML Trigger語法
](https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15)

當使用者試圖透過資料操作語言 (DML) 事件來修改資料時，便會執行 DML 觸發程序。DML 事件包括資料表的檢視或 INSERT、UPDATE 或 DELETE 陳述式。

### DML Trigger
必須透過 table 或 view 來建立，並且定義要引發 Trigger 的事件：INSERT, UPDATE, DELETE。

引發 Triggers 的陳述式和 Triggers 本身會被視為單一交易處理，而這樣的交易可以從觸發程序內部Rollback。
###### 例如偵測到伺服器錯誤時(例如，磁碟空間不足)，整個交易就會自動Rollback。 所以DML 觸發程序可執行類似條件約束的功能，讓資料保持完整性。

CREATE TRIGGER [TRIGGER 的名稱]
   ON  [指定資料表 | view]
   [ WITH <dml_trigger_option> [ ,...n ] ]
   { FOR | AFTER | INSTEAD OF} UPDATE, INSERT, DELETE -- 觸發事件
[ NOT FOR REPLICATION ]
AS 
BEGIN
    -- 下面 if 中包含兩個條件
    -- 1. UPDATE(欄位名稱)，不論 INSERT 或 UPDATE 嘗試成功與否，UPDATE() 都會傳回 TRUE
    -- 2. EXISTS 指定測試資料列是否存在的子查詢
    IF UPDATE([指定欄位]) AND EXISTS([子查詢])
    BEGIN
        PRINT '使用 PRINT 印出想知道的資訊'
    END
    ELSE
    BEGIN
        PRINT 'Nothing to do.'
    END
END

##### 參數說明：
[table | view] 這是執行DML 觸發程序的資料表或檢視。

[FOR | AFTER | INSTEAD OF]
FOR ：同等 AFTER 。
AFTER ：只在觸發的 SQL 陳述式指定的所有作業都執行成功時，才引發DML 觸發程序。 ##檢視不能定義 AFTER 觸發程序。
INSTEAD OF ：指定以DML 觸發程序來取代觸發的 SQL 陳述式，因此，會覆寫觸發陳述式的動作。

[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]
設定要激發DML 觸發程序的事件。

[NOT FOR REPLICATION]
複寫代理程式修改觸發程序所涉及的資料表時，不應執行觸發程序。

#### INSTEAD OF Trigger 運用

INSTEAD OF 觸發程序會以觸發程序中的 SQL query來取代原觸發的 SQL query。

1. 如果要設計一個資料表，其中某個欄位要符合多個 FK ，或者欄位值要用 SQL 再加以判斷是否合法，這時候就可以使用 INSTEAD OF 觸發程序。
2. 若使用不可更新檢視表，因為無法直接更新資料，這時候也可以在檢視表表建立 INSTEAD OF 觸發程序，來達到必要的功能。
3. 針對某個 TABLE ，僅允許特定欄位可以被更新。

#### 限制
1. 可更新檢視表不允許 INSTEAD OF 觸發程序
2. DDL Trigger 或 Logon Trigger 不能指定 INSTEAD OF。

### DDL Triggers

* 它會在 DDL 陳述式執行時引發，大多用來執行資料庫中的管理工作，例如稽核或管理資料庫作業等。
* DDL 觸發程序可回應各種 DDL 陳述式所引發的事件，這些事件包括 CREATE 、 ALTER 、 DROP 、 GRANT 、 DENY 、 REVOKE 或 UPDATE STATISTICS 關鍵字開頭的陳述式。

CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } 
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

##### 參數說明
[ALL SERVER | DATABASE]
ALL SERVER：將 DDL 觸發程序的範圍套用在目前伺服器上，也就是套用到所有資料庫上。
DATABASE：將 DDL 觸發程序的範圍套用在目前資料庫上。

[FOR | AFTER]
DDL 觸發程序或Logon 觸發程序不能指定 INSTEAD OF。

[event_type | event_group]
event_type ->[觸發事件](https://docs.microsoft.com/zh-tw/sql/relational-databases/triggers/ddl-events?redirectedfrom=MSDN&view=sql-server-ver15)
如：CREATE_TABLE、DROP_TABLE、ALTER_TABLE、CREATE_INDEX、CREATE_VIEW、DROP_USER 等
event_group ->[DDL 事件群組](https://docs.microsoft.com/zh-tw/sql/relational-databases/triggers/ddl-event-groups?view=sql-server-ver15)
Event types roll up within a command hierarchy called event groups.

範例:

CREATE TRIGGER trig_MoniterTables ON DATABASE　　--套用在目前資料庫
	FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
    DECLARE @user nvarchar(50);
    SELECT @user = SYSTEM_USER;
    INSERT DbEventTrack(UserName,EventMsg) Values(@user,cast(EVENTDATA() as nvarchar(max)))

--DROP
DROP TRIGGER trig_MoniterTables ON DATABASE

#### EVENTDATA function

如果使用 DDL 觸發程序，而想知道那些內容被什麼人異動，這些相關資訊就可以透過 [EVENTDATA](https://docs.microsoft.com/zh-tw/sql/t-sql/functions/eventdata-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15) 這個函式取得。
EVENTDATA()在執行Trigger時返回下面的 XML 和連接資料。

<EVENT_INSTANCE>
  <EventType>LOGON</EventType>
  <PostTime>2022-05-13T12:33:59.773</PostTime>
  <SPID>53</SPID>
  <ServerName>JERRY\JIGNESH</ServerName>
  <LoginName>sa</LoginName>
  <LoginType>Windows (NT)Login</LoginType>
  <SID>AQ==</SID>
  <ClientHost>140.123.1.1</ClientHost>
  <IsPooled>0</IsPooled>
</EVENT_INSTANCE>

* ClientHost：來源用戶端之主機名稱。如果用戶端和伺服器名稱相同，這個值會是'<local_machine>'。 否則會是用戶端的 IP 位址。

* 要獲取客戶端主機地址，在Trigger中使用以下 XML 查詢。當直接在 DDL 或登錄觸發器內部引用時，EVENTDATA 才會返回值。如果 EVENTDATA 被其他例程調用，則返回 null

EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(128)')

### Logon Triggers

[登入觸發程序
](https://docs.microsoft.com/zh-tw/sql/relational-databases/triggers/logon-triggers?redirectedfrom=MSDN&view=sql-server-ver15)
* 當 SQL Server執行個體建立使用者工作階段時，就會引發這個事件。
* 登入觸發程序會在登入驗證階段結束之後，但在使用者工作階段實際建立之前引發。從觸發程序內產生且一般會顯示給使用者的所有訊息。

#### Logon Triggers應用
可以使用登入觸發程序稽核和控制伺服器工作階段。
例如:追蹤登入活動、限制登入 SQL Server，或限制特定登入的工作階段數。

#### Logon Triggers語法:

CREATE TRIGGER trigger_name ON ALL SERVER 
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

範例:

CREATE TRIGGER Connection_Limit_Trigger
ON ALL SERVER 
WITH EXECUTE AS 'OWNER' -- 限定OWNER使用者
FOR LOGON
AS
    BEGIN
      -- 禁止 SSMS 連入
      IF APP_NAME() LIKE '%Microsoft SQL Server Management Studio%'
          ROLLBACK
        
      -- 禁止特定帳號或特定 IP 連入
      DECLARE @data xml
      DECLARE @ClientHost varchar(50)
      DECLARE @LoginName varchar(50)
      DECLARE @PostTime datetime

      SET @data = EVENTDATA()
      SET @ClientHost = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
      SET @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
      SET @PostTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

      -- 特定 IP
      IF @ClientHost = '192.168.0.20'
          ROLLBACK

      -- 特定帳號
      IF @LoginName = 'ServerName\LogonTest'
          ROLLBACK

      -- 特定時間
      IF DATEPART(hh,@PostTime) >= 20
          ROLLBACK
          
      --或是直接用函數
      IF (EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(128)') IN 
        ('192.168.1.1', '140.123.1.1', '<local machine>'))
    BEGIN
      RETURN; -- 允許登入
    END
    ELSE            
    BEGIN
            ROLLBACK; -- Disconnect the session
    END
    END

### 判斷 Trigger 抓的資料
deleted Table 和 inserted Table 為 Trigger 使用的兩個特殊暫存資料表（交易紀錄的檢視表），其欄位名稱、順序和資料型態都跟原來的 Table 一致。


* 當Insert時，除了將資料新增到資料表中外，在 INSERTED 中，也會保留資料。Delete 時，也會將資料在 Deleted 中保留。

* 當Update時，是先將資料表中的資料先刪除再新增，所以在 INSERTED 中，會記錄薪資料的記錄，而在 DELETED 會保留舊的記錄。

* SQL Server 會在 TempDB 中自動建立並管理，無法直接修改資料表內的資料或是修改其結構（EX：CREATE INDEX）

* 當Trigger 結束時，暫存的 deleted Table 和 inserted Table 會自動消失。

### 啟用與停用 Trigger

停用 Trigger ( 觸發程序 ) 語法：

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

範例:

DISABLE TRIGGER dbo.我是觸發器 ON dbo.資料表

啟用 Trigger ( 觸發程序 ) 語法：

ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

範例:

ENABLE TRIGGER dbo.我是觸發器 ON dbo.資料表

刪除觸發器 範例:

    DROP TRIGGER dbo.我是觸發器;

## 參考來源

如何判斷 Trigger 現在是 Insert Update Delete
https://dotblogs.com.tw/dc690216/2009/09/10/10553

VITO の 學習筆記 Trigger
http://vito-note.blogspot.com/2013/05/trigger.html

大家有沒有使用 資料庫的VIEW?
https://ithelp.ithome.com.tw/questions/10122148

Logon Trigger有問題導致無法登入
https://ithelp.ithome.com.tw/articles/

Web應用程式開發-SQL Trigger
https://ithelp.ithome.com.tw/articles/10078041

[SQL] Logon Trigger
http://jengting.blogspot.com/2012/12/logon-trigger.html

SQL Server EXECUTE AS
https://www.mssqltips.com/sqlservertip/1227/sql-server-execute-as/

Limit SQL Server Login Authentication scope using a Logon Trigger
https://www.sqlshack.com/prevent-sql-server-login-authentication-scope-using-logon-trigger/