第11章 視圖、存儲過程和觸發(fā)器的建立和使用

1、數(shù)據(jù)庫系統(tǒng)原理數(shù)據(jù)庫系統(tǒng)原理與應(yīng)用技術(shù)與應(yīng)用技術(shù)主講：陳漫紅主講：陳漫紅 北京聯(lián)合大學(xué)師范學(xué)院北京聯(lián)合大學(xué)師范學(xué)院 電氣信息系電氣信息系第第11章視圖、存儲過程和觸發(fā)器的建章視圖、存儲過程和觸發(fā)器的建立和使用立和使用 n11.1視圖視圖 n11.2 存儲過程存儲過程 n11.3 觸發(fā)器觸發(fā)器 n11.4 本章小結(jié)本章小結(jié)11.1 視圖11.1.1 視圖概念和作用和定義11.1.2 通過視圖進(jìn)行的查詢、插入、修改、刪除數(shù)據(jù) 11.1.3 視圖的管理11.1.1 視圖概念n視圖可以被看成是虛擬表。n視圖中的數(shù)據(jù)不物理地存儲在數(shù)據(jù)庫內(nèi)。nSELECT 語句的結(jié)果集構(gòu)成了視圖的內(nèi)容?；颈砘颈?基本

2、表基本表2視圖視圖視圖的作用n簡化數(shù)據(jù)查詢語句n使用戶能從多角度看到同一數(shù)據(jù)n提高了數(shù)據(jù)的安全性n提供了一定程度的邏輯獨(dú)立性11.1.1 視圖的創(chuàng)建視圖的創(chuàng)建創(chuàng)建視圖時應(yīng)該注意：（1）只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖，在視圖中最多只能引用1024列，視圖中記錄的數(shù)目限制只由其基表中的記錄數(shù)決定。（2）如果視圖引用的基表或者視圖被刪除，則該視圖不能再被使用，直到創(chuàng)建新的基表或者視圖。（3）如果視圖中某一列是函數(shù)、數(shù)學(xué)表達(dá)式、常量或者來自多個表的列名相同，則必須為列定義名稱。（4）不能在視圖上創(chuàng)建索引，不能在規(guī)則、默認(rèn)、觸發(fā)器的定義中引用視圖。（5）當(dāng)通過視圖查詢數(shù)據(jù)時，SQL Server要檢查以確保

3、語句中涉及的所有數(shù)據(jù)庫對象存在，每個數(shù)據(jù)庫對象在語句的上下文中有效，而且數(shù)據(jù)修改語句不能違反數(shù)據(jù)完整性規(guī)則。（6）視圖的名稱必須遵循標(biāo)識符的規(guī)則，且對每個用戶必須是惟一的。利用利用SQL Server管理平臺創(chuàng)建視圖管理平臺創(chuàng)建視圖（1）在SQL Server管理平臺中，展開指定的服務(wù)器，打開要創(chuàng)建視圖的數(shù)據(jù)庫文件夾，右擊該數(shù)據(jù)庫圖標(biāo)，從彈出的快捷菜單中依次選擇“新建（New）視圖”選項(xiàng) 。利用利用SQL Server管理平臺創(chuàng)建視圖管理平臺創(chuàng)建視圖選中“視圖”節(jié)點(diǎn)單擊右鍵，在彈出的快捷菜單中依次選擇“新建視圖” 。利用利用SQL Server管理平臺創(chuàng)建視圖管理平臺創(chuàng)建視圖（2）選擇好創(chuàng)建視

4、圖所需的表、視圖、函數(shù)后，如選擇student,sc,course表，通過單擊字段左邊的復(fù)選框選擇需要的字段。 利用利用SQL Server管理平臺創(chuàng)建視圖管理平臺創(chuàng)建視圖n單擊工具欄中的“保存”按鈕，或者單擊鼠標(biāo)右鍵，從快捷菜單中選擇保存選項(xiàng)保存視圖，出現(xiàn)選擇名稱對話框，輸入視圖名，即可完成視圖的創(chuàng)建。 .使用使用Transact-SQL語句創(chuàng)建視圖語句創(chuàng)建視圖n語法： CREATE VIEW （視圖列名表）AS 查詢語句 WITH CHECK OPTION 定義單源表視圖n建立信息系學(xué)生的視圖。CREATE VIEW IS_StudentAS SELECT Sno, Sname, Sage

