第9章 數(shù)據(jù)庫(kù)完整性與安全—2

1、第9章 數(shù)據(jù)庫(kù)完整性與安全 目 錄9.4數(shù)據(jù)庫(kù)安全性 9.1數(shù)據(jù)庫(kù)完整性 9.29.3游標(biāo) 存儲(chǔ)過(guò)程觸發(fā)器應(yīng)用與安全設(shè)計(jì) 9.59.69.4 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程是為了完成特定功能匯集而成的一組命名了的SQL語(yǔ)句集合 該集合編譯后存放在數(shù)據(jù)庫(kù)中，可根據(jù)實(shí)際情況重新編譯； 存儲(chǔ)過(guò)程可直接運(yùn)行，也可遠(yuǎn)程運(yùn)行； 存儲(chǔ)過(guò)程直接在服務(wù)器端運(yùn)行。使用存儲(chǔ)過(guò)程具有如下優(yōu)點(diǎn)： 將業(yè)務(wù)操作封裝 可為復(fù)雜的業(yè)務(wù)操作編寫存儲(chǔ)過(guò)程，放在數(shù)據(jù)庫(kù)中； 用戶可調(diào)用存儲(chǔ)過(guò)程執(zhí)行，而業(yè)務(wù)操作對(duì)用戶是不可見的； 若存儲(chǔ)過(guò)程僅修改了執(zhí)行體，沒有修改接口（即調(diào)用參數(shù)），則用戶程序不需要修改，達(dá)到業(yè)務(wù)封裝的效果。 便于事務(wù)管理 事務(wù)控制可

2、以用在存儲(chǔ)過(guò)程中； 用戶可依據(jù)業(yè)務(wù)的性質(zhì)定義事務(wù)，并對(duì)事務(wù)進(jìn)行相應(yīng)級(jí)別的操作。9.4 存儲(chǔ)過(guò)程 實(shí)現(xiàn)一定程度的安全性保護(hù) 存儲(chǔ)過(guò)程存放在數(shù)據(jù)庫(kù)中，且在服務(wù)器端運(yùn)行； 對(duì)于不允許用戶直接操作的表或視圖，可通過(guò)調(diào)用存儲(chǔ)過(guò)程來(lái)間接地訪問(wèn)這些表或視圖，達(dá)到一定程度的安全性； 這種安全性緣于用戶對(duì)存儲(chǔ)過(guò)程只有執(zhí)行權(quán)限，沒有查看權(quán)限； 擁有存儲(chǔ)過(guò)程的執(zhí)行權(quán)限，自動(dòng)獲取了存儲(chǔ)過(guò)程中對(duì)相應(yīng)表或視圖的操作權(quán)限； 這些操作權(quán)限僅能通過(guò)執(zhí)行存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)，一旦脫離存儲(chǔ)過(guò)程，也就失去了相應(yīng)操作權(quán)限。 注意：對(duì)存儲(chǔ)過(guò)程只需授予執(zhí)行權(quán)限，不需授予表或視圖的操作權(quán)限。 特別適合統(tǒng)計(jì)和查詢操作 一般統(tǒng)計(jì)和查詢，尤其是期末統(tǒng)計(jì)

3、，往往涉及數(shù)據(jù)量大、表多，若在客戶端實(shí)現(xiàn)，數(shù)據(jù)流量和網(wǎng)絡(luò)通信量較大； 很多情況下，管理信息系統(tǒng)的設(shè)計(jì)者，將復(fù)雜的查詢和統(tǒng)計(jì)用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)，免去客戶端的大量編程。9.4 存儲(chǔ)過(guò)程 減少網(wǎng)絡(luò)通信量 存儲(chǔ)過(guò)程僅在服務(wù)器端執(zhí)行，客戶端只接收結(jié)果； 由于存儲(chǔ)過(guò)程與數(shù)據(jù)一般在一個(gè)服務(wù)器中，可減少大量的網(wǎng)絡(luò)通信量。 使用存儲(chǔ)過(guò)程前，首先要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程?？蓪?duì)存儲(chǔ)過(guò)程進(jìn)行修改和刪除。 創(chuàng)建存儲(chǔ)過(guò)程后，必須對(duì)存儲(chǔ)過(guò)程授予執(zhí)行EXECUTE的權(quán)限，否則該存儲(chǔ)過(guò)程僅可以供創(chuàng)建者執(zhí)行。 9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 9.4.2 執(zhí)行存儲(chǔ)過(guò)程 9.4.3 修改和刪除存儲(chǔ)過(guò)程9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 語(yǔ)法：CREATE PR

