庫(kù)系統(tǒng)與應(yīng)用課件

1、資料庫(kù)系統(tǒng)與應(yīng)用吳家豪, Chia-Hao Wu明道大學(xué) 資訊傳播學(xué)系 助理教授2010年 6月4日資料庫(kù)系統(tǒng)與應(yīng)用吳家豪, Chia-Hao Wu第16章 觸發(fā)程序16-1 觸發(fā)程序的基礎(chǔ)16-2 DML觸發(fā)程序16-3 修改、停用與刪除DML觸發(fā)程序16-4 DDL觸發(fā)程序第16章 觸發(fā)程序16-1 觸發(fā)程序的基礎(chǔ)16-1 觸發(fā)程序的基礎(chǔ)-說(shuō)明觸發(fā)程序（Triggers）是一種特殊用途的預(yù)存程序，我們並不能單獨(dú)執(zhí)行觸發(fā)程序，因?yàn)樗窃趫?zhí)行T-SQL語(yǔ)言的DDL指令或DML指令產(chǎn)生事件時(shí)，系統(tǒng)主動(dòng)執(zhí)行的程序，我們並不能像預(yù)存程序或自訂函數(shù)一般，由使用者自行執(zhí)行觸發(fā)程序。如同預(yù)存程序與自訂函數(shù)

2、，觸發(fā)程序也是一組T-SQL指令敘述的集合（但是觸發(fā)程序沒(méi)有參數(shù)，也不能傳回值），可以使用觸發(fā)程序來(lái)執(zhí)行一些自動(dòng)化操作，例如：自動(dòng)更改或刪除相關(guān)聯(lián)的記錄資料、加強(qiáng)欄位的商業(yè)規(guī)則驗(yàn)證、比較資料更改前後的資料表狀態(tài)和建立不同資料庫(kù)的參考完整性。16-1 觸發(fā)程序的基礎(chǔ)-說(shuō)明觸發(fā)程序（Triggers）是16-1 觸發(fā)程序的基礎(chǔ)-種類(lèi)舊版SQL Server 2000支援DML觸發(fā)程序，新版2005提供DDL觸發(fā)程序，所以，SQL Server 2005的觸發(fā)程序可以分為兩種，其說(shuō)明如下所示：DML觸發(fā)程序（DML Triggers）：當(dāng)執(zhí)行資料表操作指令I(lǐng)NSERT、UPDATE和DELETE時(shí)，

3、所自動(dòng)執(zhí)行的觸發(fā)程序，可以用來(lái)驗(yàn)證商業(yè)規(guī)則，或執(zhí)行更複雜的資料驗(yàn)證程序。DDL觸發(fā)程序（DDL Triggers）：一種特殊類(lèi)型的觸發(fā)程序，它可以回應(yīng)DDL指令（主要是指CREATE、ALTER和DROP開(kāi)頭的指令）來(lái)執(zhí)行資料庫(kù)的管理工作，例如：稽核與管理資料庫(kù)作業(yè)。16-1 觸發(fā)程序的基礎(chǔ)-種類(lèi)舊版SQL Server 2016-1 觸發(fā)程序的基礎(chǔ)-用途1觸發(fā)程序可以驗(yàn)證商業(yè)規(guī)則，或執(zhí)行更複雜的資料驗(yàn)證程序，例如：檢查使用者是否調(diào)整圖書(shū)價(jià)格超過(guò)百分之五、庫(kù)存是否足夠和客戶是否擁有足夠的採(cǎi)購(gòu)額度等。觸發(fā)程序可以用來(lái)維持多資料表間的資料完整性，我們可以透過(guò)觸發(fā)程序來(lái)更改相關(guān)聯(lián)的記錄資料。例如：在