5、FROM Student WHERE Sdept = 信息系 定義多源表視圖n建立信息系選修了c01號課程的學(xué)生的視圖。CREATE VIEW V_IS_S1(Sno, Sname, Grade)ASSELECT Student.Sno, Sname, grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = 信息系 AND SC.Cno = c01 在已有視圖上定義新視圖n建立信息系選修了課程且成績在90分以上的學(xué)生的視圖。CREATE VIEW V_IS_S2ASSELECT V_IS_S1.Sno, V_IS_S1.

6、Sname, sc.Grade FROM V_IS_S1 join sc on V_IS_S1.sno=sc.sno WHERE sc.Grade = 90 定義帶表達(dá)式的視圖n定義一個反映學(xué)生出生年份的視圖。CREATE VIEW BT_S(Sno, Sname, Sbirth)AS SELECT Sno, Sname, 2010-Sage FROM Student 含分組統(tǒng)計(jì)信息的視圖n定義一個存放每個學(xué)生的學(xué)號及平均成績的視圖。CREATE VIEW S_G(Sno, AverageGrade)AS SELECT Sno, AVG(Grade) FROM SCGROUP BY Sno 對

7、視圖進(jìn)行加密對視圖進(jìn)行加密n創(chuàng)建一個 名為“v綜合信息”的視圖，用于查詢學(xué)生的學(xué)號、姓名、專業(yè)名、課程名、成績等信息。并對視圖的定義進(jìn)行加密。CREATE VIEW V綜合信息WITH ENCRYPTION ASSELECT student.sno,sname,sdept,cname,grade FROM student, sc , course WHERE student.sno = sc.sno and sc. cno= o使用視圖模板創(chuàng)建視圖使用視圖模板創(chuàng)建視圖 （1）在）在SQL Server管理平臺中，選擇管理平臺中，選擇“視圖視圖”菜單中的菜單中的“模板資源管理器模板資源管理器 ”

8、選項(xiàng)，如圖選項(xiàng)，如圖11-7所示。所示。使用視圖模板創(chuàng)建視圖使用視圖模板創(chuàng)建視圖（2）在出現(xiàn)的“模板資源管理器”選項(xiàng)中選擇“視圖”中的“創(chuàng)建視圖”選項(xiàng) 。（3）按照模板提示輸入視圖名稱，select語句后，執(zhí)行此語句，即可創(chuàng)建視圖。 11.1.2 通過視圖進(jìn)行的查詢、插入、通過視圖進(jìn)行的查詢、插入、修改、刪除數(shù)據(jù)修改、刪除數(shù)據(jù) 1.查詢視圖數(shù)據(jù)利用select語句或SQL Server管理平臺可以查看視圖的輸出數(shù)據(jù)。在SQL Server管理平臺中，右擊某個視圖的名稱，從彈出的快捷菜單中選擇“打開視圖”選項(xiàng)，就會顯示該視圖的輸出數(shù)據(jù)。還可以用SELECT查詢命令查看視圖數(shù)據(jù)。 11.1.2 通

9、過視圖進(jìn)行的查詢、插入、通過視圖進(jìn)行的查詢、插入、修改、刪除數(shù)據(jù)修改、刪除數(shù)據(jù)2.插入視圖數(shù)據(jù) 可使用INSERT插入語句向視圖中插入數(shù)據(jù)。例如向信息系的視圖插入數(shù)據(jù)。INSERT INTO IS_ student VALUES（090051,李力,22,信息系）再執(zhí)行：SELECT * FROM IS_ student 例如例如:首先創(chuàng)建一個包含限制條件的視圖首先創(chuàng)建一個包含限制條件的視圖v_student2，限制條，限制條件為年齡件為年齡20，然后插入了一條不滿足限制條件的記錄，再，然后插入了一條不滿足限制條件的記錄，再用用SELECT語句檢索視圖和表。程序清單如下：語句檢索視圖和表。程序

