存儲過程和觸器PPT課件

1、Principles and Applications of Database System 1 第四章第四章 存儲過程和觸發(fā)器存儲過程和觸發(fā)器 Principles and Applications of Database System 2 第第4章章 存儲過程和觸發(fā)器存儲過程和觸發(fā)器 4.1 4.1 存儲過程存儲過程 4.2 4.2 觸發(fā)器觸發(fā)器 Principles and Applications of Database System 3 4.1 4.1 存儲過程存儲過程 4.1.1 4.1.1 存儲過程存儲過程基本概念基本概念 4.1.2 4.1.2 創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行存儲

2、過程 4.1.3 4.1.3 存儲過程的修改和刪除存儲過程的修改和刪除 Principles and Applications of Database System 4 4.1.1 4.1.1 存儲過程存儲過程基本概念基本概念 存儲過程是指經(jīng)過預(yù)先編譯的存儲過程是指經(jīng)過預(yù)先編譯的SQLSQL語句語句 的集合，可以以一種可執(zhí)行的形式永久的集合，可以以一種可執(zhí)行的形式永久 地存儲在數(shù)據(jù)庫中。地存儲在數(shù)據(jù)庫中。 需要時只需調(diào)用該過程就可以完成相應(yīng)需要時只需調(diào)用該過程就可以完成相應(yīng) 的操作。的操作。 存儲過程類似于程序設(shè)計語言中的過程存儲過程類似于程序設(shè)計語言中的過程 和函數(shù)。和函數(shù)。 Princip

3、les and Applications of Database System 5 4.1.1 4.1.1 存儲過程存儲過程基本概念基本概念( (續(xù)續(xù)) ) 存儲過程具有以下優(yōu)點。存儲過程具有以下優(yōu)點。 （1 1）運行速度快）運行速度快 （2 2）模塊化編程，增強代碼的重用）模塊化編程，增強代碼的重用 性和共享性性和共享性 （3 3）減少網(wǎng)絡(luò)通信量）減少網(wǎng)絡(luò)通信量 （4 4）保證系統(tǒng)的安全性）保證系統(tǒng)的安全性 Principles and Applications of Database System 6 4.1.1 4.1.1 存儲過程存儲過程基本概念基本概念( (續(xù)續(xù)) ) 在在SQL S

4、erverSQL Server中，存儲過程有兩種類型：中，存儲過程有兩種類型： 系統(tǒng)存儲過程系統(tǒng)存儲過程 用戶定義的存儲過程用戶定義的存儲過程 系統(tǒng)存儲過程系統(tǒng)存儲過程存放在存放在mastermaster數(shù)據(jù)庫中并以數(shù)據(jù)庫中并以sp_sp_ 為前綴，主要是從系統(tǒng)表中獲取信息，為系為前綴，主要是從系統(tǒng)表中獲取信息，為系 統(tǒng)管理員管理統(tǒng)管理員管理SQL ServerSQL Server提供支持。提供支持。 用戶自定義存儲過程用戶自定義存儲過程是由用戶創(chuàng)建并能完成是由用戶創(chuàng)建并能完成 某一特定功能的存儲過程。對于用戶創(chuàng)建的某一特定功能的存儲過程。對于用戶創(chuàng)建的 存儲過程，盡量存儲過程，盡量不要以不要

5、以sp_sp_作為其名稱的前綴。作為其名稱的前綴。 Principles and Applications of Database System 7 4.1.2 4.1.2 創(chuàng)建和執(zhí)行存儲過程創(chuàng)建和執(zhí)行存儲過程 一、創(chuàng)建存儲過程一、創(chuàng)建存儲過程 T-SQLT-SQL語句格式：語句格式： CREATE PROCEDURE procedure_nameCREATE PROCEDURE procedure_name parameter data_type =default parameter data_type =default OUTPUT ,OUTPUT , WITH ENCRYPTION WI

6、TH ENCRYPTION AS sql_statement AS sql_statement； procedure_nameprocedure_name：存儲過程名存儲過程名 parameterparameter：存儲過程中定義的輸入存儲過程中定義的輸入/ /輸出參數(shù)輸出參數(shù) data_typedata_type：參數(shù)的數(shù)據(jù)類型參數(shù)的數(shù)據(jù)類型 defaultdefault：定義參數(shù)的默認值定義參數(shù)的默認值 OUTPUTOUTPUT：輸出參數(shù)輸出參數(shù) WITH ENCRYPTIONWITH ENCRYPTION：加密存儲過程加密存儲過程 sql_statementsql_statement：存

