16 觸發程序 16-1 觸發程序的基礎 16-2 DML觸發程序 16-3 修改、停用與刪除DML觸發程序 16-4 DDL觸發程序

16-1 觸發程序的基礎-說明 觸發程序（Triggers）是一種特殊用途的預存程序，我們並不能單獨執行觸發程序，因為它是在執行T-SQL語言的DDL指令或DML指令產生事件時，系統主動執行的程序。 觸發程序也是一組T-SQL指令敘述的集合（但沒有參數，也不能傳回值），觸發程序可以執行一些自動化操作，例如：自動更改或刪除相關聯的記錄資料、加強欄位的商業規則驗證、比較資料更改前後的資料表狀態和建立不同資料庫的參考完整性。

16-1 觸發程序的基礎-種類 SQL Server觸發程序可以分為兩種，其說明如下所示： DML觸發程序（DML Triggers）：當執行資料表操作指令INSERT、UPDATE和DELETE時，所自動執行的觸發程序，可以用來驗證商業規則，或執行更複雜的資料驗證程序。 DDL觸發程序（DDL Triggers）：一種特殊類型的觸發程序，它可以回應DDL指令（主要是指CREATE、ALTER和DROP開頭的指令）來執行資料庫的管理工作，例如：稽核與管理資料庫作業。

16-1 觸發程序的基礎-用途1 觸發程序可以驗證商業規則，或執行更複雜的資料驗證程序，例如：檢查使用者是否調整圖書價格超過百分之五、庫存是否足夠和客戶是否擁有足夠的採購額度等。 觸發程序可以用來維持多資料表間的資料完整性，我們可以透過觸發程序來更改相關聯的記錄資料。例如：在【訂單】資料表刪除一筆記錄後，使用觸發程序在【訂單明細】資料表刪除此訂單相關的所有項目資料；或是出貨一項商品，就自動將庫存量減一。

16-1 觸發程序的基礎-用途2 觸發程序能夠檢查資料更改是否是允許的操作，如果不允許就回復資料更改，我們也可以使用觸發程序直接更改或取消原來的資料操作，並且使用電子郵件發出預警的通知郵件。 觸發程序可以分析操作來執行其他的後續處理，因為觸發程序可以比較資料更改前後的資料表狀態，換句話說，我們就可以針對比較結果來執行進一步的處理。 觸發程序可以取代系統預設產生的錯誤訊息，讓我們建立自訂錯誤訊息來回應用戶端程式。

16-2 DML觸發程序 16-2-1 建立DML觸發程序 16-2-2 DML觸發程序與條件約束 16-2-3 建立AFTER觸發程序 16-2-4 建立INSTEAD OF觸發程序 16-2-5 使用UPDATE()函數

16-2 DML觸發程序-說明 DML觸發程序是當執行INSERT、UPDATE和DELETE資料表操作指令時，一種自動執行的觸發程序，可以讓我們建立強制執行的商業規則、擴充條件約束、預設值和規則等維持資料完整性的條件。

16-2 DML觸發程序-種類 SQL Server的DML觸發程序依觸發時機可以分為兩種，如下所示： AFTER觸發程序：當執行INSERT、UPDATE和DELETE指令且資料已經改變後，所觸發和執行的觸發程序，主要是用來執行一些檢查或善後處理，如果有錯誤，更改的資料可以回復至更改前的值。 INSTEAD OF觸發程序：這是在資料改變前所觸發和執行的觸發程序，可以驗證資料或取代原本需要執行的操作。

16-2-1 建立DML觸發程序- 使用Management Studio 如同預存程序，我們一樣可以使用Management Studio工具的指令或直接新增查詢來建立DML觸發程序，如下圖所示：

16-2-1 建立DML觸發程序- 使用T-SQL指令(語法) 在T-SQL語言是使用CREATE TRIGGER指令建立觸發程序，其基本語法如下所示： CREATE TRIGGER 觸發程序名稱 ON { 資料表名稱 | 檢視表名稱 } [ WITH ENCRYPTION ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [,] [ UPDATE ] [,] [DELETE] } AS T-SQL指令敘述