10、清單如下：CREATE VIEW v_student2ASSELECT * FROM studentWHERE sage20GOINSERT INTO v_ student2VALUES（090052,王則, 女，10 ,信息系）GOSELECT * FROM student GOSELECT * FROM v_ student 2GO11.1.2 通過視圖進(jìn)行的查詢、插入、通過視圖進(jìn)行的查詢、插入、修改、刪除數(shù)據(jù)修改、刪除數(shù)據(jù)3.使用視圖更新和刪除數(shù)據(jù) n使用視圖更新記錄，要注意的是，更新的只是數(shù)據(jù)庫中的基表。n使用視圖刪除記錄，可以刪除任何基表中的記錄，直接利用DELETE語句刪除記錄即可

11、。 【例如】創(chuàng)建了一個基于表student的視圖v_student2，然后通過該視圖修改表student中的記錄。 程序清單如下：UPDATE v_ student2SET name=張然WHERE name=張三SELECT * FROM student GOSELECT * FROM v_ student2GO【例11-7】利用視圖v_ student2刪除表student中姓名為“張三”的記錄。 程序清單如下：DELETE FROM v_ student2WHERE name=張然 SELECT * FROM student GOSELECT * FROM v_ student2GO我們

12、看到視圖v_ student2和表student的張然的記錄都做了刪除。11.1.2 通過視圖進(jìn)行的查詢、插入、通過視圖進(jìn)行的查詢、插入、修改、刪除數(shù)據(jù)修改、刪除數(shù)據(jù)n使用視圖修改數(shù)據(jù)時，需要注意：（1）不能同時修改兩個或者多個基表，可以對基于兩個或多個基表或者視圖的視圖進(jìn)行修改，但是每次修改都只能影響一個基表。（2）不能修改那些通過計(jì)算得到的字段。（3）如果在創(chuàng)建視圖時指定了WITH CHECK OPTION選項(xiàng)，那么使用視圖修改數(shù)據(jù)庫信息時，必須保證修改后的數(shù)據(jù)滿足視圖定義的范圍。（4）執(zhí)行UPDATE、DELETE命令時，所刪除與更新的數(shù)據(jù)必須包含在視圖的結(jié)果集中。（5）如果視圖引用多個

13、表時，無法用DELETE命令刪除數(shù)據(jù)，若使用UPDATE命令則應(yīng)與INSERT操作一樣，被更新的列必須屬于同一個表。 11.1.3 視圖的管理視圖的管理1.重命名視圖（1）在SQL Server管理平臺中，選擇要修改名稱的視圖，并右擊該視圖，從彈出的快捷菜單中選擇“重命名”選項(xiàng)?；蛘咴谝晥D上再次單擊，也可以修改視圖的名稱。接著該視圖的名稱變成可輸入狀態(tài)，可以直接輸入新的視圖名稱。（2）使用系統(tǒng)存儲過程sp_rename來修改視圖的名稱： sp_rename old_name,new_name11.1.3 視圖的管理視圖的管理（3）查看視圖信息 n查看視圖信息可以使用系統(tǒng)存儲過程sp_help來

14、顯示視圖特征n使用sp_helptext來顯示視圖在系統(tǒng)表中的定義n使用sp_depends來顯示該視圖所依賴的對象。 11.1.3 視圖的管理視圖的管理（4）修改視圖的結(jié)構(gòu) n在SQL Server管理平臺中，右擊要修改的視圖，從彈出的快捷菜單中選擇“修改”選項(xiàng)，出現(xiàn)視圖修改對話框。n使用ALTER VIEW語句修改視圖，但首先必須擁有使用視圖的權(quán)限，語法形式如下： ALTER VIEW view_name （column,.n） WITH ENCRYPTION AS select_statement WITH CHECK OPTION 11.2 存儲過程存儲過程 n11.2.1 存儲過程的

15、類型 n11.2.2 用戶存儲過程的創(chuàng)建與執(zhí)行 n11.2.3 用戶存儲過程的查看、修改和刪除 11.2 存儲過程存儲過程n存儲過程是 SQL 語句和控制流語句的預(yù)編譯集合，它以一個名稱存儲并作為一個單元處理，應(yīng)用程序可以通過調(diào)用的方法執(zhí)行存儲過程。n它使得對數(shù)據(jù)庫的管理和操作更加容易、效率更高。 11.2 存儲過程存儲過程n使用存儲過程有如下優(yōu)點(diǎn)：允許模塊化程序設(shè)計(jì) 改善性能 減少網(wǎng)絡(luò)流量 提供了安全機(jī)制 簡化管理和操作 11.2.1 存儲過程的類型存儲過程的類型共分為五類：系統(tǒng)存儲過程、用戶定義的存儲過程、擴(kuò)展存儲過程、臨時存儲過程和遠(yuǎn)程存儲過程。系統(tǒng)存儲過程：在安裝SQL Server