7、儲過程中要執(zhí)行的存儲過程中要執(zhí)行的T-SQLT-SQL語句語句 Principles and Applications of Database System 8 一、創(chuàng)建存儲過程一、創(chuàng)建存儲過程( (續(xù)續(xù)) ) 創(chuàng)建存儲過程時，需要創(chuàng)建存儲過程時，需要注意以下幾點注意以下幾點： （1 1）CREATE PROCEDURECREATE PROCEDURE不能與其他的不能與其他的T-SQLT-SQL語句語句位于位于 同一個批處理中。同一個批處理中。 （2 2）創(chuàng)建存儲過程的創(chuàng)建存儲過程的權(quán)限權(quán)限默認屬于數(shù)據(jù)庫擁有者。默認屬于數(shù)據(jù)庫擁有者。 （3 3）只能在只能在當前數(shù)據(jù)庫中創(chuàng)建當前數(shù)據(jù)庫中創(chuàng)建存儲

8、過程。存儲過程。 （4 4）成功執(zhí)行成功執(zhí)行CREATE PROCEDURECREATE PROCEDURE語句后，存儲過程語句后，存儲過程 名稱名稱將存儲在將存儲在sysobjectssysobjects系統(tǒng)表中，而系統(tǒng)表中，而CREATE CREATE PROCEDUREPROCEDURE語句的語句的文本文本將存儲在將存儲在syscommentssyscomments中。中。 （5 5）存儲過程可以存儲過程可以嵌套調(diào)用嵌套調(diào)用。 （6 6）存儲過程中存儲過程中不允許不允許使用創(chuàng)建數(shù)據(jù)庫對象的語句。使用創(chuàng)建數(shù)據(jù)庫對象的語句。 Principles and Applications of Da

9、tabase System 9 一、創(chuàng)建存儲過程一、創(chuàng)建存儲過程( (續(xù)續(xù)) ) 例例4.1 4.1 在學生在學生- -課程數(shù)據(jù)庫中創(chuàng)建一個存儲過程，查課程數(shù)據(jù)庫中創(chuàng)建一個存儲過程，查 看看“張明張明”同學的有關(guān)基本信息和選修課程的情況。同學的有關(guān)基本信息和選修課程的情況。 CREATE CREATE PROCEDURE student_coursePROCEDURE student_course AS AS SELECT Student.Sno, Sname, Cname, Grade SELECT Student.Sno, Sname, Cname, Grade FROM Student,

10、 Course, SC FROM Student, Course, SC WHERE Student.Sno = SC.Sno AND WHERE Student.Sno = SC.Sno AND Course.Cno=SC.Cno AND Sname= Course.Cno=SC.Cno AND Sname= 張明張明; 為了為了避免出現(xiàn)重復(fù)避免出現(xiàn)重復(fù)的存儲過程名稱而無法建立的存儲過程名稱而無法建立 該存儲過程，可以先使用以下語句：該存儲過程，可以先使用以下語句： IF EXISTSIF EXISTS（SELECT SELECT namename FROM FROM sysobjectss

11、ysobjects WHERE WHERE name= student_coursename= student_course AND AND type=Ptype=P) ) DROP PROCEDUREDROP PROCEDURE student_course student_course； Principles and Applications of Database System 10 一、創(chuàng)建存儲過程一、創(chuàng)建存儲過程( (續(xù)續(xù)) ) 系統(tǒng)存儲過程系統(tǒng)存儲過程sp_helptextsp_helptext： 查看存儲過程中的查看存儲過程中的SQLSQL語句語句 系統(tǒng)存儲過程系統(tǒng)存儲過程sp_

12、dependssp_depends： 查看存儲過程使用的對象以及調(diào)用該存儲查看存儲過程使用的對象以及調(diào)用該存儲 過程的其他存儲過程的名稱。過程的其他存儲過程的名稱。 Principles and Applications of Database System 11 二、執(zhí)行存儲過程二、執(zhí)行存儲過程 EXECUTEEXECUTE語句格式如下：語句格式如下： EXECUTE EXECUTE return_status=return_status= parameter = value |parameter = value |variable OUTPUT variable OUTPUT , .n ;

