第 16 章 觸發程序
本章重點 16-1 觸發程序的用途 16-2 觸發程序的種類與觸發時機 16-3 觸發程序的建立、修改、與停用 16-4 設計觸發程序的技巧 16-5 建立 AFTER 觸發程序 16-6 建立 INSTEAD OF 觸發程序
16-1 觸發程序的用途 條件約束 (Constraint) 直接設定於資料表內, 通常不需另外撰寫程式。 觸發程序 (Trigger) 對單一資料表所撰寫的特殊預存程序。
觸發程序的用途 檢查所做的更改是否允許 進行其他相關資料的更改動作 發出更改或預警的通知 自訂錯誤訊息 更改原來所要進行的資料操作 檢視表也可以有觸發程序
16-2 觸發程序的種類與觸發時機 AFTER 觸發程序 要在資料已變動完成之後 (AFTER),才會被啟動並進行必要的善後處理或檢查。 INSTEAD OF 觸發程序 是這類觸發程序會取代原本要進行的操作。
16-3 觸發程序的建立、修改、與停用 用 SQL 建立與修改觸發程序 檢視觸發程序的相關資訊 使用 SQL Server Management Studio 管理觸發程序 更改觸發程序的名稱 停用觸發程序
用 SQL 建立與修改觸發程序
用 SQL 建立與修改觸發程序 CREATE TRIGGER trigger_name ON {table | view} WITH ENCRYPTION { FOR | AFTER | INSTEAD OF} { [INSERT] [,] [UPDATE] [,] [DELETE] }
用 SQL 建立與修改觸發程序 AS sql_statements
用 SQL 建立與修改觸發程序
檢視觸發程序的相關資訊 sp_helptrigger 'table_name' [, 'type']
檢視觸發程序的相關資訊 sp_help 'trigger_name'
檢視觸發程序的相關資訊 sp_helptext 'trigger_name'
使用 SQL Server Management Studio 管理觸發程序
使用 SQL Server Management Studio 管理觸發程序
更改觸發程序的名稱
重新命名『檢視表、預存程序、自訂函數、觸發程序』的注意事項
停用觸發程序
停用觸發程序
停用觸發程序
停用觸發程序 { DISABLE | ENABLE } TRIGGER { trigger_name [ ,...n ] | ALL } ON object_name
16-4 設計觸發程序的技巧 設計觸發程序時的限制 在觸發程序中取得欄位修改之前及之後的資料 如何偵測異動筆數及復原異動 判斷欄位是否更改 觸發程序回應錯誤訊息的方式
設計觸發程序時的限制
在觸發程序中取得欄位修改之前及之後的資料
在觸發程序中取得欄位修改之前及之後的資料
在觸發程序中取得欄位修改之前及之後的資料 AFTER 觸發程序 被執行時,資料表已經完成更改的動作了,但尚未確認；也就是說,資料表內容雖然已經變更,但我們仍可用 ROLLBACK TRANSAC-TION(簡寫為 ROLLBACK) 敘述來回復到資料修改前的狀況,此時包括了在觸發程序中所做的任何修改動作都將回復。 INSTEAD OF 觸發程序 完全取代了原來的資料異動操作,因此不會先檢查條件約束；但仍會先將使用者要變更的資料放入 inserted、deleted 資料表中,以供程序中的程式使用。
如何偵測異動筆數及復原異動
如何偵測異動筆數及復原異動
如何偵測異動筆數及復原異動 當執行 INSERT、UPDATE、或 DELETE 時, 無論是否真正更動到資料, 觸發程序都會被執行。 在前面第一次刪除4 筆客戶資料時, 結果卻多出一個 “又有訂單被刪除了！” 訊息, 這是因為筆者已設定了客戶 T1與訂單 T1資料表的關聯：
如何偵測異動筆數及復原異動
如何偵測異動筆數及復原異動 在觸發程序中執行 ROLLBACK 時, 會結束目前批次的執行。
判斷欄位是否更改
判斷欄位是否更改
判斷欄位是否更改
觸發程序回應錯誤訊息的方式
觸發程序回應錯誤訊息的方式
觸發程序回應錯誤訊息的方式
觸發程序回應錯誤訊息的方式
16-5 建立 AFTER 觸發程序 AFTER INSERT 觸發程序 AFTER UPDATE 觸發程序 AFTER DELETE 觸發程序 AFTER 觸發程序綜合演練 設定 AFTER 觸發程序的啟動順序 巢狀觸發與遞迴觸發
AFTER INSERT 觸發程序
AFTER INSERT 觸發程序
AFTER UPDATE 觸發程序
AFTER UPDATE 觸發程序
AFTER UPDATE 觸發程序
AFTER UPDATE 觸發程序
AFTER DELETE 觸發程序
AFTER DELETE 觸發程序
AFTER 觸發程序綜合演練 底下的觸發程序可發 Mail 通知管理者資料被更改了：
AFTER 觸發程序綜合演練 由於資料表的 CHECK 限制只能參考到同一個資料表中的欄位值, 因此若需要由其他資料表中取出資料做檢查, 則必須用觸發程序來完成。
AFTER 觸發程序綜合演練
AFTER 觸發程序綜合演練 有些資料在更改之後, 我們會希望能將更改的日期及更改前的內容, 存入另一個記錄用的資料表中, 以供未來查閱之用。
設定 AFTER 觸發程序的啟動順序
設定 AFTER 觸發程序的啟動順序 每項操作最多只能設定一個 First 及一個 Last 觸發程序, 其他未指定 (None) 的程序則依任意順序觸發。 將觸發程序設為 None 即可取消其 First 或 Last 屬性。 設定的操作必須與觸發程序內容相符, 例如AFTER INSERT,UPDATE 的觸發程序, 不能設為DELETE 的 First 或 Last 程序。
設定 AFTER 觸發程序的啟動順序 在使用 ALTER TRIGGER 更改觸發程序的內容後, 會自動還原為None 屬性。 此功能只適用於 AFTER 觸發程序, 不能用於INSTEAD OF 觸發程序。
巢狀觸發與遞迴觸發 自己呼叫自己 (A→A→A...), 稱為『直接遞迴』, SQL Server 預設會防止直接遞迴。 經由別人再觸發 (A→B→A→B→A...), 稱為『間接遞迴』, 此狀況預設會一直執行下去, 若要禁止, 可如上將 'nested triggers' 選項設為0 即可。
16-6 建立 INSTEAD OF 觸發程序 什麼時候要使用 INSTEAD OF 觸發程序 INSTEAD OF 觸發程序的執行流程
什麼時候要使用 INSTEAD OF 觸發程序 當某敘述的資料操作被觸發程序禁止時, 若在觸發程序中使用 ROLLBACK, 則會中斷整個批次的執行；此時可改用 INSTEAD OF 觸發程序來避免批次被中斷, 因為不需要使用 ROLLBACK 來回復異動。 當一次異動多筆記錄時, 若在觸發程序中使用ROLLBACK, 則全部異動都會被回復。如果希望只取消有問題的異動, 則可以改用 INSTEAD OF 觸發程序。
什麼時候要使用 INSTEAD OF 觸發程序 若想要在檢視表中設定觸發程序, 則只能使用INSTEAD OF 觸發程序。AFTER 觸發程序不可使用在檢視表中。
INSTEAD OF 觸發程序的執行流程
INSTEAD OF 觸發程序實例 底下的範例在一次新增多筆資料時, 會先判斷新增資料的員工編號是否已經存在於資料表中, 若是則改用 UPDATE 來更新記錄：
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例 接續前例, 我們在員工 T2資料表中再加一個 AFTER 觸發程序, 看看在新增資料時是否會被觸發：
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例 接續前例, 我們再來設計 UPDATE 的觸發程序：
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例 當檢視表中使用到多個資料表時, 那麼如果要透過它來新增、修改、或刪除資料, 在處理上難免會有點複雜, 這時 INSTEAD OF 觸發程序又可派上用場了。
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例
INSTEAD OF 觸發程序實例