16-2-1 建立DML觸發程序-使用T-SQL指令(Deleted與Inserted資料表) 在DML觸發程序可以查詢系統自動產生的Deleted與Inserted邏輯資料表（Logical Tables），這兩個資料表能夠保留更改前後的記錄資料，其欄位資料完全對應ON子句資料表的欄位定義，我們一樣是使用SELECT指令來查詢Deleted與Inserted資料表，如下所示： SELECT * FROM Inserted SELECT * FROM Deleted

16-2-1 建立DML觸發程序- 使用T-SQL指令(範例1) SQL指令碼檔：Ch16_2_1_01.sql 在【教務系統】資料庫的【課程】資料表建立名為【新增記錄】的DML觸發程序，它是對應INSERT指令的事件，如下所示： CREATE TRIGGER 新增記錄 ON 課程 FOR INSERT AS BEGIN DECLARE @name varchar(30) SELECT @name = 名稱 FROM Inserted PRINT '新增課程: ' + @name END

16-2-1 建立DML觸發程序- 使用T-SQL指令(範例2) SQL指令碼檔：Ch16_2_1_02.sql 在【課程】資料表新增一筆記錄時，就會觸發執行【新增記錄】觸發程序，顯示新增記錄的課程名稱，如下所示： INSERT INTO 課程 VALUES ('CS301','作業系統概論',4)

16-2-2 DML觸發程序與條件約束-1 AFTER觸發程序與條件約束 AFTER觸發程序的執行流程是當使用者執行INSERT、UPDATE和DELETE的DML操作指令後，依序檢查條件約束、建立Deleted與Inserted資料表，並且在實際更新資料表的記錄資料後才執行AFTER觸發程序，如果操作有違反資料表的條件約束，並不會執行到AFTER觸發程序。 在AFTER觸發程序並沒有辦法事先作一些處理，來避免違反資料表的條件約束，只能增加額外的欄位檢查條件和處理來進一步維護資料完整性。例如：客戶需要擁有足夠的採購額度，才允許此客戶下訂單採購。

16-2-2 DML觸發程序與條件約束-2 INSTEAD OF觸發程序與條件約束 INSTEAD OF觸發程序是在Deleted與Inserted資料表建立後就執行，即在其他操作之前（包含條件約束）。換句話說，INSTEAD OF觸發程序是在執行條件約束前執行，我們可以在INSTEAD OF觸發程序預先處理來避免違反資料表的條件約束。 INSTEAD OF觸發程序主要是用來攔截和取代指定的操作，我們需要在INSTEAD OF觸發程序自行撰寫指令來更改或取消原來的資料操作，不過，在INSTEAD OF觸發程序執行的操作還是需要經過條件約束的檢查。

16-2-3 建立AFTER觸發程序-說明 在SQL Server資料表可以建立多個AFTER觸發程序，但只能有一個INSTEAD OF觸發程序，而且，AFTER觸發程序並不能使用在檢視表，只有INSTEAD OF觸發程序可以使用在檢視表。 AFTER和INSTEAD OF觸發程序依執行的指令不同，可以再分為：INSERT觸發程序、UPDATE觸發程序和DELETE觸發程序三種。

16-2-3 建立AFTER觸發程序- AFTER INSERT觸發程序(說明) AFTER的INSERT觸發程序是當使用者在資料表插入一筆記錄時觸發，因為是新增記錄，所以新增的記錄同時也會新增至Inserted資料表，並不會用到Deleted資料表。

16-2-3 建立AFTER觸發程序- AFTER INSERT觸發程序(範例1) SQL指令碼檔：Ch16_2_3_01.sql 針對INSERT指令建立【檢查上課數】的AFTER觸發程序，限制每一位學生最多只能上三門課程，如下所示： CREATE TRIGGER 檢查上課數 ON 班級 AFTER INSERT AS BEGIN IF ( SELECT COUNT(學號) FROM 班級 WHERE 學號 = ( SELECT 學號 FROM Inserted) ) > 3 RAISERROR('已經修太多課程!',1,1) ROLLBACK END