13、, .n ; - - return_statusreturn_status：是一個可選的是一個可選的整型變量整型變量，保存存儲過程的，保存存儲過程的 返回狀態(tài)返回狀態(tài)。這個變量在。這個變量在EXECUTEEXECUTE語句之前，必須聲明過。語句之前，必須聲明過。 - - procedure_nameprocedure_name：被調(diào)用執(zhí)行的存儲過程名。被調(diào)用執(zhí)行的存儲過程名。 - - parameterparameter：是是CREATE PROCCREATE PROC語句中定義的存儲過程參數(shù)。在語句中定義的存儲過程參數(shù)。在 以以parameter_nameparameter_name = =

14、 valuevalue格式使用時，參數(shù)名稱和常量格式使用時，參數(shù)名稱和常量 不一定按照不一定按照CREATE PROCCREATE PROC語句中定義的順序出現(xiàn)，但是若有語句中定義的順序出現(xiàn)，但是若有 一個參數(shù)使用一個參數(shù)使用parameter_name = valueparameter_name = value格式，則其他所有格式，則其他所有 參數(shù)都必須使用這種格式。參數(shù)都必須使用這種格式。 - - variablevariable：保存輸入?yún)?shù)或者輸出參數(shù)值的變量。保存輸入?yún)?shù)或者輸出參數(shù)值的變量。 - - OUTPUTOUTPUT：輸出參數(shù)。存儲過程的匹配參數(shù)也須指定關(guān)鍵字輸出參數(shù)。存儲

15、過程的匹配參數(shù)也須指定關(guān)鍵字OUTPUTOUTPUT。 Principles and Applications of Database System 12 二、執(zhí)行存儲過程二、執(zhí)行存儲過程( (續(xù)續(xù)) ) 例例4.24.2 調(diào)用例調(diào)用例4.14.1定義的存儲過程的方法定義的存儲過程的方法 為為 EXECUTE student_course;EXECUTE student_course; 即可查詢即可查詢“張明張明”同學的相關(guān)信息。同學的相關(guān)信息。 Principles and Applications of Database System 13 三、三、通過存儲過程傳遞參數(shù)通過存儲過程傳遞參數(shù)

16、 參數(shù)：參數(shù)：輸入?yún)?shù)輸入?yún)?shù)和和輸出參數(shù)輸出參數(shù) 輸入?yún)?shù)輸入?yún)?shù)：當程序執(zhí)行存儲過程時，可以通過：當程序執(zhí)行存儲過程時，可以通過 輸入?yún)?shù)向該存儲過程傳遞值，這些值可作輸入?yún)?shù)向該存儲過程傳遞值，這些值可作 為為SQLSQL編程語言中的標準變量使用編程語言中的標準變量使用 輸出參數(shù)輸出參數(shù)：存儲過程也可以通過：存儲過程也可以通過OUTPUTOUTPUT參數(shù)將參數(shù)將 值返回至調(diào)用程序。值返回至調(diào)用程序。 一個存儲過程可以定義多達一個存儲過程可以定義多達1 1 024024個參數(shù)，個參數(shù)， 每個參數(shù)必須指定每個參數(shù)必須指定參數(shù)名參數(shù)名和和數(shù)據(jù)類型數(shù)據(jù)類型，并且，并且 參數(shù)名必須參數(shù)名必須以以“

17、”符號作為前綴符號作為前綴，也可以，也可以 為參數(shù)指定默認值為參數(shù)指定默認值。 Principles and Applications of Database System 14 三、三、通過存儲過程傳遞參數(shù)通過存儲過程傳遞參數(shù)(續(xù)續(xù)) （1 1）帶輸入?yún)?shù)的存儲過程）帶輸入?yún)?shù)的存儲過程 例例4.34.3 可以查詢?nèi)我庖粋€學生的選修信息?？蓪W可以查詢?nèi)我庖粋€學生的選修信息?？蓪W 生姓名作為輸入?yún)?shù)把值傳遞給存儲過程。生姓名作為輸入?yún)?shù)把值傳遞給存儲過程。 CREATE PROCEDURE student_course1 CREATE PROCEDURE student_course1 S

18、tudentName VARCHAR(10)StudentName VARCHAR(10) ASAS SELECT Student.Sno, Sname, Cname, Grade SELECT Student.Sno, Sname, Cname, Grade FROM Student, Course, SC FROM Student, Course, SC WHERE Student.Sno=SC.Sno AND WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Sname=Course.Cno=SC.Cno AND Sname=Stude