4、【訂單】資料表刪除一筆記錄後，使用觸發(fā)程序在【訂單明細(xì)】資料表刪除此訂單相關(guān)的所有項(xiàng)目資料；或是出貨一項(xiàng)商品，就自動(dòng)將庫(kù)存量減一。16-1 觸發(fā)程序的基礎(chǔ)-用途1觸發(fā)程序可以驗(yàn)證商業(yè)規(guī)則，或16-1 觸發(fā)程序的基礎(chǔ)-用途2觸發(fā)程序能夠檢查資料更改是否是允許的操作，如果不允許就回復(fù)資料更改，我們也可以使用觸發(fā)程序直接更改或取消原來(lái)的資料操作，並且使用電子郵件發(fā)出預(yù)警的通知郵件。觸發(fā)程序可以分析操作來(lái)執(zhí)行其他的後續(xù)處理，因?yàn)橛|發(fā)程序可以比較資料更改前後的資料表狀態(tài)，換句話說(shuō)，我們就可以針對(duì)比較結(jié)果來(lái)執(zhí)行進(jìn)一步的處理。觸發(fā)程序可以取代系統(tǒng)預(yù)設(shè)產(chǎn)生的錯(cuò)誤訊息，讓我們建立自訂錯(cuò)誤訊息來(lái)回應(yīng)用戶端程式。1

5、6-1 觸發(fā)程序的基礎(chǔ)-用途2觸發(fā)程序能夠檢查資料更改是否16-2 DML觸發(fā)程序16-2-1 建立DML觸發(fā)程序16-2-2 DML觸發(fā)程序與條件約束16-2-3 建立AFTER觸發(fā)程序16-2-4 建立INSTEAD OF觸發(fā)程序16-2-5 使用UPDATE()函數(shù)16-2 DML觸發(fā)程序16-2-1 建立DML觸發(fā)程序16-2 DML觸發(fā)程序-說(shuō)明DML觸發(fā)程序是當(dāng)執(zhí)行INSERT、UPDATE和DELETE資料表操作指令時(shí)，一種自動(dòng)執(zhí)行的觸發(fā)程序，可以讓我們建立強(qiáng)制執(zhí)行的商業(yè)規(guī)則、擴(kuò)充條件約束、預(yù)設(shè)值和規(guī)則等維持資料完整性的條件。16-2 DML觸發(fā)程序-說(shuō)明DML觸發(fā)程序是當(dāng)執(zhí)行I

6、NSE16-2 DML觸發(fā)程序-種類(lèi)SQL Server的DML觸發(fā)程序依觸發(fā)時(shí)機(jī)可以分為兩種，如下所示：AFTER觸發(fā)程序：當(dāng)執(zhí)行INSERT、UPDATE和DELETE指令且資料已經(jīng)改變後，所觸發(fā)和執(zhí)行的觸發(fā)程序，主要是用來(lái)執(zhí)行一些檢查或善後處理，如果有錯(cuò)誤，更改的資料可以回復(fù)至更改前的值。INSTEAD OF觸發(fā)程序：這是在資料改變前所觸發(fā)和執(zhí)行的觸發(fā)程序，可以驗(yàn)證資料或取代原本需要執(zhí)行的操作。16-2 DML觸發(fā)程序-種類(lèi)SQL Server的DML觸16-2-1 建立DML觸發(fā)程序-使用Management Studio如同預(yù)存程序，我們一樣可以使用Management Studio

7、工具的指令或直接新增查詢來(lái)建立DML觸發(fā)程序，如下圖所示：16-2-1 建立DML觸發(fā)程序-使用Management 16-2-1 建立DML觸發(fā)程序-使用T-SQL指令(語(yǔ)法)在T-SQL語(yǔ)言是使用CREATE TRIGGER指令建立觸發(fā)程序，其基本語(yǔ)法如下所示：CREATE TRIGGER 觸發(fā)程序名稱ON 資料表名稱 | 檢視表名稱 WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AST-SQL指令敘述16-2-1 建立DML觸發(fā)程序-使用T-SQL指令(語(yǔ)法)16-2-1 建立DML觸發(fā)程序-使用T-S