16、2005時，系統(tǒng)創(chuàng)建了許多系統(tǒng)存儲過程，這些系統(tǒng)存儲過程存儲在master和msdb數(shù)據(jù)庫中。以“sp_”為前綴。 用戶定義的存儲過程：是由用戶為完成某一特定功能而編寫的存儲過程，該存儲過程存儲在當(dāng)前的數(shù)據(jù)庫中。 11.2.1 存儲過程的類型存儲過程的類型擴(kuò)展存儲過程：是對動態(tài)鏈接庫（DLL）函數(shù)的調(diào)用，在SQL Server 2005環(huán)境外執(zhí)行，一般以“xp_”為前綴。臨時存儲過程：以“#”和“#”為前綴的過程，“#” 表示全局臨時存儲過程，它們存儲在tempdb數(shù)據(jù)庫中。遠(yuǎn)程存儲過程：是在遠(yuǎn)程服務(wù)器的數(shù)據(jù)庫中創(chuàng)建和存儲的過程。 11.2.2 用戶存儲過程的創(chuàng)建與執(zhí)行 n使用使用SQL Se

17、rver管理平臺創(chuàng)建存儲過程管理平臺創(chuàng)建存儲過程 （1）選中某個SQL Server服務(wù)器中的數(shù)據(jù)庫，這里選中school數(shù)據(jù)庫,展開可編程性節(jié)點(diǎn)。右鍵單擊數(shù)據(jù)庫下的“存儲過程”選項(xiàng)，彈出快捷菜單。 （2）在快捷菜單中選擇“新建存儲過程”命令如圖所示。 11.2.2 用戶存儲過程的創(chuàng)建與執(zhí)行 11.2.2 用戶存儲過程的創(chuàng)建與執(zhí)行（3）在“創(chuàng)建存儲過程模板”的文本框中輸入存儲過程名稱和程序語句。（4）單擊檢查語法按鈕，執(zhí)行語法正確性檢驗(yàn)。（5）單擊執(zhí)行按鈕，則在對象資源管理器窗口，可以看到所創(chuàng)建的存儲過程p_cj1。11.2.2 用戶存儲過程的創(chuàng)建與執(zhí)行n使用向?qū)?chuàng)建存儲過程使用向?qū)?chuàng)建存儲過

18、程 （1）選擇“視圖”菜單中的“模板資源管理器”菜單項(xiàng)，在系統(tǒng)屏幕的右側(cè)會彈出“模板資源管理器”窗口。（2）選中“存儲過程”選項(xiàng)中的“新建存儲過程” 11.2.2 用戶存儲過程的創(chuàng)建與執(zhí)行（3）出現(xiàn)創(chuàng)建存儲過程的模板。（4）按照模板提示輸入存儲過程名稱，以及存儲過程要執(zhí)行的語句后，點(diǎn)擊執(zhí)行命令按鈕，即可創(chuàng)建該存儲過程。 11.2.2 用戶存儲過程的創(chuàng)建與執(zhí)行n使用使用Transact-SQL語句創(chuàng)建和執(zhí)行存儲過程語句創(chuàng)建和執(zhí)行存儲過程 （1）創(chuàng)建存儲過程的語句格式：CREATE PROCEDURE 過程名 （， ） AS /*描述存儲過程的操作*/11.2.2 用戶存儲過程的創(chuàng)建與執(zhí)行（2）存

19、儲過程的執(zhí)行 在數(shù)據(jù)庫應(yīng)用程序中通過嵌入式SQL語句調(diào)用，不同的開發(fā)工具提供的調(diào)用形式不一樣； 通過DBMS（或第三方軟件）提供的數(shù)據(jù)庫管理工具調(diào)用，調(diào)用的語法格式取決于具體的管理工具。 11.2.2 用戶存儲過程的創(chuàng)建與執(zhí)行n執(zhí)行存儲過程的SQL語句的語法格式為： EXEC UTE 存儲過程名 實(shí)參 , OUTPUT , n n輸入?yún)?shù)的傳遞方式有兩種：（1）按位置傳遞：直接給出參數(shù)的值，實(shí)參與形參一一對應(yīng)（2）通過參數(shù)名傳遞：使用“參數(shù)名=參數(shù)值“的形式，參數(shù)可以任意順序給出。n注：SQL Server 2005采用EXECUTE 語句，可縮寫為EXEC。創(chuàng)建不帶參數(shù)的存儲過程n查詢計(jì)算機(jī)