4、OCEDURE ( = OUTPUT , = OUTPUT ) AS 其中： ：過(guò)程名，必須符合標(biāo)識(shí)符規(guī)則，且在數(shù)據(jù)庫(kù)中唯一； ：參數(shù)名，存儲(chǔ)過(guò)程可不帶參數(shù)，參數(shù)可以是變量、常量和表達(dá)式； OUTPUT：說(shuō)明該參數(shù)是輸出參數(shù)，被調(diào)用者獲取使用。缺省時(shí)表示是輸入?yún)?shù)。9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 如果存儲(chǔ)過(guò)程的輸出參數(shù)取集合值，則該輸出參數(shù)不在存儲(chǔ)過(guò)程的參數(shù)中定義，而是在存儲(chǔ)過(guò)程中定義一個(gè)臨時(shí)表來(lái)存儲(chǔ)該集合值。 臨時(shí)表的表名前加一個(gè)#符號(hào)，如#myTemp 在存儲(chǔ)過(guò)程尾部，使用語(yǔ)句： SELECT * FROM #myTemp 將結(jié)果集合返回給調(diào)用者。 存儲(chǔ)過(guò)程結(jié)束后，臨時(shí)表自動(dòng)被刪除。 注意： 用

5、戶定義的存儲(chǔ)過(guò)程只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建； 一個(gè)存儲(chǔ)過(guò)程最大不能超過(guò)128MB。若超過(guò)128MB，可將超出的部分編寫為另一個(gè)存儲(chǔ)過(guò)程，然后在存儲(chǔ)過(guò)程中調(diào)用。9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 例9.23 輸入某個(gè)同學(xué)的學(xué)號(hào)，統(tǒng)計(jì)該同學(xué)的平均分。CREATE PROCEDURE proStudentByNo1(sNo char(7)AS SELECT a.studentNo, studentName, avg(score) FROM Student a, Score b WHERE a.studentNo=b.studentNo AND a.studentNo=sNo GROUP BY a.studentN

6、o, studentName9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 例9.24 輸入某個(gè)同學(xué)的學(xué)號(hào)，統(tǒng)計(jì)該同學(xué)的平均分，并返回該同學(xué)的姓名和平均分。 分析： 該過(guò)程涉及三個(gè)參數(shù)： 一個(gè)輸入?yún)?shù)，設(shè)為sNo，用于接收某同學(xué)的學(xué)號(hào)； 兩個(gè)輸出參數(shù)，用于返回查詢到的同學(xué)姓名和平均分，設(shè)為sName 和avg 實(shí)現(xiàn)方法一： 用一個(gè)查詢，根據(jù)輸入?yún)?shù)sNo，查詢出該同學(xué)的姓名并放到輸出參數(shù)sName中 由于在學(xué)生表中學(xué)號(hào)是唯一的，使用命令：SELECT snName=studentName FROM Student WHERE studentNo=sNo9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 用另一個(gè)查詢，根據(jù)輸入?yún)?shù)sNo，查詢

7、出該同學(xué)的選課平均分并放到輸出參數(shù)avg中 由于該同學(xué)的平均分也只有一個(gè)，使用命令： SELECT avg=avg(score) FROM Score WHERE studentNo=sNo GROUP BY studentNo9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程為：CREATE PROCEDURE proStudentByNo21(sNo char(7), sName varchar(20) OUTPUT, avg numeric(5, 1) OUTPUT )ASBEGIN - 當(dāng)中多于1個(gè)SQL語(yǔ)句時(shí)，使用塊定義 -查詢同學(xué)的姓名放入輸出參數(shù)sName中 SELECT sName=stude

8、ntName FROM Student WHERE studentNo=sNo -查詢同學(xué)選課的平均分放入輸出參數(shù)avg中 SELECT avg=avg(score) FROM Score WHERE studentNo=sNo GROUP BY studentNoEND9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 實(shí)現(xiàn)方法二： 用一個(gè)查詢，根據(jù)輸入?yún)?shù)sNo，查詢出該同學(xué)的姓名并放到輸出參數(shù)sName中，其命令同方法一 定義一個(gè)游標(biāo)，根據(jù)輸入?yún)?shù)sNo，查詢?cè)撏瑢W(xué)所有的選課記錄，使用命令： DECLARE myCur CURSOR FOR SELECT score FROM Score WHERE studen