8、QL指令(Deleted與Inserted資料表1)在DML觸發(fā)程序可以查詢系統(tǒng)自動(dòng)產(chǎn)生的Deleted與Inserted邏輯資料表（Logical Tables），這兩個(gè)資料表能夠保留更改前後的記錄資料，其欄位資料完全對(duì)應(yīng)ON子句資料表的欄位定義，我們一樣是使用SELECT指令來(lái)查詢Deleted與Inserted資料表，如下所示：SELECT * FROM InsertedSELECT * FROM Deleted16-2-1 建立DML觸發(fā)程序-使用T-SQL指令(Del16-2-1 建立DML觸發(fā)程序-使用T-SQL指令(Deleted與Inserted資料表2)資料表內(nèi)容依不同DML

9、指令，如下表所示：16-2-1 建立DML觸發(fā)程序-使用T-SQL指令(Del16-2-1 建立DML觸發(fā)程序-使用T-SQL指令(範(fàn)例1)SQL指令碼檔：Ch16-2-1-01.sql在【教務(wù)系統(tǒng)】資料庫(kù)的【課程】資料表建立名為newRecord的DML觸發(fā)程序，對(duì)應(yīng)INSERT指令的事件，如下所示：CREATE TRIGGER newRecordON 課程 FOR INSERTAS BEGIN DECLARE name varchar(30) SELECT name = 名稱 FROM Inserted PRINT nameEND16-2-1 建立DML觸發(fā)程序-使用T-SQL指令(範(fàn)例1

10、16-2-1 建立DML觸發(fā)程序-使用T-SQL指令(範(fàn)例2)SQL指令碼檔：Ch16-2-1-02.sql在【課程】資料表新增一筆記錄時(shí)，就會(huì)觸發(fā)執(zhí)行newRecord觸發(fā)程序，顯示新增記錄的課程名稱，如下所示：INSERT INTO 課程 VALUES (CS301,作業(yè)系統(tǒng)概論,4)16-2-1 建立DML觸發(fā)程序-使用T-SQL指令(範(fàn)例216-2-2 DML觸發(fā)程序與條件約束-1AFTER觸發(fā)程序與條件約束AFTER觸發(fā)程序的執(zhí)行流程是當(dāng)使用者執(zhí)行INSERT、UPDATE和DELETE的DML操作指令後，依序檢查條件約束、建立Deleted與Inserted資料表，並且在實(shí)際更新資

11、料表的記錄資料後才執(zhí)行AFTER觸發(fā)程序，換句話說(shuō)，如果操作有違反資料表的條件約束，壓根兒就不會(huì)執(zhí)行到AFTER觸發(fā)程序。所以，在AFTER觸發(fā)程序並沒(méi)有辦法事先作一些處理，來(lái)避免違反資料表的條件約束，它只能增加額外的欄位檢查條件和處理來(lái)進(jìn)一步維護(hù)資料完整性。例如：客戶需要擁有足夠的採(cǎi)購(gòu)額度，才允許此客戶下訂單採(cǎi)購(gòu)。16-2-2 DML觸發(fā)程序與條件約束-1AFTER觸發(fā)程序16-2-2 DML觸發(fā)程序與條件約束-2INSTEAD OF觸發(fā)程序與條件約束INSTEAD OF觸發(fā)程序是在Deleted與Inserted資料表建立後就執(zhí)行，不過(guò)，它是在其他操作之前（包含條件約束）。換句話說(shuō)，INS

12、TEAD OF觸發(fā)程序是在執(zhí)行條件約束前執(zhí)行，我們可以在INSTEAD OF觸發(fā)程序預(yù)先處理來(lái)避免違反資料表的條件約束。事實(shí)上，INSTEAD OF觸發(fā)程序主要是用來(lái)攔截和取代指定的操作，我們需要在INSTEAD OF觸發(fā)程序自行撰寫(xiě)指令來(lái)更改或取消原來(lái)的資料操作，不過(guò)，在INSTEAD OF觸發(fā)程序執(zhí)行的操作還是需要經(jīng)過(guò)條件約束的檢查。16-2-2 DML觸發(fā)程序與條件約束-2INSTEAD O16-2-3 建立AFTER觸發(fā)程序-說(shuō)明在SQL Server資料表可以建立多個(gè)AFTER觸發(fā)程序，但只能有一個(gè)INSTEAD OF觸發(fā)程序，而且，AFTER觸發(fā)程序並不能使用在檢視表，只有INST

13、EAD OF觸發(fā)程序可以使用在檢視表。AFTER和INSTEAD OF觸發(fā)程序依執(zhí)行的指令不同，可以再分為：INSERT觸發(fā)程序、UPDATE觸發(fā)程序和DELETE觸發(fā)程序三種。16-2-3 建立AFTER觸發(fā)程序-說(shuō)明在SQL Serv16-2-3 建立AFTER觸發(fā)程序-AFTER INSERT觸發(fā)程序(說(shuō)明)AFTER的INSERT觸發(fā)程序是當(dāng)使用者在資料表插入一筆記錄時(shí)觸發(fā)，因?yàn)槭切略鲇涗?，所以新增的記錄同時(shí)也會(huì)新增至Inserted資料表，並不會(huì)用到Deleted資料表。16-2-3 建立AFTER觸發(fā)程序-AFTER INSER16-2-3 建立AFTER觸發(fā)程序-AFTER IN

14、SERT觸發(fā)程序(範(fàn)例1)SQL指令碼檔：Ch16-2-3-01.sql在【教務(wù)系統(tǒng)】資料庫(kù)的【班級(jí)】資料表，針對(duì)INSERT指令建立名為【檢查上課數(shù)】的AFTER觸發(fā)程序，限制每一位學(xué)生最多只能上三門(mén)課程，如下所示：CREATE TRIGGER 檢查上課數(shù)ON 班級(jí) AFTER INSERTAS BEGIN IF ( SELECT COUNT(學(xué)號(hào)) FROM 班級(jí) WHERE 學(xué)號(hào) = ( SELECT 學(xué)號(hào) FROM Inserted) ) 3 BEGIN RAISERROR(Take too many courses!,1,1) ROLLBACK END END16-2-3 建立AFT

15、ER觸發(fā)程序-AFTER INSER16-2-3 建立AFTER觸發(fā)程序-AFTER INSERT觸發(fā)程序(範(fàn)例2)SQL指令碼檔：Ch16-2-3-02.sql在【班級(jí)】資料表新增記錄就會(huì)觸發(fā)執(zhí)行【檢查上課數(shù)】觸發(fā)程序，如果學(xué)生的上課數(shù)太多，就會(huì)顯示錯(cuò)誤訊息且回復(fù)資料不允許新增此記錄，如下所示：INSERT INTO 班級(jí) VALUES (I004, S001, CS111,03:00:00, 321-M)16-2-3 建立AFTER觸發(fā)程序-AFTER INSER16-2-3 建立AFTER觸發(fā)程序-AFTER UPDATE觸發(fā)程序(說(shuō)明)AFTER的UPDATE觸發(fā)程序當(dāng)使用者在資料表更

16、新記錄時(shí)觸發(fā)，因?yàn)槭歉掠涗?，更新資料是新增至Inserted資料表，原始資料是新增至Deleted資料表。16-2-3 建立AFTER觸發(fā)程序-AFTER UPDAT16-2-3 建立AFTER觸發(fā)程序-AFTER UPDATE觸發(fā)程序(範(fàn)例1)SQL指令碼檔：Ch16-2-3-03.sql在【教務(wù)系統(tǒng)】資料庫(kù)的【課程】資料表建立名為【檢查學(xué)分?jǐn)?shù)】的觸發(fā)程序，限制更新的學(xué)分?jǐn)?shù)只能增加，不能減少，如下所示：CREATE TRIGGER 檢查學(xué)分?jǐn)?shù)ON 課程 AFTER UPDATEAS BEGIN DECLARE new int, old int SELECT new = 學(xué)分 FROM In