20、系學(xué)生的考試成績，列出學(xué)生的姓名、課程名和成績。CREATE PROCEDURE student_grade1AS SELECT Sname, Cname,Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON o = o WHERE Sdept = 計(jì)算機(jī)系 創(chuàng)建帶輸入?yún)?shù)的存儲過程n查詢某個指定系學(xué)生的考試情況，列出學(xué)生的姓名、所在系、課程名和考試成績。CREATE PROCEDURE student_grade2 dept char(20)ASSELECT Sname, Sdept, Cnam

21、e, Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON o = o WHERE Sdept = dept 創(chuàng)建帶多個輸入?yún)?shù)的存儲過程n查詢某個學(xué)生某門課程的考試成績，列出學(xué)生的姓名、課程名和成績。CREATE PROCEDURE student_grade3 student_name char(10), course_name char(20) AS SELECT Sname, Cname, Grade FROM Student s JOIN sc ON s.sno = sc.sno INN

22、ER JOIN course c ON o = o WHERE sname = student_name AND cname = course_name 參數(shù)的傳遞方式n按參數(shù)位置傳遞值 EXEC student_grade3 劉晨, VB n按參數(shù)名傳遞值 EXEC Student_grade3 student_name = 劉晨, course_name=VB 創(chuàng)建帶多個輸入?yún)?shù)并有默認(rèn)值的存儲過程n查詢某個學(xué)生某門課程的考試成績，若沒有指定課程，則默認(rèn)課程為“數(shù)據(jù)庫基礎(chǔ)”。CREATE PROCEDURE student_grade4 student_name char(10), cou

23、rse_name char(20) = 數(shù)據(jù)庫基礎(chǔ)AS SELECT Sname, Cname, Grade FROM Student s JOIN sc ON s.sno = sc.sno JOIN course c ON o = o WHERE sname = student_name AND cname = course_name調(diào)用參數(shù)有默認(rèn)值的存儲過程EXEC student_grade4 吳賓n等價(jià)于執(zhí)行：EXEC student_grade4 吳賓, 數(shù)據(jù)庫基礎(chǔ)創(chuàng)建帶有多個輸入?yún)?shù)并均指定默認(rèn)值的存儲過程n查詢指定系、指定性別的學(xué)生中年齡大于等于指定年齡的學(xué)生的情況。系的默認(rèn)值為

24、“計(jì)算機(jī)系”，性別的默認(rèn)值為“男生”， 年齡的默認(rèn)值為20。CREATE PROC P_Student dept char(20) = 計(jì)算機(jī)系, sex char(2) = 男, age int = 20AS SELECT * FROM Student WHERE Sdept = dept AND Ssex = sex AND Sage = age執(zhí)行存儲過程n執(zhí)行1：不提供任何參數(shù)值。EXEC P_Studentn執(zhí)行2：提供全部參數(shù)值。EXEC P_Student 信息系, 女, 19n執(zhí)行3：只提供第二個參數(shù)的值。EXEC P_Student sex = 女n執(zhí)行4：只提供第一個和第三

25、個參數(shù)的值。EXEC P_Student sex=女 , age = 19創(chuàng)建帶有輸出參數(shù)的存儲過程n計(jì)算兩個數(shù)的和。CREATE PROCEDURE sum1var1 int, var2 int, var3 int outputAs Set var3 = var1 * var2 n執(zhí)行此存儲過程：Declare res intExecute sum1 5,7,res outputPrint res 創(chuàng)建帶輸入?yún)?shù)和一個輸出參數(shù)的存儲過程n統(tǒng)計(jì)指定課程的平均成績，并將統(tǒng)計(jì)的結(jié)果用輸出參數(shù)返回。CREATE PROCEDURE AvgGrade cn char(20), avg_grade in