9、tNo=sNo 定義局部變量score，用于接收從游標(biāo)集中獲取的成績(jī)； 定義局部變量count，用于統(tǒng)計(jì)選課門數(shù)； 定義局部變量sum，用于對(duì)成績(jī)進(jìn)行累加。9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 其存儲(chǔ)過(guò)程為：CREATE PROCEDURE proStudentByNo22(sNo char(7), sName varchar(20) OUTPUT, avg numeric(5, 1) OUTPUT)AS BEGIN DECLARE score tinyint, count tinyint, sum int -查找姓名，并放入到輸出參數(shù)sName中 SELECT sName=studentName FRO

10、M Student WHERE studentNo=sNo -變量賦初值 SET count=0 SET sum=0 -統(tǒng)計(jì)學(xué)生選課門數(shù)count和總分sum，使用游標(biāo)： DECLARE myCur CURSOR FOR SELECT score FROM Score WHERE studentNo=sNo 9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 OPEN myCur - 打開游標(biāo) FETCH myCur INTO score - 獲取當(dāng)前元組數(shù)據(jù) WHILE (FETCH_STATUS=0) BEGIN SET count=count+1 SET sum=sum+score FETCH myCur INT

11、O score - 獲取下一元組數(shù)據(jù) END CLOSE myCur DEALLOCATE myCur IF count0 SELECT avg=sum / count ELSE SELECT avg=0END9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 SQL Server數(shù)據(jù)庫(kù)還可以返回一個(gè)數(shù)據(jù)集合 該數(shù)據(jù)集合在客戶端的程序中可以被網(wǎng)格類的對(duì)象接收； 可以對(duì)其進(jìn)行逐行處理； 游標(biāo)中可以嵌套游標(biāo)。 例9.25 輸入某同學(xué)的學(xué)號(hào)，使用游標(biāo)統(tǒng)計(jì)該同學(xué)的平均分，并返回平均分，同時(shí)逐行顯示該同學(xué)的姓名、選課名稱和選課成績(jī)。CREATE PROCEDURE proStudentAvg(sNo char(7), avg n

12、umeric(6, 2) OUTPUT )ASBEGIN DECLARE sName varchar(20), cName varchar(20) DECLARE grade tinyint, sum int, count tinyint SELECT sum=0, count=09.4.1 創(chuàng)建存儲(chǔ)過(guò)程 -定義、打開、獲取游標(biāo) DECLARE curGrade CURSOR FOR SELECT studentName, courseName, score FROM Score a, Student b, Course c WHERE b.studentNo=sNo AND a.studen

13、tNo=b.studentNo AND a.courseNo=c.courseNo OPEN curGrade FETCH curGrade INTO sName, cName, grade WHILE (FETCH_STATUS=0) BEGIN -業(yè)務(wù)處理 SELECT sName, cName, grade - 輸出 SET sum=sum+grade SET count=count+1 FETCH curGrade INTO sName, cName, grade END9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 CLOSE curGrade DEALLOCATE curGrade IF count=0

14、 SELECT avg=0 ELSE SELECT avg=sum/countEND 本例使用了SELECT語(yǔ)句來(lái)顯示變量的值，即 SELECT sName, cName, grade 由于存儲(chǔ)過(guò)程僅在服務(wù)器端執(zhí)行，其顯示的內(nèi)容只在服務(wù)器端出現(xiàn)，并不返回給客戶端，這樣的輸出結(jié)果是沒有價(jià)值的。 顯示內(nèi)容在調(diào)試存儲(chǔ)過(guò)程時(shí)有作用，一旦過(guò)程調(diào)試正確，使用存儲(chǔ)過(guò)程的修改命令將顯示內(nèi)容刪除。9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 例9.26 輸入某學(xué)院名稱，統(tǒng)計(jì)該學(xué)院每個(gè)班級(jí)同學(xué)的選課信息，返回班級(jí)編號(hào)、班級(jí)名稱、課程名稱、課程選課人數(shù)、課程平均分。 本例使用嵌套游標(biāo)，讀者通過(guò)該例掌握嵌套游標(biāo)的使用方法。 分析： 本例

