數(shù)據(jù)庫應用技術 第八章 存儲過程和觸發(fā)器

1、數(shù)據(jù)庫應用技術第8章 存儲過程和觸發(fā)器1復習1、視圖和索引的作用？2、索引的分類？2答案1、Group by，Order by。2、where，having。3教學目標掌握：存儲過程和觸發(fā)器的創(chuàng)建和執(zhí)行以及管理；理解：存儲過程和觸發(fā)器的概念和作用；了解：存儲過程與觸發(fā)器的特點和分類，系統(tǒng)存儲過程和 擴展存儲過程的使用。4教學重點： 存儲過程和觸發(fā)器的創(chuàng)建、查看、修改和刪除教學難點： 存儲過程和觸發(fā)器的概念和作用5存儲過程和觸發(fā)器的作用？6教學內容8.8 觸發(fā)器使用注意事項8.7 管理觸發(fā)器8.6 創(chuàng)建觸發(fā)器8.5 觸發(fā)器簡介8.4 系統(tǒng)存儲過程和擴展存儲過程8.3 管理存儲過程8.2 存儲過程

2、的創(chuàng)建和執(zhí)行8.1 存儲過程概述78.1 存儲過程概述存儲過程以一個名稱存儲在數(shù)據(jù)庫中，并作為一個單元來處理一種數(shù)據(jù)庫對象，存放在服務器上、預先編譯好的T-SQL語句的集合8優(yōu)點1增強代碼的可重用性，提高開發(fā)效率4可以提高數(shù)據(jù)的安全性3可以減少網絡流量2執(zhí)行速度快9分類系統(tǒng)存儲過程擴展存儲過程臨時存儲過程系統(tǒng)存儲過程臨時存儲過程擴展存儲過程系統(tǒng)存儲過程臨時存儲過程本地存儲過程擴展存儲過程系統(tǒng)存儲過程臨時存儲過程108.2 存儲過程的創(chuàng)建和執(zhí)行格式CREATE PROCEDURE 存儲過程名 參數(shù) 數(shù)據(jù)類型=默認值 OUTPUT ， WITH ENCRYPTION ASSQL語句塊文本加密參數(shù)為

3、輸出類型11【例8.1】在“圖書管理”數(shù)據(jù)庫中建立一個名為“圖書出版情況”的存儲過程，該存儲過程從“圖書”、“作者”和“出版社”表中檢索所有圖書的“圖書名”、“作者名”和相應的“出版社名”。USE 圖書管理GOCREATE PROCEDURE 圖書出版情況AS SELECT 圖書名,作者名,出版社名 FROM 圖書 AS A INNER JOIN 作者 AS B ON A.作者號=B.作者號 INNER JOIN 出版社 AS C ON A.出版社號=C.出版社號GO-執(zhí)行存儲過程EXEC 圖書出版情況GO12【例8.2】在“圖書管理”數(shù)據(jù)庫中建立一個名為“作者查詢”的存儲過程，該存儲過程從“

4、作者”、“圖書”和“出版社”表中檢索指定作者的“作者名”，以及該作者出版圖書的“圖書名”和相應的“出版社名”。要求將作者姓名通過參數(shù)傳遞給存儲過程。USE 圖書管理GOCREATE PROCEDURE 作者查詢 name varchar(10)AS SELECT 作者名,圖書名,出版社名 FROM 作者 AS A INNER JOIN 圖書 AS B ON A.作者號=B.作者號 INNER JOIN 出版社 AS C ON B.出版社號=C.出版社號 WHERE 作者名=nameGO-執(zhí)行存儲過程EXEC 作者查詢 任明GO13執(zhí)行存儲過程格式：EXECUTE 存儲過程名 參數(shù)=值 | 變量