26、t outputAS SELECT avg_grade = AVG(Grade) FROM SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = cn 創(chuàng)建帶輸入?yún)?shù)和多個輸出參數(shù)的存儲過程n統(tǒng)計(jì)指定課程的平均成績和選課人數(shù)，將統(tǒng)計(jì)的結(jié)果用輸出參數(shù)返回。CREATE PROCEDURE Avg_Count cn char(20), avg_grade int output, total int outputASSELECT avg_grade = AVG(Grade), total = COUNT(*)FROM SC JOIN Course C ON

27、 C.Cno = SC.CnoWHERE Cname = cn 創(chuàng)建刪除數(shù)據(jù)的存儲過程n刪除考試成績不及格學(xué)生的修課記錄。CREATE PROCEDURE p_DeleteSCASDELETE FROM sc WHERE grade 60創(chuàng)建修改數(shù)據(jù)的存儲過程n將指定課程的學(xué)分增加2分。CREATE PROCEDURE p_UpdateCredit cn varchar(20)AS UPDATE course SET credit=credit+2 WHERE cname = cn11.2.3 用戶存儲過程的查看、修改和刪除 1.查看用戶存儲過程（1）使用SQL Server管理平臺查看用戶創(chuàng)

28、建的存儲過程。展開指定的服務(wù)器和數(shù)據(jù)庫，選擇并依次展開“程序存儲過程”，然后右擊要查看的存儲過程名稱。 11.2.3 用戶存儲過程的查看、修改和刪除（2）使用系統(tǒng)存儲過程來查看用戶存儲過程 。n用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型，其語法為： sp_help objname= name n用于顯示存儲過程的源代碼，其語法為： sp_helptext objname= namen用于顯示和存儲過程相關(guān)的數(shù)據(jù)庫對象，其語法為： sp_depends objname=objectn用于返回當(dāng)前數(shù)據(jù)庫中的存儲過程列表，其語法為： sp_stored_proceduressp_name=name ,sp_

29、owner=owner ,sp_qualifier = qualifier 11.2.3 用戶存儲過程的查看、修改和刪除2.修改用戶存儲過程ALTER PROCEDURE 存儲過程名 形參 數(shù)據(jù)類型 =默認(rèn)值 output ，n AS SQL語句11.2.3 用戶存儲過程的查看、修改和刪除3.存儲過程的刪除 DROP PROCEDURE 數(shù)據(jù)庫名. 11.3 觸發(fā)器n11.3.1 創(chuàng)建和使用DML觸發(fā)器 n11.3.2 創(chuàng)建和使用DDL觸發(fā)器 n11.3.3 觸發(fā)器的管理 n11.3.4 觸發(fā)器實(shí)例 11.3 觸發(fā)器n觸發(fā)器是一種特殊的存儲過程，它在執(zhí)行操作事件時自動觸發(fā)執(zhí)行。n觸發(fā)器與存儲過

30、程的區(qū)別：（1）觸發(fā)器是自動執(zhí)行的，而存儲過程需要顯示調(diào)用才能執(zhí)行。（2）觸發(fā)器是建立在表或視圖之上的，而存儲過程是建立在數(shù)據(jù)庫之上的。刪除表就刪除了該表之上的所有觸發(fā)器，與該表有關(guān)的存儲過程仍然存在，只有刪除數(shù)據(jù)庫才能刪除該數(shù)據(jù)庫上的所有存儲過程。11.3 觸發(fā)器nSQL Server2005 包括兩大類觸發(fā)器 :（1）DML 觸發(fā)器在數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言 (DML) 事件時將啟用。DML 事件包括在指定表或視圖中修改數(shù)據(jù)的 INSERT 語句、UPDATE 語句或 DELETE 語句。 （2）DDL 觸發(fā)器是 SQL Server 2005 的新增功能。當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語

31、言 (DDL) 事件時將調(diào)用這些觸發(fā)器。 11.3.1 創(chuàng)建和使用DML觸發(fā)器 nDML 觸發(fā)器的主要優(yōu)點(diǎn)如下：（1）DML 觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級聯(lián)更改。（2）DML 觸發(fā)器可以防止惡意或錯誤的 INSERT、UPDATE 以及 DELETE 操作，并強(qiáng)制執(zhí)行比 CHECK 約束定義的限制更為復(fù)雜的其他限制。與 CHECK 約束不同，DML 觸發(fā)器可以引用其他表中的列。（3）DML 觸發(fā)器可以評估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施。 11.3.1 創(chuàng)建和使用DML觸發(fā)器當(dāng)創(chuàng)建一個DML觸發(fā)器時必須指定如下選項(xiàng)：名稱；在其上定義觸發(fā)器的表；觸發(fā)器將何時激發(fā)；激活觸發(fā)器的數(shù)據(jù)