15、涉及兩個(gè)參數(shù) 一個(gè)是輸入?yún)?shù)：學(xué)院名稱，設(shè)為institute； 一個(gè)是輸出參數(shù)，它為一個(gè)集合值，包含了該學(xué)院所有班級(jí)的班級(jí)編號(hào)、班級(jí)名稱、課程名稱、課程選課人數(shù)、課程平均分； 對(duì)于集合值輸出參數(shù)，在過(guò)程中定義一個(gè)臨時(shí)表來(lái)存儲(chǔ)該集合，設(shè)臨時(shí)表為#myTemp 在過(guò)程尾部使用語(yǔ)句“SELECT * FROM #myTemp”將該集合返回給調(diào)用者。9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 定義5個(gè)臨時(shí)變量，分別保存查詢出來(lái)的班級(jí)編號(hào)classNo、班級(jí)名稱className、課程名稱courseName、選課人數(shù)count、選課平均分avg。 由于一個(gè)學(xué)院有多個(gè)班級(jí)，定義一個(gè)游標(biāo)curClass，根據(jù)輸入的學(xué)院名

16、稱，查詢?cè)搶W(xué)院所有的班級(jí)編號(hào)和班級(jí)名稱。 將查詢出的班級(jí)編號(hào)和班級(jí)名稱放入變量classNo、className中。定義游標(biāo)語(yǔ)句為： DECLARE curClass CURSOR FOR SELECT classNo, className FROM Class WHERE institute=institute9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 由于一個(gè)班級(jí)選修了多門課程，需依據(jù)查詢出來(lái)的班級(jí)號(hào)，按選課的課程名進(jìn)行分組計(jì)算，統(tǒng)計(jì)該班每門課程的選課人數(shù)和選課平均分。 需要使用第二個(gè)游標(biāo)，將查詢出來(lái)的該班的選課人數(shù)和平均分放入變量count和avg中。定義游標(biāo)語(yǔ)句為：DECLARE curCourse C

17、URSOR FOR SELECT courseName, count(*), avg(score) FROM Student a, Score b, Course c WHERE a.studentNo=b.studentNo AND b.courseNo=c.courseNo AND classNo=classNo GROUP BY courseName 注意：classNo變量的值是從外游標(biāo)中獲取的班級(jí)編號(hào)。 將查詢出來(lái)的班級(jí)編號(hào)、班級(jí)名稱、課程名稱、課程選課人數(shù)、課程平均分插入到臨時(shí)表#myTemp中。9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程為：CREATE PROCEDURE proInst

18、itute( institute varchar(30) )ASBEGIN DECLARE className varchar(30), courseName varchar(30) DECLARE classNo char(6), count tinyint, avg numeric(5, 1) /*定義一個(gè)臨時(shí)表，存放每個(gè)班級(jí)的班級(jí)編號(hào)、班級(jí)名稱、課程 名稱、課程選課人數(shù)、課程平均分*/ CREATE TABLE #myTemp ( classNo char(6), className varchar(30), courseName varchar(30), classCount tiny

19、int, classAvg numeric(5, 1) )9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 -定義游標(biāo)curClass，依據(jù)輸入?yún)?shù)institute，查找班級(jí)編號(hào)和班級(jí)名稱 DECLARE curClass CURSOR FOR SELECT classNo, className FROM Class WHERE institute=institute OPEN curClass FETCH curClass INTO classNo, className WHILE (FETCH_STATUS=0) BEGIN -定義游標(biāo)curCourse，查找classNo班選課的課程名稱、選課人數(shù)、平均分 D

20、ECLARE curCourse CURSOR FOR SELECT courseName, count(*), avg(score) FROM Student a, Score b, Course c WHERE a.studentNo=b.studentNo AND b.courseNo=c.courseNo AND classNo=classNo GROUP BY courseName9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 OPEN curCourse FETCH curCourse INTO courseName, count, avg WHILE (FETCH_STATUS=0) BEGIN /*

21、 將班級(jí)編號(hào)、班級(jí)名稱、課程名稱、課程選課人數(shù)、課程平均分 插入到臨時(shí)表#myTemp中 */ INSERT INTO #myTemp VALUES( classNo, className, courseName, count, avg ) - 獲取下一游標(biāo)值，取該班下一門課程的課程名、選課人數(shù)和平均分 FETCH curCourse INTO courseName, count, avg END CLOSE curCourse DEALLOCATE curCourse -獲取游標(biāo)curClass的下一個(gè)值，即取下一個(gè)班級(jí) FETCH curClass INTO classNo, classN