5、 OUTPUT ，【例8.3】執(zhí)行例7.20建立的存儲過程“圖書出版情況”EXEC 圖書出版情況GO14 查看存儲過程的定義格式：sp_helptext 存儲過程名【例8.5】查看存儲過程“圖書出版情況”的定義。USE 圖書管理-下一行語句不是批處理中的第一條語句，因此必須加上“EXECUTE”EXEC sp_helptext 圖書出版情況GO15 查看存儲過程的參數(shù)格式：sp_help 存儲過程名【例8.6】 查看存儲過程“作者查詢”的參數(shù)。USE 圖書管理GOsp_help 作者查詢GO 查看存儲過程的相關性格式：sp_depends 存儲過程名16 查看存儲過程的相關性格式：sp_dep

6、ends 存儲過程名【例8.6】 查看存儲過程“作者查詢”的相關性。USE 圖書管理GOsp_depends 作者查詢GO172使用T-SQL語句修改存儲過程格式：ALTER PROCEDURE 存儲過程名 參數(shù) 數(shù)據(jù)類型=默認值 OUTPUT ， WITH ENCRYPTIONASSQL語句塊【例8.8】修改例7.21建立的存儲過程“作者查詢”，要求對此存儲過程進行加密，其他不變。ALTER PROCEDURE 作者查詢 name varchar(10) WITH ENCRYPTIONAS SELECT 作者名,圖書名,出版社名 FROM 作者 AS A INNER JOIN 圖書 AS B

7、 ON A.作者號=B.作者號 INNER JOIN 出版社 AS C ON B.出版社號=C.出版社號 WHERE 作者名=nameGOEXEC 作者查詢 任明GO18重命名存儲過程格式：sp_rename 存儲過程原名，存儲過程新名【例8.9】將存儲過程“圖書出版情況”更名為“圖書情況”。USE 圖書管理GOsp_rename 圖書出版情況,圖書情況GO19刪除存儲過程格式：DROP PROCEDURE 存儲過程名，【例8.10】使用T-SQL命令刪除存儲過程“圖書情況”。USE 圖書管理GODROP PROCEDURE 圖書情況GO20系統(tǒng)存儲過程系統(tǒng)存儲過程功能sp_addlogin在

8、服務器增加標準登錄名，使用戶可以訪問整個服務器sp_databases列出服務器可用的數(shù)據(jù)庫名，用于尋找數(shù)據(jù)庫sp_depends列出引用或依賴于指定對象的所有對象sp_help列出指定對象的的一般信息sp_helptext顯示指定對象的文本sp_help *是指以sp_help開頭的一組系統(tǒng)存儲過程，這些系統(tǒng)存儲過程提供指定對象的某種特定信息sp_password改變標準登錄的口令sp_stored_procedures列出所有可用的存儲過程名和自定義函數(shù)名sp_tables列出所有可用的表名和視圖名sp_who列出使用數(shù)據(jù)庫的用戶sp_rename改變指定對象的名稱21擴展存儲過程擴展存儲

9、過程功能xp_CmdShell運行在命令提示符下執(zhí)行的命令xp_FileExist測試文件是否存在xp_FixedDrives顯示硬盤的盤符和容量228.5 觸發(fā)器簡介存儲過程可以實現(xiàn)更為復雜的數(shù)據(jù)完整性約束，可以完成使用普通約束無法實現(xiàn)的復雜功能一種特殊類型的存儲過程，當表中的數(shù)據(jù)被修改時，SQL Server自動執(zhí)行觸發(fā)器。23特點1與一個表或視圖相聯(lián)系4是一個事務3不能直接調用2自動激活1與一個表或視圖相聯(lián)系3不能直接調用1與一個表或視圖相聯(lián)系4是一個事務3不能直接調用1與一個表或視圖相聯(lián)系2自動激活4是一個事務3不能直接調用1與一個表或視圖相聯(lián)系24分類系統(tǒng)存儲過程擴展存儲過程臨時存儲