16-2-3 建立AFTER觸發程序- AFTER INSERT觸發程序(範例2) SQL指令碼檔：Ch16_2_3_02.sql 在【班級】資料表新增記錄就會觸發執行【檢查上課數】觸發程序，如果學生的上課數太多，就會顯示錯誤訊息且回復資料不允許新增此記錄，如下所示： INSERT INTO 班級 VALUES ('I004', 'S001', 'CS111','03:00:00', '321-M')

16-2-3 建立AFTER觸發程序- AFTER UPDATE觸發程序(說明) AFTER的UPDATE觸發程序當使用者在資料表更新記錄時觸發，因為是更新記錄，更新資料是新增至Inserted資料表，原始資料是新增至Deleted資料表。

16-2-3 建立AFTER觸發程序- AFTER UPDATE觸發程序(範例1) SQL指令碼檔：Ch16_2_3_03.sql 在【課程】資料表建立名為【檢查學分數】的觸發程序，限制更新的學分數只能增加，不能減少，如下所示： CREATE TRIGGER 檢查學分數 ON 課程 AFTER UPDATE AS BEGIN DECLARE @new int, @old int SELECT @new = 學分 FROM Inserted SELECT @old = 學分 FROM Deleted IF @old > @new PRINT '不允許更新學分欄位!' ROLLBACK TRAN END

16-2-3 建立AFTER觸發程序- AFTER UPDATE觸發程序(範例2) SQL指令碼檔：Ch16_2_3_04.sql 在【課程】資料表更新記錄時就會觸發執行【檢查學分數】觸發程序，檢查更改的學分數是否是增加，如下所示： UPDATE 課程 SET 學分 = 3 WHERE 課程編號 = 'CS301'

16-2-3 建立AFTER觸發程序- AFTER DELETE觸發程序(說明) AFTER的DELETE觸發程序是當使用者在資料表刪除一筆記錄時觸發，因為是刪除記錄，所以刪除的記錄同時也會新增至Deleted資料表，並不會用到Inserted資料表。

16-2-3 建立AFTER觸發程序- AFTER DELETE觸發程序(範例1) SQL指令碼檔：Ch16_2_3_05.sql 在【教務系統】資料庫的【員工】資料表，同時針對DELETE和UPDATE指令建立名為【員工管理】的AFTER觸發程序，可以檢查刪除或更改的姓名是否存在【學生】資料表，如果存在，就拒絕刪除與更新，如下所示： CREATE TRIGGER 員工管理 ON 員工 AFTER DELETE, UPDATE AS IF EXISTS (SELECT * FROM 學生 WHERE 姓名 = (SELECT 姓名 FROM Deleted)) BEGIN RAISERROR('不合法姓名!',1,1) ROLLBACK TRAN END

16-2-3 建立AFTER觸發程序- AFTER DELETE觸發程序(範例2) SQL指令碼檔：Ch16_2_3_06.sql 在【員工】資料表刪除記錄時就會觸發【員工管理】觸發程序，如果員工姓名存在【學生】資料表，就顯示錯誤訊息，並且回復資料不允許刪除這筆記錄，如下所示： DELETE 員工 WHERE 身份證字號 = 'F332213046'

16-2-3 建立AFTER觸發程序- AFTER DELETE觸發程序(範例3) SQL指令碼檔：Ch16_2_3_07.sql 在【員工】資料表更新記錄就會觸發執行【員工管理】的觸發程序，如果更新的員工姓名存在【學生】資料表，就顯示錯誤訊息，和回復資料不允許更新記錄，如下所示： UPDATE 員工 SET 薪水 = 55000 WHERE 身份證字號 = 'F332213046'

