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

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

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

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

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

5、戶定義的存儲過程只能在當前數(shù)據(jù)庫中創(chuàng)建； 一個存儲過程最大不能超過128MB。若超過128MB，可將超出的部分編寫為另一個存儲過程，然后在存儲過程中調用。9.4.1 創(chuàng)建存儲過程 例9.23 輸入某個同學的學號，統(tǒng)計該同學的平均分。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)建存儲過程 例9.24 輸入某個同學的學號，統(tǒng)計該同學的平均分，并返回該同學的姓名和平均分。 分析： 該過程涉及三個參數(shù)： 一個輸入?yún)?shù)，設為sNo，用于接收某同學的學號； 兩個輸出參數(shù)，用于返回查詢到的同學姓名和平均分，設為sName 和avg 實現(xiàn)方法一： 用一個查詢，根據(jù)輸入?yún)?shù)sNo，查詢出該同學的姓名并放到輸出參數(shù)sName中 由于在學生表中學號是唯一的，使用命令：SELECT snName=studentName FROM Student WHERE studentNo=sNo9.4.1 創(chuàng)建存儲過程 用另一個查詢，根據(jù)輸入?yún)?shù)sNo，查詢

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

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

9、tNo=sNo 定義局部變量score，用于接收從游標集中獲取的成績； 定義局部變量count，用于統(tǒng)計選課門數(shù)； 定義局部變量sum，用于對成績進行累加。9.4.1 創(chuàng)建存儲過程 其存儲過程為：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)計學生選課門數(shù)count和總分sum，使用游標： DECLARE myCur CURSOR FOR SELECT score FROM Score WHERE studentNo=sNo 9.4.1 創(chuàng)建存儲過程 OPEN myCur - 打開游標 FETCH myCur INTO score - 獲取當前元組數(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)建存儲過程 SQL Server數(shù)據(jù)庫還可以返回一個數(shù)據(jù)集合 該數(shù)據(jù)集合在客戶端的程序中可以被網(wǎng)格類的對象接收； 可以對其進行逐行處理； 游標中可以嵌套游標。 例9.25 輸入某同學的學號，使用游標統(tǒng)計該同學的平均分，并返回平均分，同時逐行顯示該同學的姓名、選課名稱和選課成績。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)建存儲過程 -定義、打開、獲取游標 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è)務處理 SELECT sName, cName, grade - 輸出 SET sum=sum+grade SET count=count+1 FETCH curGrade INTO sName, cName, grade END9.4.1 創(chuàng)建存儲過程 CLOSE curGrade DEALLOCATE curGrade IF count=0

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

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

16、稱，查詢該學院所有的班級編號和班級名稱。 將查詢出的班級編號和班級名稱放入變量classNo、className中。定義游標語句為： DECLARE curClass CURSOR FOR SELECT classNo, className FROM Class WHERE institute=institute9.4.1 創(chuàng)建存儲過程 由于一個班級選修了多門課程，需依據(jù)查詢出來的班級號，按選課的課程名進行分組計算，統(tǒng)計該班每門課程的選課人數(shù)和選課平均分。 需要使用第二個游標，將查詢出來的該班的選課人數(shù)和平均分放入變量count和avg中。定義游標語句為：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變量的值是從外游標中獲取的班級編號。 將查詢出來的班級編號、班級名稱、課程名稱、課程選課人數(shù)、課程平均分插入到臨時表#myTemp中。9.4.1 創(chuàng)建存儲過程 存儲過程為：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) /*定義一個臨時表，存放每個班級的班級編號、班級名稱、課程 名稱、課程選課人數(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)建存儲過程 -定義游標curClass，依據(jù)輸入?yún)?shù)institute，查找班級編號和班級名稱 DECLARE curClass CURSOR FOR SELECT classNo, className FROM Class WHERE institute=institute OPEN curClass FETCH curClass INTO classNo, className WHILE (FETCH_STATUS=0) BEGIN -定義游標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)建存儲過程 OPEN curCourse FETCH curCourse INTO courseName, count, avg WHILE (FETCH_STATUS=0) BEGIN /*

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

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

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

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

25、dentAvg，將顯示結果內容刪除。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 修改和刪除存儲過程 -定義、打開、獲取游標 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è)務處理 SET sum=sum+grade SET count=count+1 FETCH curGrade INTO sName, cName, grade END CLOSE curGrade DEAL

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

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

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

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

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

32、本例需要使用插入和修改兩個觸發(fā)器，因為可能破壞約束 “性別僅能取男和女”的操作是插入和修改操作。 違約條件是： 如果在inserted表中存在有性別取值不為“男”或“女”的記錄（由于inserted表保存了修改后的記錄，只要對inserted表進行判斷即可），則取消本次操作。 插入觸發(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 該例也可以合并為一個觸發(fā)器CREATE TRIGGER sexUptInsON StudentFOR INSERT, UPDATEAS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( 男, 女) ) ROLLBACK 本例的inserted表結構與Student表結構相同。9.5.1 創(chuàng)建觸發(fā)器 例9.33 創(chuàng)建觸發(fā)器，當輸入某個同學選課成績時，如果他是

34、少數(shù)民族人，其成績自動加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ā)器 ，自動修改班級表中的班級人數(shù)，規(guī)定一次僅能修改一個學生記錄。 分析： 該觸發(fā)器的含義是： 當對學生表Student刪除和插入記錄時必須修改班級人數(shù)； 當修改學生表中某同學的所屬班級時，也要修改班級表中的相應班級的人數(shù)； 分別為插入、刪除和修改操作設計觸發(fā)器。 由于規(guī)定一次僅能修改一個學生記錄，因此在觸發(fā)器中必須進行判斷：如果執(zhí)行DML語句作用的對象超過一條記錄，則取消本次操作。 由于規(guī)定一次僅能修改

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

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

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

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

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

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

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

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