19、ntNameStudentName； Principles and Applications of Database System 15 三、三、通過存儲過程傳遞參數(shù)通過存儲過程傳遞參數(shù)(續(xù)續(xù)) 調(diào)用該存儲過程的方法為調(diào)用該存儲過程的方法為 EXECUTE student_course1 EXECUTE student_course1 張明張明 ； 或或 EXECUTE student_course1EXECUTE student_course1 StudentName StudentName = = 張明張明 ； Principles and Applications of Database

20、 System 16 三、三、通過存儲過程傳遞參數(shù)通過存儲過程傳遞參數(shù)(續(xù)續(xù)) 例例4.44.4 創(chuàng)建一個存儲過程用于向?qū)W生表中插入記錄。創(chuàng)建一個存儲過程用于向?qū)W生表中插入記錄。 CREATE PROCEDURE student_insert xh CHAR(6), name VARCHAR(8), sex CHAR(2), age INT, sdep CHAR(10) AS INSERT INTO Student VALUES (xh,name,sex, age,sdep); 該存儲過程通過參數(shù)將學生的有關(guān)情況傳遞給存儲過該存儲過程通過參數(shù)將學生的有關(guān)情況傳遞給存儲過 程。調(diào)用該存儲過程的方

21、法為程。調(diào)用該存儲過程的方法為 EXECUTE PROCEDURE student_insert 950002, 李明李明, 男男, 20, CS ； Principles and Applications of Database System 17 三、三、通過存儲過程傳遞參數(shù)通過存儲過程傳遞參數(shù)(續(xù)續(xù)) （2 2）使用默認參數(shù)值）使用默認參數(shù)值 例例4.54.5 創(chuàng)建一個存儲過程，若沒有給出學生姓名，創(chuàng)建一個存儲過程，若沒有給出學生姓名， 則返回所有學生的有關(guān)情況。則返回所有學生的有關(guān)情況。 CREATE PROCEDURE student_course2 StudentName VARC

22、HAR(10) = NULL AS IF StudentName IS NULL BEGIN SELECT Student.Sno,Sname,Cname,Grade FROM Student, Course, SC WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno END ELSE Principles and Applications of Database System 18 三、三、通過存儲過程傳遞參數(shù)通過存儲過程傳遞參數(shù)(續(xù)續(xù)) (續(xù)上頁續(xù)上頁) ELSE BEGIN SELECT Student.Sno, Sname, Cname, Gr

23、ade FROM Student, Course, SC WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Sname=StudentName END Principles and Applications of Database System 19 三、三、通過存儲過程傳遞參數(shù)通過存儲過程傳遞參數(shù)(續(xù)續(xù)) （2 2）使用輸出參數(shù)）使用輸出參數(shù) 例例4.64.6 創(chuàng)建一個存儲過程，返回選修某門課程的學生人數(shù)。創(chuàng)建一個存儲過程，返回選修某門課程的學生人數(shù)。 CREATE PROCEDURE student_count CourseName VA

24、RCHAR(20),StudentSum INT OUTPUTINT OUTPUT AS SELECT StudentSum = COUNT(*) FROM Coures,SC WHERE Course.Cno=SC.Cno AND Cname=CourseName； 調(diào)用該存儲過程的例子：調(diào)用該存儲過程的例子： DECLARE studentNum INT; EXECUTE student_count 數(shù)據(jù)庫原理及應(yīng)用數(shù)據(jù)庫原理及應(yīng)用 , studnetNum OUTPUT; SELECT The result is：, studentNum; Principles and Applicat

25、ions of Database System 20 三、三、通過存儲過程傳遞參數(shù)通過存儲過程傳遞參數(shù)(續(xù)續(xù)) 例例4.7 4.7 創(chuàng)建一個存儲過程，輸出學生的基本情況。創(chuàng)建一個存儲過程，輸出學生的基本情況。 CREATE PROCEDURE student_query xh CHAR(6), name VARCHAR(8) OUTPUT, sex CHAR(2) OUTPUT,age INT OUTPUT AS Select name=Sname,sex=Ssex,age=Sage FROM Student WHERE Sno=xh; 調(diào)用該存儲過程，查詢學號為調(diào)用該存儲過程，查詢學號為“9