22、ame END CLOSE curClass DEALLOCATE curClass -顯示臨時(shí)表的內(nèi)容，同時(shí)將臨時(shí)表的內(nèi)容返回給調(diào)用者 SELECT * FROM #myTempEND9.4.1 創(chuàng)建存儲(chǔ)過(guò)程 在本例中，獲取班級(jí)編號(hào)、班級(jí)名稱不能寫成： SELECT classNo=classNo, className=className FROM Class WHERE institute=institute 因?yàn)椋?一個(gè)學(xué)院有多個(gè)班級(jí)，該查詢返回一個(gè)元組集合。 變量classNo和className僅分別接收一個(gè)數(shù)據(jù)。 必須使用游標(biāo)，本例定義游標(biāo)為curClass。9.4.2 執(zhí)行存儲(chǔ)過(guò)

23、程 使用存儲(chǔ)過(guò)程時(shí)，必須執(zhí)行命令EXECUTE 語(yǔ)法： EXECUTE = , = OUTPUT , = , = OUTPUT 注意：EXECUTE的參數(shù)必須與對(duì)應(yīng)的PROCEDURE的參數(shù)相匹配。 例9.27 執(zhí)行存儲(chǔ)過(guò)程proStudentByNo1 EXECUTE proStudentByNo1 0800001 9.4.2 執(zhí)行存儲(chǔ)過(guò)程 例9.28 執(zhí)行存儲(chǔ)過(guò)程proStudentByNo2 DECLARE sName varchar(20), avg numeric(5, 1)EXECUTE proStudentByNo2 0800001, sName OUTPUT, avg OUTP

24、UTSELECT sName, avg 例9.29 執(zhí)行過(guò)程proInstitute EXECUTE proInstitute 信息管理學(xué)院 也可以使用命令：DECLARE institute varchar(30)SET institute=信息管理學(xué)院EXECUTE proInstitute institute9.4.3 修改和刪除存儲(chǔ)過(guò)程 修改存儲(chǔ)過(guò)程 語(yǔ)法為： ALTER PROCEDURE = OUTPUT , = OUTPUT AS 注意：由于存儲(chǔ)過(guò)程是在服務(wù)器端執(zhí)行，程序中不需要有輸出命令SELECT，由SELECT引出的輸出不會(huì)在客戶端出現(xiàn)。例9.30 修改存儲(chǔ)過(guò)程proStu

25、dentAvg，將顯示結(jié)果內(nèi)容刪除。ALTER PROCEDURE proStudentAvg( sNo char(7), avg numeric(6, 2) OUTPUT )ASBEGIN DECLARE sName varchar(20), cName varchar(20) DECLARE grade tinyint, sum int, count tinyint SELECT sum=0, count=09.4.3 修改和刪除存儲(chǔ)過(guò)程 -定義、打開、獲取游標(biāo) DECLARE curGrade CURSOR FOR SELECT studentName, courseName, scor

26、e FROM Score a, Student b, Course c WHERE b.studentNo=sNo AND a.studentNo=b.studentNo AND a.courseNo=c.courseNo OPEN curGrade FETCH curGrade INTO sName, cName, grade WHILE (FETCH_STATUS=0) BEGIN -業(yè)務(wù)處理 SET sum=sum+grade SET count=count+1 FETCH curGrade INTO sName, cName, grade END CLOSE curGrade DEAL

27、LOCATE curGrade 9.4.3 修改和刪除存儲(chǔ)過(guò)程 IF count=0 SELECT avg = 0 ELSE SELECT avg=sum/count END 刪除存儲(chǔ)過(guò)程 語(yǔ)法： DROP PROCEDURE 例9.31 刪除存儲(chǔ)過(guò)程proStudentAvg DROP PROCEDURE proStudentAvg目 錄9.4數(shù)據(jù)庫(kù)安全性 9.1數(shù)據(jù)庫(kù)完整性 9.29.3游標(biāo) 存儲(chǔ)過(guò)程觸發(fā)器應(yīng)用與安全設(shè)計(jì) 9.59.69.5觸發(fā)器 觸發(fā)器(trigger)是用戶定義在關(guān)系表上的一類由事件驅(qū)動(dòng)的存儲(chǔ)過(guò)程，由服務(wù)器自動(dòng)激活。 觸發(fā)器可進(jìn)行更為復(fù)雜的檢查和操作，具有更精細(xì)和更強(qiáng)大