16-2-4 建立INSTEAD OF觸發程序-說明 INSTEAD OF觸發程序可以攔截和取代指定操作，因為觸發的操作指令並不會執行，所以在INSTEAD OF觸發程序就需要執行這些操作。 不同於AFTER觸發程序，INSTEAD OF觸發程序可以使用在檢視表，讓檢視表成為可編輯的檢視表。不過，INSTEAD OF觸發程序並不支援遞迴呼叫，而且在同一個資料表或檢視表只能擁有一個INSTEAD OF觸發程序。

16-2-4 建立INSTEAD OF觸發程序- 建立(說明) INSTEAD OF觸發程序一樣分為INSERT、UPDATE和DELETE三種，因其架構和AFTER觸發程序類似，所以筆者只準備使用INSERT事件來說明如何在資料表建立INSTEAD OF觸發程序。

16-2-4 建立INSTEAD OF觸發程序- 建立(範例1a) SQL指令碼檔：Ch16_2_4_01.sql 在【教務系統】資料庫的【課程】資料表，針對INSERT指令建立名為【新增課程記錄】的INSTEAD OF觸發程序，如果新增記錄的課程編號存在，就改為更新課程記錄，如下所示： CREATE TRIGGER 新增課程記錄 ON 課程 INSTEAD OF INSERT AS BEGIN IF EXISTS (SELECT * FROM 課程 WHERE 課程編號 = ( SELECT 課程編號 FROM Inserted)) UPDATE 課程 SET 課程.名稱 = Inserted.名稱,

16-2-4 建立INSTEAD OF觸發程序- 建立(範例1b) 課程.學分 = Inserted.學分 FROM 課程 JOIN Inserted ON 課程.課程編號 = Inserted.課程編號 PRINT '更新一筆記錄!' END ELSE BEGIN INSERT 課程 SELECT * FROM Inserted PRINT '新增一筆記錄!'

16-2-4 建立INSTEAD OF觸發程序- 建立(範例2) SQL指令碼檔：Ch16_2_4_02.sql 在【課程】資料表新增記錄就會觸發INSTEAD OF觸發程序【新增課程記錄】，以此例是存在的課程編號，如下所示： INSERT INTO 課程 VALUES ('CS213', '物件導向程式設計', 4)

16-2-4 建立INSTEAD OF觸發程序- 建立(範例3) SQL指令碼檔：Ch16_2_4_03.sql 在【課程】資料表新增記錄就會觸發INSTEAD OF觸發程序【新增課程記錄】，以此例是新的課程編號，如下所示： INSERT INTO 課程 VALUES ('CS333', '物件導向程式設計(2)', 3)

16-2-4 建立INSTEAD OF觸發程序- 在檢視表建立(說明) INSTEAD OF觸發程序也可以使用在檢視表（AFTER觸發程序不能使用在檢視表），可以讓原來不可編輯的檢視表成為可編輯的檢視表。如果是合併查詢的檢視表，就可以同時插入、更新或刪除多個資料表的記錄資料。 例如：建立同時是學生和員工的【學生員工_檢視】檢視表後，就可以新增此檢視表的INSTEAD OF觸發程序，當在檢視表新增記錄，即可同時在【學生】和【員工】資料表各新增一筆記錄。

16-2-4 建立INSTEAD OF觸發程序- 在檢視表建立(範例1) SQL指令碼檔：Ch16_2_4_04.sql 建立【學生】與【員工】資料表的合併查詢檢視表【學生員工_檢視】，可以同時顯示是學生和員工的記錄資料，如下所示： CREATE VIEW 學生員工_檢視 AS SELECT 學生.學號,學生.姓名,學生.性別, 學生.電話,學生.生日, 員工.身份證字號,員工.城市, 員工.街道, 員工.薪水,員工.保險,員工.扣稅 FROM 學生 INNER JOIN 員工 ON 學生.姓名 = 員工.姓名 GO SELECT * FROM 學生員工_檢視