17、serted SELECT old = 學(xué)分 FROM Deleted IF old new BEGIN PRINT Cant update Credit column! ROLLBACK TRAN END END16-2-3 建立AFTER觸發(fā)程序-AFTER UPDAT16-2-3 建立AFTER觸發(fā)程序-AFTER UPDATE觸發(fā)程序(範(fàn)例2)SQL指令碼檔：Ch16-2-3-04.sql在【課程】資料表更新記錄時(shí)就會(huì)觸發(fā)執(zhí)行【檢查學(xué)分?jǐn)?shù)】觸發(fā)程序，檢查更改的學(xué)分?jǐn)?shù)是否是增加，如下所示：UPDATE 課程 SET 學(xué)分 = 3WHERE 課程編號(hào) = CS30116-2-3 建立AFT

18、ER觸發(fā)程序-AFTER UPDAT16-2-3 建立AFTER觸發(fā)程序-AFTER DELETE觸發(fā)程序(說(shuō)明)AFTER的DELETE觸發(fā)程序是當(dāng)使用者在資料表刪除一筆記錄時(shí)觸發(fā)，因?yàn)槭莿h除記錄，所以刪除的記錄同時(shí)也會(huì)新增至Deleted資料表，並不會(huì)用到Inserted資料表。16-2-3 建立AFTER觸發(fā)程序-AFTER DELET16-2-3 建立AFTER觸發(fā)程序-AFTER DELETE觸發(fā)程序(範(fàn)例1)SQL指令碼檔：Ch16-2-3-05.sql在【教務(wù)系統(tǒng)】資料庫(kù)的【員工】資料表，同時(shí)針對(duì)DELETE和UPDATE指令建立名為【員工管理】的AFTER觸發(fā)程序，可以檢查刪除