28、的數(shù)據(jù)控制能力。 觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，不管什么原因造成的數(shù)據(jù)變化都能自動(dòng)響應(yīng)，對(duì)于每條SQL語(yǔ)句，觸發(fā)器僅執(zhí)行一次，事務(wù)可用于觸發(fā)器中。 事務(wù)定義： BEGIN TRANSACTION COMMIT TRANSACTION ROLLBACK TRANSACTION 有兩個(gè)特殊的表用在觸發(fā)器語(yǔ)句中，不同的數(shù)據(jù)庫(kù)管理系統(tǒng)其名稱不一樣： 在SQL Server中使用deleted和inserted表； Oracle數(shù)據(jù)庫(kù)使用old和new表。9.5觸發(fā)器 注意： 這兩張表的結(jié)構(gòu)與作用的表結(jié)構(gòu)完全一致； 當(dāng)作用表的SQL語(yǔ)句開始時(shí)，自動(dòng)產(chǎn)生這兩張表的結(jié)構(gòu)與內(nèi)容； 當(dāng)SQL語(yǔ)句執(zhí)行完畢，這兩張表

29、也隨即刪除。 下面以SQL Server為例介紹觸發(fā)器： deleted表 存儲(chǔ)DELETE和UPDATE語(yǔ)句執(zhí)行時(shí)所影響的行的拷貝； 在DELETE和UPDATE語(yǔ)句執(zhí)行前被作用的行轉(zhuǎn)移到deleted表中。 將被刪除的元組或修改前的元組值存入該表中 inserted表 存儲(chǔ)INSERT和UPDATE語(yǔ)句執(zhí)行時(shí)所影響的行的拷貝； 在INSERT和UPDATE語(yǔ)句執(zhí)行期間，新行被同時(shí)加到inserted表和觸發(fā)器表中。 將被插入的元組或修改后的元組值存入該表中，同時(shí)更新基本表。9.5觸發(fā)器 實(shí)際上，UPDATE命令是刪除后緊跟著插入，舊行首先拷貝到deleted表中，新行同時(shí)拷貝到insert

30、ed表和基本表中。 觸發(fā)器僅在當(dāng)前數(shù)據(jù)庫(kù)中生成 觸發(fā)器有三種類型，即插入、刪除和更新； 插入、刪除和更新可作為一種類型的觸發(fā)器； 查詢操作不會(huì)產(chǎn)生觸發(fā)動(dòng)作，沒有查詢觸發(fā)器類型。 9.5.1 創(chuàng)建觸發(fā)器 9.5.2 修改和刪除觸發(fā)器 9.5.3 觸發(fā)器的作用 9.5.1 創(chuàng)建觸發(fā)器 創(chuàng)建觸發(fā)器的語(yǔ)法： CREATE TRIGGER ON FOR INSERT | UPDATE | DELETE AS 其中： ：觸發(fā)器的名稱，在數(shù)據(jù)庫(kù)中必須唯一； ：觸發(fā)器作用的基本表，該表也稱為觸發(fā)器的目標(biāo)表； INSERT | UPDATE | DELETE ：觸發(fā)器事件，觸發(fā)器的事件可以是插入INSERT、更

31、新UPDATE和刪除DELETE事件，也可以是這幾個(gè)事件的組合。9.5.1 創(chuàng)建觸發(fā)器 INSERT 類型的觸發(fā)器是指：當(dāng)對(duì)指定表執(zhí)行了插入操作時(shí)系統(tǒng)自動(dòng)執(zhí)行觸發(fā)器代碼。 UPDATE 類型的觸發(fā)器是指：當(dāng)對(duì)指定表執(zhí)行了更新操作時(shí)系統(tǒng)自動(dòng)執(zhí)行觸發(fā)器代碼。 DELETE類型的觸發(fā)器是指：當(dāng)對(duì)指定表執(zhí)行了刪除操作時(shí)系統(tǒng)自動(dòng)執(zhí)行觸發(fā)器代碼。 ：觸發(fā)動(dòng)作的執(zhí)行體，即一段SQL語(yǔ)句塊 如果該觸發(fā)執(zhí)行體執(zhí)行失敗，則激活觸發(fā)器的事件就會(huì)終止，且觸發(fā)器的目標(biāo)表或觸發(fā)器可能影響的其它表不發(fā)生任何變化，即執(zhí)行事務(wù)的回滾操作。9.5.1 創(chuàng)建觸發(fā)器 例9.32 創(chuàng)建觸發(fā)器，保證學(xué)生表中的性別僅能取男和女。 分析：

32、本例需要使用插入和修改兩個(gè)觸發(fā)器，因?yàn)榭赡芷茐募s束 “性別僅能取男和女”的操作是插入和修改操作。 違約條件是： 如果在inserted表中存在有性別取值不為“男”或“女”的記錄（由于inserted表保存了修改后的記錄，只要對(duì)inserted表進(jìn)行判斷即可），則取消本次操作。 插入觸發(fā)器CREATE TRIGGER sexIns ON StudentFOR INSERTAS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( 男, 女) ) ROLLBACK9.5.1 創(chuàng)建觸發(fā)器 修改觸發(fā)器CREATE TRIGGER sexUpt ON