26、50001”950001”同學的相關(guān)同學的相關(guān) 信息信息 DECLARE name VARCHAR(8); DECLARE sex CHAR(2); DECLARE age INT; EXECUTE student_query 950001, name OUTPUT, sex OUTPUT, age OUTPUT; Principles and Applications of Database System 21 4.1.3 4.1.3 存儲過程的修改和刪除存儲過程的修改和刪除 一、修改存儲過程一、修改存儲過程 T-SQLT-SQL語句格式：語句格式： ALTER PROCEDURE proc

27、edure_nameALTER PROCEDURE procedure_name parameter data_type =default parameter data_type =default OUTPUT ,OUTPUT , WITH ENCRYPTION WITH ENCRYPTION AS sql_statement AS sql_statement； 二、刪除存儲過程二、刪除存儲過程 T-SQLT-SQL語句格式：語句格式： DROP PROCEDURE procedure_name;DROP PROCEDURE procedure_name; Principles and App

28、lications of Database System 22 4.2 4.2 觸發(fā)器觸發(fā)器 4.2.1 4.2.1 觸發(fā)器觸發(fā)器基本概念基本概念 4.2.2 4.2.2 定義定義觸發(fā)器觸發(fā)器 4.2.3 4.2.3 激活激活觸發(fā)器觸發(fā)器 4.2.3 4.2.3 刪除刪除觸發(fā)器觸發(fā)器 Principles and Applications of Database System 23 4.2 4.2 觸發(fā)器觸發(fā)器 觸發(fā)器（觸發(fā)器（TriggerTrigger）是由是由用戶定義在關(guān)系表上用戶定義在關(guān)系表上的的 一類一類特殊過程特殊過程，該過程一經(jīng)定義，任何用戶對，該過程一經(jīng)定義，任何用戶對 表的增

29、加、刪除、更新操作均由服務(wù)器表的增加、刪除、更新操作均由服務(wù)器自動激自動激 活活相應(yīng)的觸發(fā)器，在相應(yīng)的觸發(fā)器，在DBMSDBMS核心層進行核心層進行集中的完集中的完 整性控制整性控制。觸發(fā)器類似于約束，但是比約束更。觸發(fā)器類似于約束，但是比約束更 加靈活，可以實施比約束更加復(fù)雜的檢查和操加靈活，可以實施比約束更加復(fù)雜的檢查和操 作，具有更強大的數(shù)據(jù)控制能力。作，具有更強大的數(shù)據(jù)控制能力。 Principles and Applications of Database System 24 4.2.1 4.2.1 觸發(fā)器觸發(fā)器基本概念基本概念 觸發(fā)器的定義觸發(fā)器的定義：滿足某個特定條件時自動：滿足

30、某個特定條件時自動 觸發(fā)執(zhí)行的特殊存儲過程，用于保證表中觸發(fā)執(zhí)行的特殊存儲過程，用于保證表中 的數(shù)據(jù)遵循數(shù)據(jù)庫設(shè)計者確定的規(guī)則和約的數(shù)據(jù)遵循數(shù)據(jù)庫設(shè)計者確定的規(guī)則和約 束。束。 存儲過程存儲過程是通過名稱而被是通過名稱而被顯式調(diào)用顯式調(diào)用執(zhí)行，執(zhí)行， 而而觸發(fā)器觸發(fā)器不能被顯式調(diào)用，只能不能被顯式調(diào)用，只能通過事件通過事件 觸發(fā)而執(zhí)行觸發(fā)而執(zhí)行 Principles and Applications of Database System 25 4.2.1 4.2.1 觸發(fā)器觸發(fā)器基本概念基本概念( (續(xù)續(xù)) ) 觸發(fā)器常用于強制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性，常常觸發(fā)器常用于強制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性，常

31、常 用于下列幾種情況：用于下列幾種情況： （1 1）觸發(fā)器可以通過觸發(fā)器可以通過級聯(lián)級聯(lián)的方式對相關(guān)的表進的方式對相關(guān)的表進 行修改。行修改。 （2 2）觸發(fā)器可以實施比觸發(fā)器可以實施比CHECKCHECK約束約束更復(fù)雜的約束更復(fù)雜的約束。 （3 3）觸發(fā)器可以根據(jù)觸發(fā)器可以根據(jù)改變前后表中數(shù)據(jù)改變前后表中數(shù)據(jù)的不同的不同 來進行相應(yīng)的操作。來進行相應(yīng)的操作。 （4 4）對于一個表上的不同操作可以采用不同的對于一個表上的不同操作可以采用不同的 觸發(fā)器，即使是對相同的語句也可以調(diào)用不同觸發(fā)器，即使是對相同的語句也可以調(diào)用不同 的觸發(fā)器完成不同的操作。的觸發(fā)器完成不同的操作。 Principles