16-2-4 建立INSTEAD OF觸發程序- 在檢視表建立(範例2a) SQL指令碼檔：Ch16_2_4_05.sql 在【教務系統】資料庫的【學生員工_檢視】檢視表，針對INSERT指令建立名為【新增學生員工記錄】的INSTEAD OF觸發程序，可以同時新增【員工】和【學生】資料表的記錄資料，如下所示： CREATE TRIGGER 新增學生員工記錄 ON 學生員工_檢視 INSTEAD OF INSERT AS DECLARE @rowCount int SELECt @rowCount = COUNT(*) FROM Inserted IF @rowCount = 1 BEGIN INSERT 學生

16-2-4 建立INSTEAD OF觸發程序- 在檢視表建立(範例2b) SELECT 學號, 姓名, 性別, 電話, 生日 FROM Inserted INSERT 員工 SELECT 身份證字號, 姓名, 城市, 街道, 電話, 薪水, 保險, 扣稅 PRINT '新增兩筆記錄!' END ELSE RAISERROR('錯誤: 只允許能新增一筆記錄.',1,1)

16-2-4 建立INSTEAD OF觸發程序- 在檢視表建立(範例3) SQL指令碼檔：Ch16_2_4_06.sql 在【學生員工_檢視】檢視表新增記錄就會觸發INSTEAD OF觸發程序【新增學生員工記錄】，可以同時新增【員工】和【學生】資料表的記錄資料，如下所示： INSERT INTO 學生員工_檢視 VALUES ('S500', '陳允傑', '男','05-55522222','1995/12/25' 'F123450789','台北','仁愛路', 50000, 2000, 900)

16-2-5 使用UPDATE()函數-語法 UPDATE()函數可以檢查指定欄位是否有更新，它是建立觸發程序時，一個非常好用的函數，其基本語法如下所示： IF UPDATE(欄位名稱1) [AND | OR UPDATE(欄位名稱2)] 上述語法的IF指令使用UPDATE()函數檢查參數的欄位是否有更新，如果有，傳回True，否則為False。

16-2-5 使用UPDATE()函數-範例1a SQL指令碼檔：Ch16_2_5_01.sql CREATE TRIGGER 更新檢查 ON 教授 AFTER UPDATE AS DECLARE @count int SET @count = 0 IF UPDATE(職稱) BEGIN PRINT '更新職稱欄位!' SET @count = @count + 1

16-2-5 使用UPDATE()函數-範例1b END IF UPDATE(科系) BEGIN PRINT '更新科系欄位!' SET @count = @count + 1 IF @count > 0 PRINT '更新 [' + CONVERT(varchar, @count) + '] 個欄位!' ROLLBACK TRAN

16-2-5 使用UPDATE()函數-範例2 SQL指令碼檔：Ch16_2_5_02.sql 在【教授】資料表更新記錄時就會觸發執行【更新檢查】觸發程序，顯示更新哪些欄位，和更新的欄位數，如下所示： UPDATE 教授 SET 職稱 = '教授' WHERE 教授編號 = 'I003'

16-3 修改、停用與刪除DML觸發程序 16-3-1 修改觸發程序 16-3-2 停用觸發程序 16-3-3 刪除觸發程序

16-3-1 修改觸發程序- 使用Management Studio 請在「物件總管」視窗指定資料表或檢視表的觸發程序上，執行【右】鍵快顯功能表的【修改】指令，即可重新編輯觸發程序的T-SQL指令敘述。

16-3-1 修改觸發程序- 使用T-SQL指令(說明) T-SQL語言是使用ALTER TRIGGER指令來修改觸發程序，其基本語法和CREATE TRIGGER相同。 簡單的說，修改觸發程序就是重新定義觸發程序。

16-3-1 修改觸發程序- 使用T-SQL指令(範例1a) SQL指令碼檔：Ch16_3_1_01.sql 在【教務系統】資料庫的【教授】資料表修改名為【更新檢查】的觸發程序，額外顯示更新後的欄位值，如下所示： ALTER TRIGGER 更新檢查 ON 教授 AFTER UPDATE AS DECLARE @count int, @rank varchar(10), @dep varchar(5) SET @count = 0 IF UPDATE(職稱)