10、過程系統(tǒng)存儲過程臨時存儲過程擴展存儲過程系統(tǒng)存儲過程臨時存儲過程本地存儲過程擴展存儲過程系統(tǒng)存儲過程臨時存儲過程258.2 存儲過程的創(chuàng)建和執(zhí)行格式CREATE PROCEDURE 存儲過程名 參數(shù) 數(shù)據(jù)類型=默認值 OUTPUT ， WITH ENCRYPTION ASSQL語句塊文本加密參數(shù)為輸出類型26優(yōu)點1觸發(fā)器可以強制定義比CHECK約束更為復雜的數(shù)據(jù)完整性約束4觸發(fā)器可以使用自定義的錯誤信息3觸發(fā)器可以比較數(shù)據(jù)庫修改前后的數(shù)據(jù)狀態(tài)，并根據(jù)其差異采取相應的措施2觸發(fā)器可以實現(xiàn)數(shù)據(jù)庫中多個表的級聯(lián)更改27觸發(fā)器的類型AFTER觸發(fā)器INSTEAD OF觸發(fā)器這種類型的觸發(fā)器在數(shù)據(jù)變動（

11、INSERT、DELETEUPDATE操作）完成以后才被觸發(fā)。AFTER觸發(fā)器只能在表上定義，每個表可以創(chuàng)建多個AFTER觸發(fā)器。這種類型的觸發(fā)器在數(shù)據(jù)變動以前被觸發(fā)，并取代變動數(shù)據(jù)的操作（INSERT、DELETE、UPDATE操作），即變動數(shù)據(jù)的操作并不執(zhí)行，而是執(zhí)行觸發(fā)器定義的操作。INSTEAD OF觸發(fā)器可以在表或視圖上定義，每個表或視圖的每INSERT、DELETE、UPDATE操作只能定義一個INSTEAD OF觸發(fā)器。288.6 創(chuàng)建觸發(fā)器格式：CREATE TRIGGER 觸發(fā)器名 ON 表名|視圖名 WITH ENCRYPTION FOR|AFTER|INSTEAD OF

12、INSERT，DELETE，UPDATE AS SQL語句塊29AFTER觸發(fā)器和INSTEAD OF觸發(fā)器系統(tǒng)存儲過程系統(tǒng)存儲過程系統(tǒng)存儲過程DELETE觸發(fā)器INSERT觸發(fā)器UPDATE觸發(fā)器30Inserted表和Deleted表Inserted表：用于保存觸發(fā)器表中被插入的記錄。Deleted表：用于保存觸發(fā)器表中被刪除的記錄。31AFTER觸發(fā)器 INSERT觸發(fā)器INSERT觸發(fā)器的工作過程如下：當向觸發(fā)器表插入數(shù)據(jù)時，INSERT觸發(fā)器被激活；新數(shù)據(jù)行被插入到觸發(fā)器表和臨時表Inserted中；執(zhí)行觸發(fā)器中定義的語句?！纠?.16】請在“借閱”表中創(chuàng)建一個觸發(fā)器“Ins_bor

13、rower_tr1”，當向“借閱”表中插入記錄時，檢查“圖書”表中是否存在相應的圖書，即檢查是否存在相應的“圖書號”， 如果不存在則不允許向“借閱”表中插入記錄，即只有圖書存在才可以借閱此圖書。32-創(chuàng)建INSERT觸發(fā)器USE 圖書管理IF EXISTS (SELECT * FROM sysobjects WHERE name=Ins_borrower_tr1 and type=TR ) DROP TRIGGER Ins_borrower_tr1GOCREATE TRIGGER Ins_borrower_tr1 ON 借閱 FOR INSERTAS IF EXISTS (SELECT 圖書號

14、 FROM Inserted WHERE 圖書號 not IN (SELECT 圖書號 FROM 圖書) ) BEGIN PRINT 抱歉，該圖書不存在，不能借閱！ ROLLBACK TRAN ENDGO33 DELETE觸發(fā)器對觸發(fā)器表執(zhí)行DELETE操作時，DELETE觸發(fā)器被激活；刪除觸發(fā)器表中的記錄，并將被刪除記錄放入臨時表Deleted表中；執(zhí)行觸發(fā)器中定義的語句?！纠?.18】請在“讀者”表中創(chuàng)建一個觸發(fā)器“Del_reader_tr”，當刪除“讀者”表中的某個記錄時，檢查在“借閱”表中是否存在相應的讀者，即檢查是否存在相應的“借書證號”，如果存在則不允許刪除“讀者”表中相應的記錄