33、 StudentFOR UPDATEAS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( 男, 女) ) ROLLBACK 該例也可以合并為一個(gè)觸發(fā)器CREATE TRIGGER sexUptInsON StudentFOR INSERT, UPDATEAS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( 男, 女) ) ROLLBACK 本例的inserted表結(jié)構(gòu)與Student表結(jié)構(gòu)相同。9.5.1 創(chuàng)建觸發(fā)器 例9.33 創(chuàng)建觸發(fā)器，當(dāng)輸入某個(gè)同學(xué)選課成績(jī)時(shí)，如果他是

34、少數(shù)民族人，其成績(jī)自動(dòng)加5分。CREATE TRIGGER ScoreIns ON ScoreFOR INSERTAS IF EXISTS ( SELECT * FROM inserted, Student WHERE inserted.studentNo=Student.studentNo AND student.nation漢族 ) UPDATE Score SET Score.score=Score.score+5 FROM inserted, Student WHERE inserted.studentNo=Student.studentNo AND Student.nation漢族

35、AND inserted.studentNo=Score.studentNo AND inserted.courseNo=Score.courseNo9.5.1 創(chuàng)建觸發(fā)器 例9.34 創(chuàng)建觸發(fā)器 ，自動(dòng)修改班級(jí)表中的班級(jí)人數(shù)，規(guī)定一次僅能修改一個(gè)學(xué)生記錄。 分析： 該觸發(fā)器的含義是： 當(dāng)對(duì)學(xué)生表Student刪除和插入記錄時(shí)必須修改班級(jí)人數(shù)； 當(dāng)修改學(xué)生表中某同學(xué)的所屬班級(jí)時(shí)，也要修改班級(jí)表中的相應(yīng)班級(jí)的人數(shù)； 分別為插入、刪除和修改操作設(shè)計(jì)觸發(fā)器。 由于規(guī)定一次僅能修改一個(gè)學(xué)生記錄，因此在觸發(fā)器中必須進(jìn)行判斷：如果執(zhí)行DML語(yǔ)句作用的對(duì)象超過(guò)一條記錄，則取消本次操作。 由于規(guī)定一次僅能修改

36、一個(gè)學(xué)生記錄，因此可直接在SELECT語(yǔ)句中使用變量接收查詢出來(lái)的屬性值，不需要使用游標(biāo)： SELECT classNo=classNo FROM inserted9.5.1 創(chuàng)建觸發(fā)器/* 插入觸發(fā)器，inserted表結(jié)構(gòu)與Student表結(jié)構(gòu)相同 */CREATE TRIGGER classInsON StudentFOR INSERTAS /* 定義一個(gè)變量classNo，用于接受所插入的學(xué)生所屬的班級(jí)編號(hào) */ DECLARE classNo char(6) /* 如果插入的記錄數(shù)大于1條, 則回滾 */ IF ( SELECT count(*) FROM inserted )1 R

37、OLLBACK ELSE BEGIN /* 找出插入的學(xué)生所屬的班級(jí)編號(hào)放到變量classNo中 */ SELECT classNo=classNo FROM inserted /* 更新班級(jí)表中對(duì)應(yīng)班級(jí)編號(hào)為classNo的班級(jí)人數(shù) */ UPDATE Class SET classNum=classNum+1 WHERE classNo=classNo END9.5.1 創(chuàng)建觸發(fā)器/* 刪除觸發(fā)器，deleted表結(jié)構(gòu)與Student表結(jié)構(gòu)相同 */CREATE TRIGGER classUptON StudentFOR DELETEAS /* 定義一個(gè)變量classNo，用于接受所刪除的

38、學(xué)生所屬的班級(jí)編號(hào) */ DECLARE classNo char(6) /* 如果刪除的記錄數(shù)大于1條，則回滾 */ IF ( SELECT count(*) FROM deleted )1 ROLLBACK ELSE BEGIN /* 找出刪除的學(xué)生所屬的班級(jí)編號(hào)放到變量classNo中 */ SELECT classNo=classNo FROM deleted /* 更新班級(jí)表中對(duì)應(yīng)班級(jí)編號(hào)為classNo的班級(jí)人數(shù) */ UPDATE Class SET classNum=classNum-1 WHERE classNo=classNo END9.5.1 創(chuàng)建觸發(fā)器/* 更新觸發(fā)器，

39、deleted和inserted表結(jié)構(gòu)與Student表結(jié)構(gòu)相同 */CREATE TRIGGER classUptON StudentFOR UPDATEAS /* 定義一個(gè)變量oldClassNo，用于接受所修改前的學(xué)生所屬的班級(jí)編號(hào) */ /* 定義一個(gè)變量newClassNo，用于接受所修改后的學(xué)生所屬的班級(jí)編號(hào) */ DECLARE oldClassNo char(6) DECLARE newClassNo char(6) /* 如果修改的記錄數(shù)大于1條，則回滾 */ IF ( SELECT count(*) FROM deleted )1 ROLLBACK ELSE BEGIN /

