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

Presentation on theme: "16 觸發程序 16-1 觸發程序的基礎 16-2 DML觸發程序 16-3 修改、停用與刪除DML觸發程序 16-4 DDL觸發程序."— Presentation transcript:

1

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

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

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

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

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

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

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

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

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

11 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指令敘述

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

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

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

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

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

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

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

19 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

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

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

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

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

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

25 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

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

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

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

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

30 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.名稱,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

58 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

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