19、或更改的姓名是否存在【學(xué)生】資料表，如果存在，就不允許刪除與更新，如下所示：CREATE TRIGGER 員工管理ON 員工 AFTER DELETE, UPDATEAS IF EXISTS (SELECT * FROM 學(xué)生 WHERE 姓名 = ( SELECT 姓名 FROM Deleted)BEGIN RAISERROR(Invalid Name!,1,1) ROLLBACK TRANEND16-2-3 建立AFTER觸發(fā)程序-AFTER DELET16-2-3 建立AFTER觸發(fā)程序-AFTER DELETE觸發(fā)程序(範(fàn)例2)SQL指令碼檔：Ch16-2-3-06.sql在【員工】資

20、料表刪除記錄時(shí)就會(huì)觸發(fā)【員工管理】觸發(fā)程序，如果員工姓名存在【學(xué)生】資料表，就顯示錯(cuò)誤訊息，並且回復(fù)資料不允許刪除這筆記錄，如下所示：DELETE 員工WHERE 身份證字號(hào) = F33221304616-2-3 建立AFTER觸發(fā)程序-AFTER DELET16-2-3 建立AFTER觸發(fā)程序-AFTER DELETE觸發(fā)程序(範(fàn)例3)SQL指令碼檔：Ch16-2-3-07.sql在【員工】資料表更新記錄時(shí)就會(huì)觸發(fā)執(zhí)行【員工管理】的觸發(fā)程序，如果更新的員工姓名存在【學(xué)生】資料表，就顯示錯(cuò)誤訊息，並且回復(fù)資料不允許更新記錄，如下所示：UPDATE 員工SET 薪水 = 55000WHERE 身

21、份證字號(hào) = F33221304616-2-3 建立AFTER觸發(fā)程序-AFTER DELET16-2-4 建立INSTEAD OF觸發(fā)程序-說(shuō)明INSTEAD OF觸發(fā)程序可以攔截和取代指定操作，因?yàn)橛|發(fā)的操作指令並不會(huì)執(zhí)行，所以在INSTEAD OF觸發(fā)程序就需要執(zhí)行這些操作。不同於AFTER觸發(fā)程序，INSTEAD OF觸發(fā)程序可以使用在檢視表，讓檢視表成為可編輯的檢視表。不過(guò)，INSTEAD OF觸發(fā)程序並不支援遞迴呼叫，而且在同一個(gè)資料表或檢視表只能擁有一個(gè)INSTEAD OF觸發(fā)程序。16-2-4 建立INSTEAD OF觸發(fā)程序-說(shuō)明INST16-2-4 建立INSTEAD OF

22、觸發(fā)程序-建立(說(shuō)明)INSTEAD OF觸發(fā)程序一樣分為INSERT、UPDATE和DELETE三種，因其架構(gòu)和AFTER觸發(fā)程序類(lèi)似，所以筆者只準(zhǔn)備使用INSERT事件來(lái)說(shuō)明如何在資料表建立INSTEAD OF觸發(fā)程序。16-2-4 建立INSTEAD OF觸發(fā)程序-建立(說(shuō)明)16-2-4 建立INSTEAD OF觸發(fā)程序-建立(範(fàn)例1)SQL指令碼檔：Ch16-2-4-01.sql在【教務(wù)系統(tǒng)】資料庫(kù)的【課程】資料表，針對(duì)INSERT指令建立名為【新增課程記錄】的INSTEAD OF觸發(fā)程序，如果新增記錄的課程編號(hào)存在，就改為更新課程記錄，如下所示：CREATE TRIGGER 新增課