32、修改語句，有效選項(xiàng)為 INSERT、UPDATE 或 DELETE，多個數(shù)據(jù)修改語句可激活同一個觸發(fā)器；執(zhí)行觸發(fā)操作的編程語句。 11.3.1 創(chuàng)建和使用DML觸發(fā)器nDML 觸發(fā)器所使用的 deleted 和 inserted 邏輯表 :Deleted表用于存儲delete，update語句所影響的行的副本。在執(zhí)行delete或update語句時，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。Inserted表用于存儲Insert或update語句所影響的行的副本，在一個插入或更新事務(wù)處理中，新建的行被同時添加到Inserted表和觸發(fā)器表中。Inserted表中的行是觸發(fā)器表中新行的副本

33、。 11.3.1 創(chuàng)建和使用DML觸發(fā)器nDML 觸發(fā)器的類型：insert觸發(fā)器：在表或視圖執(zhí)行插入記錄操作時觸發(fā)。update觸發(fā)器：在表或視圖執(zhí)行修改記錄操作時觸發(fā)。delete觸發(fā)器：在表或視圖執(zhí)行刪除記錄操作時觸發(fā)。DML 觸發(fā)器的創(chuàng)建 （1）使用）使用SQL Server管理平臺創(chuàng)建觸發(fā)器管理平臺創(chuàng)建觸發(fā)器 在SQL Server管理平臺中，展開指定的服務(wù)器和數(shù)據(jù)庫項(xiàng)，然后展開表，選擇并展開要在其上創(chuàng)建觸發(fā)器的表，右擊觸發(fā)器選項(xiàng)，從彈出的快捷菜單中選擇“新建觸發(fā)器”選項(xiàng)，則會出現(xiàn)觸發(fā)器創(chuàng)建編輯窗口。用戶可參照模板在其中輸入創(chuàng)建觸發(fā)器的T-SQL語句。最后，單擊“執(zhí)行”按鈕，即可成功

34、創(chuàng)建觸發(fā)器。 DML 觸發(fā)器的創(chuàng)建（2）使用）使用CREATE TRIGGER命令創(chuàng)建命令創(chuàng)建DML觸發(fā)器觸發(fā)器 CREATE TRIGGER schema_name.觸發(fā)器名 ON 表名|視圖名 WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION AS SQL 語句 .n DML 觸發(fā)器的創(chuàng)建例如：對school數(shù)據(jù)庫的sc表建立觸發(fā)器。說明inserted和deleted表的作用。CREATE TRIGGER tr1ON scFOR INSERT, UPD

35、ATE, DELETE AS PRINT inserted表：Select * from insertedPRINT deleted表：Select * from deletedGo當(dāng)執(zhí)行插入操作：insert into sc values(0912103,c03,78,必修) DML 觸發(fā)器的創(chuàng)建n例如：在school數(shù)據(jù)庫的student表上創(chuàng)建一個名為tr_delete_stu的觸發(fā)器，當(dāng)要刪除指定學(xué)號的行時，激發(fā)該觸發(fā)器，撤消刪除操作，并給出提示信息“不能刪除student表中的信息!”。 程序清單如下：CREATE TRIGGER tr_delete_stu ON student A

36、FTER DELETEAS ROLLBACK TRANSACTION PRINT 不能刪除student表中的信息!GO當(dāng)執(zhí)行：DELETE student WHERE sno= 0912101 時，系統(tǒng)將提示 “不能刪除student表中的信息!” 的信息。DML 觸發(fā)器的創(chuàng)建例如：在student表上創(chuàng)建一個觸發(fā)器。當(dāng)更新了某位學(xué)生的學(xué)號信息時，就激活觸發(fā)器級聯(lián)更新sc表中相關(guān)成績記錄中的學(xué)號信息，并使用print語句返回一個提示信息。解決的方法有：創(chuàng)建外鍵約束(不允許修改，或者允許級聯(lián)更新)，用觸發(fā)器實(shí)現(xiàn)自動級聯(lián)修改。 DML 觸發(fā)器的創(chuàng)建CREATE TRIGGER tr_update