32、 and Applications of Database System 26 4.2.1 4.2.1 觸發(fā)器觸發(fā)器基本概念基本概念( (續(xù)續(xù)) ) SQL ServerSQL Server中提供兩種觸發(fā)：中提供兩種觸發(fā)： AFTER AFTER 觸發(fā)器觸發(fā)器 INSTEAD OF INSTEAD OF 觸發(fā)器觸發(fā)器 一個觸發(fā)器與一個觸發(fā)器與3 3部分的內(nèi)容有關(guān)：部分的內(nèi)容有關(guān)： 定義觸發(fā)器的定義觸發(fā)器的表表 激活觸發(fā)器的激活觸發(fā)器的操作語句操作語句 觸發(fā)器被激活時觸發(fā)器被激活時進行的操作進行的操作 Principles and Applications of Database System

33、27 4.2.1 4.2.1 觸發(fā)器觸發(fā)器基本概念基本概念( (續(xù)續(xù)) ) 在使用觸發(fā)器的過程中，在使用觸發(fā)器的過程中，SQL ServerSQL Server用到了兩個用到了兩個 特殊的臨時表：特殊的臨時表：insertedinserted表表和和deleteddeleted表表。它。它 們與創(chuàng)建觸發(fā)器的表具有們與創(chuàng)建觸發(fā)器的表具有相同的結(jié)構(gòu)相同的結(jié)構(gòu)。觸發(fā)器。觸發(fā)器 執(zhí)行完畢后，這兩個表執(zhí)行完畢后，這兩個表自動被刪除自動被刪除。 insertedinserted表表： 該表中存放的是由于執(zhí)行該表中存放的是由于執(zhí)行INSERTINSERT或或UPDATEUPDATE語句語句 而要向表中插入的

34、新數(shù)據(jù)行。而要向表中插入的新數(shù)據(jù)行。 deleteddeleted表表： 該表中存放的是由于執(zhí)行該表中存放的是由于執(zhí)行DELETEDELETE或或UPDATEUPDATE語句語句 而要從表中刪除的數(shù)據(jù)行。而要從表中刪除的數(shù)據(jù)行。 Principles and Applications of Database System 28 4.2.2 4.2.2 定義定義觸發(fā)器觸發(fā)器 創(chuàng)建創(chuàng)建觸發(fā)器的語句格式為：觸發(fā)器的語句格式為： CREATE TRIGGER trigger_name ON table_name | view_name WITH ENCRYPTION FOR | AFTER | INS

35、TEAD OF INSERT , UPDATE , DELETE AS sql_statement Principles and Applications of Database System 29 4.2.2 4.2.2 定義定義觸發(fā)器觸發(fā)器(續(xù)續(xù)) 例例4.84.8 創(chuàng)建一個觸發(fā)器，當學生表中的記創(chuàng)建一個觸發(fā)器，當學生表中的記 錄被更新時，顯示表中的所有記錄。錄被更新時，顯示表中的所有記錄。 CREATE TRIGGER student_change ON Student AFTER INSERT, UPDATE, DELETE AS SELECT * FROM Student; Prin

36、ciples and Applications of Database System 30 4.2.2 4.2.2 定義定義觸發(fā)器觸發(fā)器(續(xù)續(xù)) 例例4.94.9 在在StudentStudent表上創(chuàng)建表上創(chuàng)建DELETEDELETE觸發(fā)器，觸發(fā)器， 實現(xiàn)實現(xiàn)StudentStudent表和表和SCSC表的表的級聯(lián)刪除級聯(lián)刪除。 CREATE TRIGGER studentDelete ON Student AFTER DELETE AS DELETE FROM SC WHERE Sno IN (SELECT deleted.Sno FROM deleted); Principles and