23、程記錄ON 課程 INSTEAD OF INSERTAS BEGINIF EXISTS (SELECT * FROM 課程 WHERE 課程編號(hào) = ( SELECT 課程編號(hào) FROM Inserted) 16-2-4 建立INSTEAD OF觸發(fā)程序-建立(範(fàn)例116-2-4 建立INSTEAD OF觸發(fā)程序-建立(範(fàn)例2)BEGIN UPDATE 課程 SET 課程.名稱 = Inserted.名稱, 課程.學(xué)分 = Inserted.學(xué)分 FROM 課程 JOIN Inserted ON 課程.課程編號(hào) = Inserted.課程編號(hào) PRINT Update One Record!

24、ENDELSE BEGIN INSERT 課程 SELECT * FROM Inserted PRINT Insert One Record! ENDEND16-2-4 建立INSTEAD OF觸發(fā)程序-建立(範(fàn)例216-2-4 建立INSTEAD OF觸發(fā)程序-建立(範(fàn)例3)SQL指令碼檔：Ch16-2-4-02.sql在【課程】資料表新增記錄就會(huì)觸發(fā)INSTEAD OF觸發(fā)程序【新增課程記錄】，以此例是存在的課程編號(hào)，如下所示：INSERT INTO 課程 VALUES (CS213, 物件導(dǎo)向程式設(shè)計(jì), 4)16-2-4 建立INSTEAD OF觸發(fā)程序-建立(範(fàn)例316-2-4 建立I

25、NSTEAD OF觸發(fā)程序-建立(範(fàn)例4)SQL指令碼檔：Ch16-2-4-03.sql在【課程】資料表新增記錄就會(huì)觸發(fā)INSTEAD OF觸發(fā)程序【新增課程記錄】，以此例是新的課程編號(hào)，如下所示：INSERT INTO 課程 VALUES (CS333, 物件導(dǎo)向程式設(shè)計(jì)(2), 3)16-2-4 建立INSTEAD OF觸發(fā)程序-建立(範(fàn)例416-2-4 建立INSTEAD OF觸發(fā)程序-在檢視表建立(說(shuō)明)INSTEAD OF觸發(fā)程序也可以使用在檢視表（AFTER觸發(fā)程序不能使用在檢視表），可以讓原來(lái)不可編輯的檢視表成為可編輯的檢視表。如果是合併查詢的檢視表，就可以同時(shí)插入、更新或刪除多

26、個(gè)資料表的記錄資料。例如：建立同時(shí)是學(xué)生和員工的【學(xué)生員工_檢視】檢視表後，就可以新增此檢視表的INSTEAD OF觸發(fā)程序，當(dāng)在檢視表新增記錄，即可同時(shí)在【學(xué)生】和【員工】資料表各新增一筆記錄。16-2-4 建立INSTEAD OF觸發(fā)程序-在檢視表建立16-2-4 建立INSTEAD OF觸發(fā)程序-在檢視表建立(範(fàn)例1)SQL指令碼檔：Ch16-2-4-04.sql建立【學(xué)生】與【員工】資料表的合併查詢檢視表【學(xué)生員工_檢視】，可以同時(shí)顯示是學(xué)生和員工的記錄資料，如下所示：CREATE VIEW 學(xué)生員工_檢視 ASSELECT 學(xué)生.學(xué)號(hào),學(xué)生.姓名,學(xué)生.性別, 學(xué)生.電話,學(xué)生.生日

27、, 員工.身份證字號(hào),員工.城市,員工.街道, 員工.薪水,員工.保險(xiǎn),員工.扣稅FROM 學(xué)生 INNER JOIN 員工ON 學(xué)生.姓名 = 員工.姓名GOSELECT * FROM 學(xué)生員工_檢視16-2-4 建立INSTEAD OF觸發(fā)程序-在檢視表建立16-2-4 建立INSTEAD OF觸發(fā)程序-在檢視表建立(範(fàn)例2)SQL指令碼檔：Ch16-2-4-05.sql在【教務(wù)系統(tǒng)】資料庫(kù)的【學(xué)生員工_檢視】檢視表，針對(duì)INSERT指令建立名為【新增學(xué)生員工記錄】的INSTEAD OF觸發(fā)程序，可以同時(shí)新增【員工】和【學(xué)生】資料表的記錄資料，如下所示：CREATE TRIGGER 新增學(xué)

28、生員工記錄ON 學(xué)生員工_檢視 INSTEAD OF INSERTAS DECLARE rowCount intSELECt rowCount = COUNT(*) FROM InsertedIF rowCount = 1 BEGIN 16-2-4 建立INSTEAD OF觸發(fā)程序-在檢視表建立16-2-4 建立INSTEAD OF觸發(fā)程序-在檢視表建立(範(fàn)例3)INSERT 學(xué)生 SELECT 學(xué)號(hào), 姓名, 性別, 電話, 生日 FROM Inserted INSERT 員工 SELECT 身份證字號(hào), 姓名, 城市, 街道, 電話, 薪水, 保險(xiǎn), 扣稅 FROM Inserted PR

29、INT Insert Two Records! ENDELSE RAISERROR(Limit INSERT to a single row.,1,1)16-2-4 建立INSTEAD OF觸發(fā)程序-在檢視表建立16-2-4 建立INSTEAD OF觸發(fā)程序-在檢視表建立(範(fàn)例4)SQL指令碼檔：Ch16-2-4-06.sql在【學(xué)生員工_檢視】檢視表新增記錄就會(huì)觸發(fā)INSTEAD OF觸發(fā)程序【新增學(xué)生員工記錄】，可以同時(shí)新增【員工】和【學(xué)生】資料表的記錄資料，如下所示：INSERT INTO 學(xué)生員工_檢視 VALUES (S500, 陳允傑, 男,05-55522222,1970/12/

30、25 F123450789,臺(tái)北,仁愛(ài)路, 50000, 2000, 900)16-2-4 建立INSTEAD OF觸發(fā)程序-在檢視表建立16-2-5 使用UPDATE()函數(shù)-語(yǔ)法UPDATE()函數(shù)可以檢查指定欄位是否有更新，它是建立觸發(fā)程序時(shí)，一個(gè)非常好用的函數(shù)，其基本語(yǔ)法如下所示：IF UPDATE(欄位名稱1) AND | OR UPDATE(欄位名稱2)上述語(yǔ)法的IF指令使用UPDATE()函數(shù)檢查參數(shù)的欄位是否有更新，如果有，傳回True，否則為False。16-2-5 使用UPDATE()函數(shù)-語(yǔ)法UPDATE()16-2-5 使用UPDATE()函數(shù)-範(fàn)例1SQL指令碼檔：C

31、h16-2-5-01.sql在【教務(wù)系統(tǒng)】資料庫(kù)的【教授】資料表建立名為【更新檢查】的觸發(fā)程序，可以使用UPDATE()函數(shù)來(lái)檢查更新哪些欄位，和更新的欄位數(shù)，如下所示：CREATE TRIGGER 更新檢查ON 教授 AFTER UPDATEAS DECLARE count intSET count = 0IF UPDATE(職稱)BEGIN PRINT Update Rank column! SET count = count + 1END 16-2-5 使用UPDATE()函數(shù)-範(fàn)例1SQL指令碼檔16-2-5 使用UPDATE()函數(shù)-範(fàn)例2IF UPDATE(科系)BEGIN PRI

32、NT Update Department column! SET count = count + 1ENDIF count 0BEGIN PRINT Update + CONVERT(varchar, count) + columns! ROLLBACK TRANEND 16-2-5 使用UPDATE()函數(shù)-範(fàn)例2IF UPDA16-2-5 使用UPDATE()函數(shù)-範(fàn)例3SQL指令碼檔：Ch16-2-5-02.sql在【教授】資料表更新記錄時(shí)就會(huì)觸發(fā)執(zhí)行【更新檢查】觸發(fā)程序，顯示更新哪些欄位，和更新的欄位數(shù)，如下所示：UPDATE 教授 SET 職稱 = 教授WHERE 教授編號(hào) = I0

33、0316-2-5 使用UPDATE()函數(shù)-範(fàn)例3SQL指令碼檔16-3 修改、停用與刪除DML觸發(fā)程序16-3-1 修改觸發(fā)程序16-3-2 停用觸發(fā)程序16-3-3 刪除觸發(fā)程序16-3 修改、停用與刪除DML觸發(fā)程序16-3-1 修改觸16-3-1 修改觸發(fā)程序-使用Management Studio對(duì)於資料庫(kù)已經(jīng)存在的觸發(fā)程序，我們可以使用Management Studio工具來(lái)修改觸發(fā)程序的內(nèi)容。請(qǐng)?jiān)谖锛偣芤暣爸付ㄙY料表或檢視表的觸發(fā)程序上，執(zhí)行【右】鍵快顯功能表的【修改】指令，即可重新編輯觸發(fā)程序的T-SQL指令敘述。16-3-1 修改觸發(fā)程序-使用Management Stu1

34、6-3-1 修改觸發(fā)程序-使用T-SQL指令(說(shuō)明)T-SQL語(yǔ)言是使用ALTER TRIGGER指令來(lái)修改觸發(fā)程序，其基本語(yǔ)法和CREATE TRIGGER相同。簡(jiǎn)單的說(shuō)，修改觸發(fā)程序就是重新定義觸發(fā)程序。16-3-1 修改觸發(fā)程序-使用T-SQL指令(說(shuō)明)T-S16-3-1 修改觸發(fā)程序-使用T-SQL指令(範(fàn)例1)SQL指令碼檔：Ch16-3-1-01.sql在【教務(wù)系統(tǒng)】資料庫(kù)的【教授】資料表修改名為【更新檢查】的觸發(fā)程序，額外顯示更新後的欄位值，如下所示：ALTER TRIGGER 更新檢查ON 教授 AFTER UPDATEAS DECLARE count int, rank v

35、archar(10), dep varchar(5)SET count = 016-3-1 修改觸發(fā)程序-使用T-SQL指令(範(fàn)例1)SQ16-3-1 修改觸發(fā)程序-使用T-SQL指令(範(fàn)例2)IF UPDATE(職稱)BEGIN SELECT rank = 職稱 FROM Inserted PRINT Update Rank: + rank SET count = count + 1END IF UPDATE(科系)16-3-1 修改觸發(fā)程序-使用T-SQL指令(範(fàn)例2)IF16-3-1 修改觸發(fā)程序-使用T-SQL指令(範(fàn)例3)BEGIN SELECT dep = 科系 FROM Inse

36、rted PRINT Update Department: + dep SET count = count + 1ENDIF count 0BEGIN PRINT Update + CONVERT(varchar, count) + columns! ROLLBACK TRANEND16-3-1 修改觸發(fā)程序-使用T-SQL指令(範(fàn)例3)BE16-3-1 修改觸發(fā)程序-使用T-SQL指令(範(fàn)例4)SQL指令碼檔：Ch16-3-1-02.sql在【教授】資料表更新記錄就會(huì)觸發(fā)執(zhí)行【更新檢查】觸發(fā)程序，顯示更新哪些欄位、欄位值和更新欄位數(shù)，如下所示：UPDATE 教授 SET 職稱 = 講師WHE

37、RE 教授編號(hào) = I00316-3-1 修改觸發(fā)程序-使用T-SQL指令(範(fàn)例4)SQ16-3-2 停用觸發(fā)程序-使用Management Studio在Management Studio工具的物件總管視窗，請(qǐng)?jiān)谥付ㄙY料表或檢視表的觸發(fā)程序上，執(zhí)行【右】鍵快顯功能表的【停用】指令，即可停用觸發(fā)程序。16-3-2 停用觸發(fā)程序-使用Management Stu16-3-2 停用觸發(fā)程序-使用T-SQL指令(語(yǔ)法)T-SQL語(yǔ)言是使用ALTER TABLE指令來(lái)停用與資料表結(jié)合的觸發(fā)程序，其語(yǔ)法如下所示：ALTER TABLE 資料表名稱 ENABLE | DISABLE TRIGGER ALL | 觸發(fā)程序名稱 上述語(yǔ)法可以啟用或停用資料表指定或全部的觸發(fā)程序，ENABLE是啟用，DISABLE是停用，ALL是全部的觸發(fā)程序，或是指定的觸發(fā)程序名稱，如果不只一個(gè)請(qǐng)使用,逗號(hào)分隔。16-3-2 停用觸發(fā)程序-使用T-SQL指令(語(yǔ)法)T-S16-3-2 停用觸發(fā)程序-使用T-SQL指令(範(fàn)例)SQL指令碼檔：Ch16-3-2.sql停用【教授】資料表名為【更新檢查】的觸發(fā)程序，如下所示：ALTER TABLE 教授DISABLE TRIGGER 更新檢查16