15、，即如果讀者借書未還則不能被注銷。34-建立DELETE觸發(fā)器USE 圖書管理IF EXISTS (SELECT * FROM sysobjects WHERE name=Del_reader_tr and type=TR ) DROP TRIGGER Del_reader_trGOCREATE TRIGGER Del_reader_tr ON 讀者 FOR DELETEAS IF EXISTS (SELECT 借書證號 FROM Deleted WHERE 借書證號 IN (SELECT 借書證號 FROM 借閱) ) BEGIN PRINT 抱歉，該讀者借書未還，不能注銷！ ROLLBAC

16、K TRAN ENDGO35 UPDATE觸發(fā)器對觸發(fā)器表執(zhí)行UPDATE操作時，UPDATE觸發(fā)器被激活；更新觸發(fā)器表中的記錄，并將原始記錄放入臨時表Deleted中，將更新后的記錄放入臨時表Inserted表中；執(zhí)行觸發(fā)器中定義的語句。36INSTEAD OF觸發(fā)器37 查看觸發(fā)器的定義信息格式：sp_helptext 觸發(fā)器名【例8.25】查看觸發(fā)器“Update_publishID_tr”的定義信息。USE 圖書管理-下一行語句不是批處理中的第一條語句，因此必須加上“EXEC”EXEC sp_helptext Update_publishID_trGO管理觸發(fā)器38 查看觸發(fā)器的一般信

17、息格式：sp_help 觸發(fā)器名【例8.26】查看觸發(fā)器“Update_publishID_tr”的名稱、所有者、類型以及建立時間。USE 圖書管理GOsp_help Update_publishID_trGO39修改觸發(fā)器格式：ALTER TRIGGER 觸發(fā)器名 ON 表名|視圖名 WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT，DELETE，UPDATE AS SQL語句塊40【例8.23】修改例8.4建立的觸發(fā)器“Del_au_tr”，要求對此觸發(fā)器進行加密，其他要求不變。USE 圖書管理GOALTER TRIGGER Del_au_tr ON

18、作者 WITH ENCRYPTION FOR DELETEAS IF EXISTS (SELECT * FROM Deleted WHERE 作者號 IN (SELECT 作者號 FROM 圖書) ) DELETE 圖書 WHERE 作者號 IN (SELECT 作者號 FROM Deleted)GO41觸發(fā)器使用注意事項 創(chuàng)建觸發(fā)器的語句CREATE TRIGGER必須為批處理中的第一條語句，且在該批處理中，CREATE TRIGGER語句之后不能有其他的語句，否則SQL會將這些語句作為觸發(fā)器定義的一部分，因此創(chuàng)建觸發(fā)器的語句CREATE TRIGGER應作為一個獨立的批處理。 觸發(fā)器不能定

19、義在臨時表和系統(tǒng)表中，但是觸發(fā)器可以引用臨時表。 由于TRUNCATE TABLE語句沒有日志，因此TRUNCATE TABLE語句不會激活DELETE觸發(fā)器。 CREATE TRIGGER語句只能在當前數(shù)據(jù)庫中建立觸發(fā)器，但在觸發(fā)器內可以引用其他數(shù)據(jù)庫中的數(shù)據(jù)庫對象。 在一個觸發(fā)器中可以使用各種SET語句設置連接選項，但它們所設置的連接選項只在觸發(fā)器內有效，當觸發(fā)器執(zhí)行之后，各連接選項恢復到觸發(fā)器激活前的狀態(tài)值。42 觸發(fā)器的主要用途是檢查或修改數(shù)據(jù)，而不是與用戶交互，因此在觸發(fā)器內不要使用能夠返回結果集合的SELECT等語句。 如果在一個觸發(fā)器內包含有ROLLBACK TRANSACTION語句，則：如果激活觸發(fā)器的語句處于一個用戶定義事務之內，觸發(fā)器內的 ROLLBACK TRANSACTION語句執(zhí)行后將回滾整個事務。當