16-3-1 修改觸發程序- 使用T-SQL指令(範例1b) IF UPDATE(職稱) BEGIN SELECT @rank = 職稱 FROM Inserted PRINT '更新職稱: ' + @rank SET @count = @count + 1 END IF UPDATE(科系) SELECT @dep = 科系 FROM Inserted

16-3-1 修改觸發程序- 使用T-SQL指令(範例1c) PRINT '更新科系: ' + @dep SET @count = @count + 1 END IF @count > 0 BEGIN PRINT '更新 [' + CONVERT(varchar, @count) + '] 個欄位!' ROLLBACK TRAN

16-3-1 修改觸發程序- 使用T-SQL指令(範例2) SQL指令碼檔：Ch16_3_1_02.sql 在【教授】資料表更新記錄就會觸發執行【更新檢查】觸發程序，顯示更新哪些欄位、欄位值和更新欄位數，如下所示： UPDATE 教授 SET 職稱 = '講師' WHERE 教授編號 = 'I003'

16-3-2 停用觸發程序- 使用Management Studio

16-3-2 停用觸發程序- 使用T-SQL指令(語法) T-SQL語言是使用ALTER TABLE指令來停用與資料表結合的觸發程序，其語法如下所示： ALTER TABLE 資料表名稱 { ENABLE | DISABLE } TRIGGER { ALL | 觸發程序名稱 } 上述語法可以啟用或停用資料表指定或全部的觸發程序，ENABLE是啟用，DISABLE是停用，ALL是全部的觸發程序，或是指定的觸發程序名稱，如果不只一個請使用「,」逗號分隔。

16-3-2 停用觸發程序- 使用T-SQL指令(範例) SQL指令碼檔：Ch16_3_2.sql 停用【教授】資料表名為【更新檢查】的觸發程序，如下所示： ALTER TABLE 教授 DISABLE TRIGGER 更新檢查

16-3-3 刪除觸發程序- 使用Management Studio 在Manament Studio工具的「物件總管」視窗展開資料表或檢視表的觸發程序，在其上執行【右】鍵快顯功能表的【刪除】指令，再按【確定】鈕，即可刪除觸發程序。

16-3-3 刪除觸發程序- 使用T-SQL指令 T-SQL語言是使用DROP TRIGGER指令來刪除觸發程序，其基本語法如下所示： SQL指令碼檔：Ch16_3_3.sql 刪除【教授】資料表名為【更新檢查】的觸發程序，如下所示： DROP TRIGGER 更新檢查

16-4 DDL觸發程序-說明 DDL觸發程序（DDL Trigger）是一種特殊類型的觸發程序，它可以回應DDL指令（主要是指CREATE、ALTER和DROP開頭的指令）來執行資料庫的管理工作，例如：稽核與管理資料庫作業。 DDL觸發程序是在執行DDL指令後觸發執行，所以並不能建立類似DML觸發程序的INSTEAD OF觸發程序。

16-4 DDL觸發程序-時機 一般來說，在SQL Server使用DDL觸發程序的時機，如下所示： 保護資料庫綱要不會改變。 希望在更改資料庫綱要時，有一些反應來進行額外處理。 記錄資料庫綱要的改變或相關事件。

16-4 DDL觸發程序-語法 在T-SQL語言一樣是使用CREATE TRIGGER指令來建立DDL觸發程序，其基本語法如下所示： ON { ALL SERVER | DATABASE } { FOR | AFTER } 事件種類 AS T-SQL指令敘述

16-4 DDL觸發程序-範例1 SQL指令碼檔：Ch16_4_01.sql 建立名為【唯讀資料表】的DDL觸發程序，當在資料庫執行DROP TABLE或ALTER TABLE指令時，取消其操作，簡單的說，就是拒絕刪除或更改資料表設計，如下所示： CREATE TRIGGER 唯讀資料表 ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS BEGIN BEGIN TRAN PRINT '資料表綱要是唯讀的!' ROLLBACK TRAN END

16-4 DDL觸發程序-範例2 SQL指令碼檔：Ch16_4_02.sql DROP TABLE 熱銷產品