37、_stu1 ON STUDENT AFTER UPDATEASDECLARE 原學(xué)號 char(6), 新學(xué)號 char(6)SELECT 原學(xué)號=deleted.sno, 新學(xué)號= inserted.snoFROM DELETED, INSERTED WHERE deleted.sname = inserted.snamePRINT 準(zhǔn)備級聯(lián)更新sc表中的學(xué)號信息.UPDATE sc SET sno=新學(xué)號 WHERE sno=原學(xué)號 PRINT 已經(jīng)級聯(lián)更新了sc表中原學(xué)號為+ 原學(xué)號 +的成績信息。n建立了該觸發(fā)器后當(dāng)執(zhí)行：UPDATE student SET sno=0912111 w

38、here sno=0912101操作后，會看到 sc表中學(xué)號為0912101的記錄也都改為091211111.3.2 創(chuàng)建和使用DDL觸發(fā)器 nDDL觸發(fā)器一般用于以下目的：防止對數(shù)據(jù)庫結(jié)構(gòu)進(jìn)行某些更改。希望數(shù)據(jù)庫中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫結(jié)構(gòu)中的更改。要記錄數(shù)據(jù)庫結(jié)構(gòu)中的更改或事件。11.3.2 創(chuàng)建和使用DDL觸發(fā)器1.創(chuàng)建創(chuàng)建DDL觸發(fā)器觸發(fā)器 CREATE TRIGGER 觸發(fā)器名稱ON ALL SERVER|DATABASE FOR|AFTER event_type|event_group,nAS sql_statement11.3.2 創(chuàng)建和使用DDL觸發(fā)器2.DDL觸發(fā)器的應(yīng)用觸

39、發(fā)器的應(yīng)用n在響應(yīng)當(dāng)前數(shù)據(jù)庫或服務(wù)器中處理的T-SQL事件時，可以激發(fā)DDL觸發(fā)器。例如：在SCHOOL數(shù)據(jù)庫中創(chuàng)建一個DDL觸發(fā)器safe，用來防止該數(shù)據(jù)庫中的任一表被修改或刪除。11.3.2 創(chuàng)建和使用DDL觸發(fā)器程序清單如下：USE SCHOOLGOCREATE TRIGGER safeON DATABASEAFTER DROP_TABLE,ALTER_TABLEASBEGIN RAISERROR(不能修改表結(jié)構(gòu),16,2) ROLLBACKENDGOn當(dāng)執(zhí)行以下程序：USE SCHOOLGOALTER TABLE student ADD nation char(10)GO11.3.3觸

40、發(fā)器的管理 1.使用使用SQL Server管理平臺修改、禁用和刪除觸發(fā)管理平臺修改、禁用和刪除觸發(fā)器器 （1）啟動SQL Server Management Studio,在“對象資源管理器”中依次展開數(shù)據(jù)庫表節(jié)點(diǎn)觸發(fā)器（2）擊鼠標(biāo)右鍵出現(xiàn)快捷菜單。在圖中選擇【修改】可以完成觸發(fā)器的修改操作。 （3）在圖中選擇【刪除】可以刪除指定的觸發(fā)器。 （4）在“禁用觸發(fā)器”對話框中，單擊【關(guān)閉】按鈕，即可禁用選定的觸發(fā)器，被禁用的觸發(fā)器的圖標(biāo)將變成灰色。 2. 使用系統(tǒng)存儲過程來查看用戶創(chuàng)建使用系統(tǒng)存儲過程來查看用戶創(chuàng)建的觸發(fā)器的觸發(fā)器 （1）sp_help。用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)建時間。其語法格式為：sp_help 觸發(fā)器名（2）sp_helptext。用于顯示觸發(fā)器的正文信息，其語法為： sp_helptext 觸發(fā)器名（3）sp_depends。用于顯示和觸發(fā)器相關(guān)的數(shù)據(jù)庫對象，其語法為 sp_depends 觸發(fā)器名3.利用利用T-SQL語句修改和刪除觸發(fā)器語句修改和刪除觸發(fā)器 （1）觸發(fā)器的修改 ALTER TRIGGER 觸發(fā)器名稱On 表名FOR | AFTER|INSERT OFINSERT,DELETE,UPDATEAS