40、* 找出修改前的學(xué)生所屬的班級(jí)編號(hào)放到變量oldClassNo中 */ SELECT oldClassNo=classNo FROM deleted 9.5.1 創(chuàng)建觸發(fā)器 /* 找出修改后的學(xué)生所屬的班級(jí)編號(hào)放到變量newClassNo中 */ SELECT newClassNo=classNo FROM inserted /* 更新班級(jí)表中對(duì)應(yīng)班級(jí)編號(hào)的班級(jí)人數(shù) */ UPDATE Class SET classNum=classNum-1 WHERE classNo=oldClassNo UPDATE Class SET classNum=classNum+1 WHERE classNo

41、=newClassNo END 本例在更新觸發(fā)器中要同時(shí)使用deleted表和inserted表。9.5.2 修改和刪除觸發(fā)器 語(yǔ)法為： ALTER TRIGGER ON FOR INSERT | UPDATE | DELETE AS 9.5.2 修改和刪除觸發(fā)器 例9.35 修改例9.34中的更新觸發(fā)器，只有更新了學(xué)生所屬的班級(jí)時(shí)才觸發(fā)，允許對(duì)多條更新記錄進(jìn)行操作。 分析： 觸發(fā)條件是：只有更新了學(xué)生所屬的班級(jí)時(shí)才觸發(fā)。 在程序中使用語(yǔ)句“IF UPDATE(classNo)”來(lái)保證：如果僅修改了班級(jí)編號(hào)之外的屬性，則不引發(fā)觸發(fā)器工作。 本例允許對(duì)多條更新記錄進(jìn)行操作，必須使用游標(biāo)。 找出更

42、新前后的班級(jí)編號(hào)，分別放入到變量oldClassNo和newClassNo中； 對(duì)這兩個(gè)班的人數(shù)分別進(jìn)行減一和增一操作。9.5.2 修改和刪除觸發(fā)器 要找出更新前后的班級(jí)編號(hào)，對(duì)inserted和deleted兩張表進(jìn)行連接操作，連接條件是學(xué)號(hào)相等，其連接語(yǔ)句為 SELECT a.classNo, b.classNo FROM inserted a, deleted b WHERE a.studentNo=b.studentNo inserted表中保存的是修改后的班級(jí)編號(hào)。 deleted表中保存的是修改前的班級(jí)編號(hào)。9.5.2 修改和刪除觸發(fā)器 l 觸發(fā)器程序?yàn)椋篈LTER TRIGGER

43、 classUptON StudentFOR UPDATEAS IF UPDATE(classNo) BEGIN - 定義一個(gè)變量oldClassNo，用于接受所修改前的學(xué)生所屬的班級(jí)編號(hào) - 定義一個(gè)變量newClassNo，用于接受所修改后的學(xué)生所屬的班級(jí)編號(hào) DECLARE oldClassNo char(6) DECLARE newClassNo char(6) -定義游標(biāo)uptCur，找出更新前后的班級(jí)編號(hào) DECLARE uptCur CURSOR FOR SELECT a.classNo, b.classNo FROM inserted a, deleted b WHERE a.studentNo=b.studentNo9.5.2 修改和刪除觸發(fā)器 OPEN uptCur -打開游標(biāo)FETCH uptCur INTO newClassNo, oldClassNo -獲取當(dāng)前游標(biāo)值WHILE ( FETCH_STATUS=0 ) BEGIN /* 更新班級(jí)表中對(duì)應(yīng)班級(jí)編號(hào)的班級(jí)人數(shù) */ UPDATE Class SET classNum=classNum-1 - 原班級(jí)人數(shù)減1 WHERE classNo=oldClassNo UPDATE Class SE