37、 Applications of Database System 31 4.2.2 4.2.2 定義定義觸發(fā)器觸發(fā)器(續(xù)續(xù)) 例例4.104.10 在在SCSC表上創(chuàng)建表上創(chuàng)建INSERTINSERT觸發(fā)器，當向觸發(fā)器，當向SCSC表表 中添加學生選課記錄時，檢查該學生的中添加學生選課記錄時，檢查該學生的SnoSno是是 否存在。若不存在，則不能將記錄插入。否存在。若不存在，則不能將記錄插入。 CREATE TRIGGER sc_insert ON SC AFTER INSERT AS IF (SELECT COUNT(*) FROM Student, inserted WHERE Stude

38、nt.Sno=inserted.Sno)=0 BEGIN PRINT 學號不存在，不能插入該記錄學號不存在，不能插入該記錄 ROLLBACK TRANSACTION END; Principles and Applications of Database System 32 4.2.2 4.2.2 定義定義觸發(fā)器觸發(fā)器(續(xù)續(xù)) 例例4.114.11 創(chuàng)建創(chuàng)建UPDATEUPDATE觸發(fā)器，禁止對觸發(fā)器，禁止對StudentStudent表表 中學生的學號進行修改。中學生的學號進行修改。 CREATE TRIGGER student_update ON Student AFTER UPDATE

39、AS IF UPDATE(Sno) BEGIN PRINT 學生的學號不能修改學生的學號不能修改 ROLLBACK TRANSACTION END; Principles and Applications of Database System 33 4.2.2 4.2.2 定義定義觸發(fā)器觸發(fā)器(續(xù)續(xù)) 例例4.124.12 在在SCSC表上創(chuàng)建觸發(fā)器，當一次向表上創(chuàng)建觸發(fā)器，當一次向SCSC表中添加多表中添加多 個記錄時，刪除學號在個記錄時，刪除學號在StudentStudent表中不存在的記錄，表中不存在的記錄， 從而保證數(shù)據(jù)的一致性。注意，不能在從而保證數(shù)據(jù)的一致性。注意，不能在SCSC表

40、中定義外表中定義外 鍵約束。鍵約束。 CREATE TRIGGER sc_insert ON SC AFTER INSERT AS IF(SELECT COUNT(*) FROM Student, inserted WHERE Student.Sno=inserted.Sno) ROWCOUNT BEGIN DELETE FROM SC WHERE Sno NOT IN (SELECT Sno FROM Student) END; Principles and Applications of Database System 34 4.2.2 4.2.2 定義定義觸發(fā)器觸發(fā)器(續(xù)續(xù)) 例例4.1

41、34.13 在視圖上定義在視圖上定義INSTEAD OFINSTEAD OF觸發(fā)器。觸發(fā)器。 在第在第3 3章曾經(jīng)介紹，視圖中有導出列時不能對視章曾經(jīng)介紹，視圖中有導出列時不能對視 圖進行更新，但是，可以利用圖進行更新，但是，可以利用INSTEAD OFINSTEAD OF觸發(fā)觸發(fā) 器對視圖進行更新。器對視圖進行更新。 假設(shè)有一個反映學生出生年份的視圖假設(shè)有一個反映學生出生年份的視圖 CREATE VIEW CREATE VIEW birth_viewbirth_view(Sno, Smame, Ssex, (Sno, Smame, Ssex, Sbirth, Sdept)Sbirth, Sd

42、ept) AS SELECT Sno,Sname,Ssex,AS SELECT Sno,Sname,Ssex,2008-Sage2008-Sage,Sdept,Sdept FROM student; FROM student; Principles and Applications of Database System 35 4.2.2 4.2.2 定義定義觸發(fā)器觸發(fā)器(續(xù)續(xù)) CREATE TRIGGER birth_view_insert ON birth_view INSTEAD OF INSERT AS DECLARE Sno CHAR(6) DECLARE Sname VARCHAR

43、(10) DECLARE Ssex CHAR(2) DECLARE birth INT DECLARE Sage INT DECLARE Sdept CHAR(20) SELECT Sno=Sno, Sname=Sname, Ssex=Ssex, birth=Sbirth, Sdept=Sdept FROM inserted SET Sage = 2008birth INSERT INTO Student（Sno, Sname, Ssex, Sage, Sdept） VALUES（Sno, Sname, Ssex, Sage, Sdept）; Principles and Applications of Database System 36 4.2.2 4.2.2 定義定義觸發(fā)器觸發(fā)器(續(xù)續(xù)) 修改觸發(fā)器修改觸發(fā)器命令格式為：命令格式為： ALTER TRIGGERALTER TRIGGER trigger_name trigger_name ON table_name | view_nameON table_name | view_name WIT