第五章存儲過程、觸發(fā)器和數(shù)據(jù)完整性(2012)

1、第五章第五章 存儲過程、觸發(fā)器和數(shù)據(jù)完整性存儲過程、觸發(fā)器和數(shù)據(jù)完整性12SQL Server 編程結(jié)構(gòu)存儲過程3觸發(fā)器4數(shù)據(jù)庫完整性5.1 SQL Server5.1 SQL Server編程結(jié)構(gòu)編程結(jié)構(gòu)1 1局部變量的聲明格式為：局部變量的聲明格式為： DECLARE DECLARE 局部變量名局部變量名 數(shù)據(jù)類型數(shù)據(jù)類型 , , 局部變量名局部變量名 數(shù)據(jù)類型數(shù)據(jù)類型 例：下面的語句聲明了兩個變量例：下面的語句聲明了兩個變量variable1variable1和和variable2variable2，數(shù)據(jù)類型分別為，數(shù)據(jù)類型分別為intint和和datetimedatetime。 DEC

2、LARE variable1 int, DECLARE variable1 int, variable2 datetime variable2 datetime5.1.1 變量變量注注: :在同一個在同一個DECLAREDECLARE語句中，可以同時定義多個變量，變語句中，可以同時定義多個變量，變量之間用逗號隔開。量之間用逗號隔開。 2 2為局部變量賦值可以采用為局部變量賦值可以采用SETSET語句或語句或SELECTSELECT語句：語句：l SET SET 變量名變量名= =表達(dá)式表達(dá)式l SELECT SELECT 變量名變量名= =表達(dá)式表達(dá)式l SELECT SELECT 列列1,1

3、, ,列列n n 變量名變量名= =表達(dá)式表達(dá)式 FROM FROM 表名表名 WHERE WHERE 條件表達(dá)式條件表達(dá)式5.1.1 5.1.1 變量變量注注:1):1)如果如果SELECTSELECT語句返回多個數(shù)值，則局部變量取最后一個語句返回多個數(shù)值，則局部變量取最后一個返回值。返回值。 2)SELECT2)SELECT語句的賦值功能和查詢功能不能混合使用，否則語句的賦值功能和查詢功能不能混合使用，否則系統(tǒng)會產(chǎn)生錯誤信息。系統(tǒng)會產(chǎn)生錯誤信息。 5.1.2 5.1.2 顯示信息顯示信息1 1PRINTPRINT語句語句注意：使用注意：使用PRINTPRINT語句只能顯示字符數(shù)據(jù)類型。語句

4、只能顯示字符數(shù)據(jù)類型。2 2RAISERRORRAISERROR語句語句語法如下：語法如下：RAISERROR (RAISERROR (| | , , 嚴(yán)重度嚴(yán)重度, , 狀態(tài)狀態(tài), , 參數(shù)參數(shù)1, 1, 參數(shù)參數(shù)2)2)5.1.3 5.1.3 注釋語句注釋語句語法為：語法為： / /* *注釋文本注釋文本* */ / 或或 - - 注釋文本注釋文本 5.1.4 5.1.4 批處理批處理 批處理是成組執(zhí)行的一條或多條批處理是成組執(zhí)行的一條或多條T-SQLT-SQL指令指令，被作，被作為整體進(jìn)行語法分析、優(yōu)化、編譯和執(zhí)行。如果為整體進(jìn)行語法分析、優(yōu)化、編譯和執(zhí)行。如果批處理的任何部分在語法上不

5、正確，或批處理參批處理的任何部分在語法上不正確，或批處理參照的對象不存在，則整個批處理無法執(zhí)行。照的對象不存在，則整個批處理無法執(zhí)行。 GOGO語句語句用于指定批處理語句的結(jié)束處，單獨(dú)占用用于指定批處理語句的結(jié)束處，單獨(dú)占用一行。一行。GOGO本身并不是本身并不是T-SQLT-SQL語句的組成部分，它只語句的組成部分，它只是一個用于表示批處理結(jié)束的前端指令。是一個用于表示批處理結(jié)束的前端指令。 注意：注意：（1 1）不能在同一個批處理中刪除數(shù)據(jù)庫對象（表、視圖或）不能在同一個批處理中刪除數(shù)據(jù)庫對象（表、視圖或存儲過程等），然后又引用或重新創(chuàng)建它們。存儲過程等），然后又引用或重新創(chuàng)建它們。（2

6、2）不能在同一個批處理中，修改表的列后又引用它。）不能在同一個批處理中，修改表的列后又引用它。（3 3）用）用SETSET語句設(shè)置的選項只在批處理結(jié)束時才使用，可以語句設(shè)置的選項只在批處理結(jié)束時才使用，可以將將SETSET語句與查詢在批處理中組合起來，但有些語句與查詢在批處理中組合起來，但有些SETSET選項不選項不能在批處理中使用。能在批處理中使用。5.1.5 5.1.5 流程控制語句流程控制語句1 1BEGINBEGINENDEND語句語句語法形式如下：語法形式如下： BEGINBEGIN 語句語句 ENDEND2 2IFIFELSEELSE語句語句語法形式如下：語法形式如下： IF IF

7、 條件表達(dá)式條件表達(dá)式 語句語句 ELSE IFELSE IF條件表達(dá)式條件表達(dá)式 語句語句 執(zhí)行過程為：執(zhí)行過程為：如果條件表達(dá)如果條件表達(dá)式為真，則執(zhí)行式為真，則執(zhí)行IFIF后面的后面的語句或語句塊，如果條件語句或語句塊，如果條件表達(dá)式為假，則執(zhí)行表達(dá)式為假，則執(zhí)行ELSEELSE后面的語句或語句塊。后面的語句或語句塊。 【例例5.15.1】在電力搶修工程數(shù)據(jù)庫中，如果在電力搶修工程數(shù)據(jù)庫中，如果stockstock表表中存在庫存量低于中存在庫存量低于1 1的物資，就顯示文本：的物資，就顯示文本：the the amount is not enoughamount is not enoug

8、h；否則顯示所有物資信息。；否則顯示所有物資信息。5.1.5 5.1.5 流程控制語句流程控制語句IF exists(SELECT * FROM stock where amount1) PRINT the amount is not enough! ELSE BEGIN SELECT * FROM stock END 注意：注意：IFIF語句常與關(guān)鍵字子語句常與關(guān)鍵字子EXISTSEXISTS結(jié)合使用，用于檢測是否存在滿結(jié)合使用，用于檢測是否存在滿足條件的記錄，只要檢測到有一行記錄存在，就為真。足條件的記錄，只要檢測到有一行記錄存在，就為真。3 3WHILEWHILE循環(huán)語句循環(huán)語句 語法形

9、式如下：語法形式如下： WHILE WHILE 邏輯表達(dá)式邏輯表達(dá)式 語句語句【例例5.25.2】將將stockstock表中所有物資單價增加表中所有物資單價增加10%10%，直到有一個，直到有一個物資單價超過物資單價超過1500015000或單價總和超過或單價總和超過5000050000為止。為止。WHILE (SELECT sum(unit) FROM stock)50000WHILE (SELECT sum(unit) FROM stock)15000) FROM stock WHERE unit15000) break break ELSE ELSE continue continue

10、 END END5.1.5 5.1.5 流程控制語句流程控制語句4 4GOTOGOTO語句語句語法形式如下：語法形式如下： GOTO lableGOTO lable lable: lable:6 6WAITFORWAITFOR語句語句 語法形式如下：語法形式如下： WAITFOR DELAY WAITFOR DELAY 時間時間 | TIME | TIME 時間時間 其中，其中，DELAYDELAY表示等候由表示等候由“時間時間”參數(shù)指定的參數(shù)指定的時間間隔，時間間隔，TIMETIME表示等候到指定的表示等候到指定的“時間時間”為止。為止。時間參數(shù)的數(shù)據(jù)類型為時間參數(shù)的數(shù)據(jù)類型為datetim

11、edatetime，但不帶日期，但不帶日期，格式為格式為hh:mm:sshh:mm:ss。5.1.5 5.1.5 流程控制語句流程控制語句5 5RETURNRETURN語句語句語法格式為：語法格式為： RETURN RETURN 整型表達(dá)式整型表達(dá)式【例例5.35.3】使用使用WAITFORWAITFOR語句表示等待一分鐘后，顯示語句表示等待一分鐘后，顯示stockstock表。等到中午表。等到中午12:00:0012:00:00時，顯示時，顯示salvagingsalvaging表。表。WAITFOR DELAYWAITFOR DELAY 00:01:00 00:01:00 SELECT S

12、ELECT * * FROM stock FROM stockWAITFOR TIMEWAITFOR TIME 12:00:00 12:00:00 SELECT SELECT * * FROM salvaging FROM salvaging5.1.5 5.1.5 流程控制語句流程控制語句7.CASE7.CASE語句語句(1) (1) 格式一格式一 CASE CASE WHEN WHEN THEN THEN 1 WHEN WHEN THEN THEN 2 ELSE ELSE n END END【例例5.45.4】用用CASECASE語句格式一實現(xiàn)：在對語句格式一實現(xiàn)：在對stockstock表

13、的查詢中，當(dāng)倉表的查詢中，當(dāng)倉庫號的值是庫號的值是“供電局供電局1 1號倉庫號倉庫”、“供電局供電局2 2號倉庫號倉庫”、“供電供電局局3 3號倉庫號倉庫”時分別返回時分別返回“北京北京”、“上海上?！薄ⅰ皬V州廣州”，否，否則返回則返回“未知未知”。SELECT mat_num,mat_name,speci,amount,unit,total, SELECT mat_num,mat_name,speci,amount,unit,total, warehouse warehouse=CASE=CASE warehouse warehouse WHEN WHEN 供電局供電局1#1#倉庫倉庫THE

14、N THEN 北京北京 WHEN WHEN 供電局供電局2#2#倉庫倉庫THEN THEN 上海上海 WHEN WHEN 供電局供電局3#3#倉庫倉庫THEN THEN 廣州廣州 ELSE ELSE 未知未知 ENDEND FROM stockFROM stock(2) (2) 格式二格式二CASECASE WHEN WHEN THEN THEN 1 WHEN WHEN THEN THEN 2 ELSE ELSE nENDEND【例例5.55.5】用用CASECASE語句格式二實現(xiàn)：在對語句格式二實現(xiàn)：在對stockstock表的查詢中，當(dāng)表的查詢中，當(dāng)倉庫號的值是倉庫號的值是“供電局供電局1

15、 1號倉庫號倉庫”、“供電局供電局2 2號倉庫號倉庫”、“供電局供電局3 3號倉庫號倉庫”時分別返回時分別返回“北京北京”、“上海上?！?、“廣廣州州”，否則返回，否則返回“未知未知”。SELECT mat_num,mat_name,speci, amount,unit,total, SELECT mat_num,mat_name,speci, amount,unit,total, warehouse warehouse =CASE=CASE WHEN warehouse= WHEN warehouse=供電局供電局1#1#倉庫倉庫THEN THEN 北京北京 WHEN warehouse= W

16、HEN warehouse=供電局供電局2#2#倉庫倉庫THEN THEN 上海上海 WHEN warehouse= WHEN warehouse=供電局供電局3#3#倉庫倉庫THEN THEN 廣州廣州 ELSE ELSE 未知未知 END END FROM stockFROM stock5.2 5.2 存儲過程存儲過程5.2.15.2.1存儲過程的基本概念存儲過程的基本概念 存儲在數(shù)據(jù)庫服務(wù)器中的存儲在數(shù)據(jù)庫服務(wù)器中的一組編譯成單個執(zhí)一組編譯成單個執(zhí)行計劃的行計劃的SQLSQL語句語句。在使用。在使用Transact-SQLTransact-SQL語言編程語言編程的過程中，可以將某些需要多

17、次調(diào)用以實現(xiàn)某個的過程中，可以將某些需要多次調(diào)用以實現(xiàn)某個特定任務(wù)的代碼段編寫成一個過程，將其保存在特定任務(wù)的代碼段編寫成一個過程，將其保存在數(shù)據(jù)庫中，并由數(shù)據(jù)庫中，并由SQL ServerSQL Server服務(wù)器通過過程名調(diào)服務(wù)器通過過程名調(diào)用，稱為存儲過程。用，稱為存儲過程。 優(yōu)點：優(yōu)點：1)1)運(yùn)行效率高，提供了在服務(wù)器端快速執(zhí)行運(yùn)行效率高，提供了在服務(wù)器端快速執(zhí)行SQLSQL語句的有效途徑。語句的有效途徑。 2)2)降低了客戶機(jī)和服務(wù)器之間的通信量。降低了客戶機(jī)和服務(wù)器之間的通信量。 3)3)方便實施企業(yè)規(guī)則。方便實施企業(yè)規(guī)則。 存儲過程和非存儲過程操作示意存儲過程和非存儲過程操作示

18、意 5.2.1 存儲過程的基本概念存儲過程的基本概念5.2.2 5.2.2 創(chuàng)建存儲過程創(chuàng)建存儲過程 創(chuàng)建存儲過程的創(chuàng)建存儲過程的SQLSQL語句格式為：語句格式為： CREATE PROCEDURE CREATE PROCEDURE 存儲過程名存儲過程名 ；版本號；版本號 參數(shù)參數(shù) 數(shù)據(jù)類型數(shù)據(jù)類型 VARYING = VARYING = 默認(rèn)值默認(rèn)值 OUTPUT , OUTPUT , WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICA

19、TION FOR REPLICATION AS AS SQL SQL語句語句1 1基本存儲過程基本存儲過程【例例5.65.6】創(chuàng)建一個最簡單的存儲過程，用于返回創(chuàng)建一個最簡單的存儲過程，用于返回stockstock表中的所有記錄。表中的所有記錄。 CREATE PROCEDURE exp1CREATE PROCEDURE exp1 AS AS SELECT SELECT * * FROM stock FROM stock5.2.2 5.2.2 創(chuàng)建存儲過程創(chuàng)建存儲過程執(zhí)行存儲過程執(zhí)行存儲過程: :EXECUTE EXECUTE = = 存儲過程名存儲過程名 = = | 執(zhí)行執(zhí)行exp1exp1

20、: : EXECUTE exp1EXECUTE exp1或者：或者： EXEC exp1EXEC exp12 2帶參數(shù)的存儲過程帶參數(shù)的存儲過程【例例5.75.7】創(chuàng)建一個存儲過程，通過輸入的倉庫名稱創(chuàng)建一個存儲過程，通過輸入的倉庫名稱顯示出該倉庫的所有庫存物資信息。顯示出該倉庫的所有庫存物資信息。 CREATE PROCEDURE exp2 CREATE PROCEDURE exp2 ckmc varchar(50)ckmc varchar(50) AS AS SELECT SELECT * * FROM stock FROM stock WHERE warehouse WHERE ware

21、house=ckmc=ckmc5.2.2 5.2.2 創(chuàng)建存儲過程創(chuàng)建存儲過程【例例5.85.8】創(chuàng)建一個帶輸入?yún)?shù)的存儲過程，向創(chuàng)建一個帶輸入?yún)?shù)的存儲過程，向stockstock表中添加一個表中添加一個新的數(shù)據(jù)行。新的數(shù)據(jù)行。 CREATE PROCEDURE exp3CREATE PROCEDURE exp3 mno char(8), mname varchar(50), mspeci varchar(20) mno char(8), mname varchar(50), mspeci varchar(20) ASAS INSERT INSERT INTO stock(mat_num,m

22、at_name,speci) INTO stock(mat_num,mat_name,speci) VALUES VALUES(mno,mname,mspeci)(mno,mname,mspeci)執(zhí)行該存儲過程：執(zhí)行該存儲過程： EXECUTE exp3 m030,EXECUTE exp3 m030,護(hù)套絕緣電線護(hù)套絕緣電線,BVV-35,BVV-35或者：或者： EXECUTE exp3 mno=m030, mname=EXECUTE exp3 mno=m030, mname=護(hù)套絕緣電線護(hù)套絕緣電線, , mspeci=BVV-35mspeci=BVV-35或者：或者： EXECUTE

23、exp3 mname=EXECUTE exp3 mname=護(hù)套絕緣電線護(hù)套絕緣電線, mspeci=BVV-35, , mspeci=BVV-35, mno=m030mno=m030注意注意: : 為了確保為了確保CreateCreate命令能成功執(zhí)行，可以在命令能成功執(zhí)行，可以在Create Create ProcedureProcedure之前執(zhí)行如下語句：之前執(zhí)行如下語句：IF EXISTS (SELECT name FROM sysobjects WHERE IF EXISTS (SELECT name FROM sysobjects WHERE name=exp3 and type

24、=P)name=exp3 and type=P) DROP PROCEDURE exp3 DROP PROCEDURE exp3 GO GO 5.2.2 5.2.2 創(chuàng)建存儲過程創(chuàng)建存儲過程3 3帶默認(rèn)參數(shù)的存儲過程帶默認(rèn)參數(shù)的存儲過程【例例5.95.9】創(chuàng)建一個帶默認(rèn)參數(shù)的存儲過程，通過傳遞的參創(chuàng)建一個帶默認(rèn)參數(shù)的存儲過程，通過傳遞的參數(shù)顯示物資的名稱、規(guī)格、項目名稱、是否按期完工等信數(shù)顯示物資的名稱、規(guī)格、項目名稱、是否按期完工等信息，如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值。息，如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值。CREATE PROCEDURE exp4 CREATE PROCEDURE

25、 exp4 mname varchar(50)=%mname varchar(50)=%絕緣絕緣%, pno char(8)=20110001%, pno char(8)=20110001 AS AS SELECT mat_name,speci,prj_name,prj_status SELECT mat_name,speci,prj_name,prj_status FROM stock, salvaging, out_stock FROM stock, salvaging, out_stock WHERE stock.mat_num=out_stock.mat_num WHERE stock

26、.mat_num=out_stock.mat_num and salvaging.prj_num=out_stock.prj_num and salvaging.prj_num=out_stock.prj_num and and mat_name like mname mat_name like mname and and salvaging.prj_num=pnosalvaging.prj_num=pno5.2.2 5.2.2 創(chuàng)建存儲過程創(chuàng)建存儲過程執(zhí)行創(chuàng)建的存儲過程執(zhí)行創(chuàng)建的存儲過程exp4exp4。 EXECUTE exp4 EXECUTE exp4 或者：或者： EXECUTE ex

27、p4 %EXECUTE exp4 %絕緣電線絕緣電線 或者：或者： EXECUTE exp4 pno=20110001EXECUTE exp4 pno=20110001或者：或者： EXECUTE exp4 EXECUTE exp4 護(hù)套絕緣電線護(hù)套絕緣電線 ,20110001,201100014 4帶輸出參數(shù)的存儲過程帶輸出參數(shù)的存儲過程【例例5.105.10】創(chuàng)建一個存儲過程，求某個搶修工程領(lǐng)創(chuàng)建一個存儲過程，求某個搶修工程領(lǐng)取物資的總數(shù)量。取物資的總數(shù)量。CREATE PROCEDURE sum_mat CREATE PROCEDURE sum_mat pn char(8), pn ch

28、ar(8), sum int sum int OUTPUTOUTPUT ASAS SELECT SELECT sum=sum(amount)sum=sum(amount) FROM out_stock FROM out_stock WHERE prj_num WHERE prj_num=pn=pn5.2.2 5.2.2 創(chuàng)建存儲過程創(chuàng)建存儲過程執(zhí)行：執(zhí)行： DECLARE total int DECLARE total int EXECUTE sum_mat 20110001, total OUTPUT EXECUTE sum_mat 20110001, total OUTPUT PRINT

29、PRINT 該項目領(lǐng)取物資總量為：該項目領(lǐng)取物資總量為：+ CAST(total AS + CAST(total AS varchar(20) varchar(20) 5.2.4 5.2.4 修改和刪除存儲過程修改和刪除存儲過程修改存儲過程的語句是：修改存儲過程的語句是：ALTER PROCEDURE ALTER PROCEDURE 存儲過程名存儲過程名 ；版本號；版本號 參數(shù)參數(shù) 數(shù)據(jù)類型數(shù)據(jù)類型 VARYING = VARYING =默認(rèn)值默認(rèn)值 OUTPUT , OUTPUT , WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION WITH RE

30、COMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION FOR REPLICATION AS AS SQL SQL語句語句刪除存儲過程的語句是：刪除存儲過程的語句是： DROP PROCEDURE DROP PROCEDURE 存儲過程名存儲過程名5.3 5.3 觸發(fā)器觸發(fā)器5.3.1 5.3.1 觸發(fā)器的基本概念觸發(fā)器的基本概念 用戶定義在關(guān)系表上的用戶定義在關(guān)系表上的一類由事件驅(qū)動的特一類由事件驅(qū)動的特殊過程殊過程，是一種保證數(shù)據(jù)完整性的方法，是一種保證數(shù)據(jù)完整性的方法, ,也可看作也可看作是是一類特殊的存儲過程一類特殊的存儲過程，一

31、旦定義，無須用戶調(diào)一旦定義，無須用戶調(diào)用，任何對表的修改操作均由服務(wù)器自動激活相用，任何對表的修改操作均由服務(wù)器自動激活相應(yīng)的觸發(fā)器應(yīng)的觸發(fā)器。 主要作用主要作用: :實現(xiàn)主鍵和外鍵所不能保證的復(fù)雜實現(xiàn)主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)一致性。除此之外還有以下的參照完整性和數(shù)據(jù)一致性。除此之外還有以下幾個功能：幾個功能：1 1強(qiáng)化約束強(qiáng)化約束; ;2 2跟蹤變化跟蹤變化; ;3 3級聯(lián)運(yùn)行級聯(lián)運(yùn)行; ;4 4存儲過程的調(diào)用。存儲過程的調(diào)用。 5.3.2 5.3.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器定義觸發(fā)器的語句是：定義觸發(fā)器的語句是：CREATE TRIGGER CREATE TRIGGER

32、ON ON 表名表名 | | 視圖名視圖名 WITH ENCRYPTION WITH ENCRYPTION FOR|AFTER|INSTEAD OFINSERT,UPDATE, FOR|AFTER|INSTEAD OFINSERT,UPDATE, DELETEDELETE NOT FOR REPLICATION NOT FOR REPLICATION ASAS SQL SQL 語句語句 1.INSERT 1.INSERT 觸發(fā)器觸發(fā)器l該觸發(fā)器在每次往基本表中插入數(shù)據(jù)時觸發(fā)執(zhí)該觸發(fā)器在每次往基本表中插入數(shù)據(jù)時觸發(fā)執(zhí)行，該數(shù)據(jù)同時復(fù)制到基本表和內(nèi)存中的行，該數(shù)據(jù)同時復(fù)制到基本表和內(nèi)存中的INSE

33、RTEDINSERTED表中。表中。l INSERTINSERT觸發(fā)器主要有三個作用：檢驗要輸入的觸發(fā)器主要有三個作用：檢驗要輸入的數(shù)據(jù)是否符合規(guī)則、在插入的數(shù)據(jù)中增加數(shù)據(jù)、數(shù)據(jù)是否符合規(guī)則、在插入的數(shù)據(jù)中增加數(shù)據(jù)、級聯(lián)改變數(shù)據(jù)庫中其他的數(shù)據(jù)表。級聯(lián)改變數(shù)據(jù)庫中其他的數(shù)據(jù)表。lINSERTEDINSERTED表中用于存儲表中用于存儲INSERTINSERT和和UPDATEUPDATE語句所語句所影響的行的復(fù)本，執(zhí)行影響的行的復(fù)本，執(zhí)行INSERTINSERT和和UPDATEUPDATE語句時，語句時，新的數(shù)據(jù)行被添加到基本表中，同時這些數(shù)據(jù)新的數(shù)據(jù)行被添加到基本表中，同時這些數(shù)據(jù)行的備份被復(fù)制

34、到行的備份被復(fù)制到INSERTEDINSERTED臨時表中。臨時表中。5.3.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器【例例5.115.11】創(chuàng)建一個創(chuàng)建一個INSERTINSERT觸發(fā)器，在對表觸發(fā)器，在對表stockstock進(jìn)行插入后，輸出所影響的行數(shù)信息。進(jìn)行插入后，輸出所影響的行數(shù)信息。CREATE TRIGGER tr1_stockCREATE TRIGGER tr1_stockON stockON stockFOR INSERT FOR INSERT ASAS PRINT( PRINT(所影響的行數(shù)為所影響的行數(shù)為:+ cast(rowcount :+ cast(rowcount as varc

35、har(10)+as varchar(10)+行行) 5.3.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器觸發(fā)器觸發(fā)器tr1_stocktr1_stock創(chuàng)建后，當(dāng)往創(chuàng)建后，當(dāng)往stockstock表中插入表中插入1 1行新的行新的數(shù)據(jù)時，數(shù)據(jù)庫服務(wù)器會輸出如下信息：數(shù)據(jù)時，數(shù)據(jù)庫服務(wù)器會輸出如下信息： ( (所影響的行數(shù)為：所影響的行數(shù)為：1 1行行) )【例例5.125.12】創(chuàng)建一個創(chuàng)建一個INSERTINSERT觸發(fā)器，在對表觸發(fā)器，在對表stockstock進(jìn)行插入后，進(jìn)行插入后，驗證庫存量的大小，庫存量小于驗證庫存量的大小，庫存量小于1 1，則撤銷該插入操作。，則撤銷該插入操作。CREATE TRIG

36、GER tr2_stockCREATE TRIGGER tr2_stock ON stock ON stock FOR INSERT FOR INSERTAS AS DECLARE amount intDECLARE amount int SELECT amount=amount SELECT amount=amount FROM INSERTED FROM INSERTED IF amount1 IF amount=1=1符合規(guī)則，可以正常插入執(zhí)行。符合規(guī)則，可以正常插入執(zhí)行。l INSERT INSERT INTO stock(mat_num,mat_name,speci,warehous

37、e, INTO stock(mat_num,mat_name,speci,warehouse, amount,unit) amount,unit) VALUES(m031, VALUES(m031,護(hù)套絕緣電線護(hù)套絕緣電線,BVV-120,BVV-120,供電局供電局1#1#倉倉庫庫,0,100),0,100) 由于庫存量由于庫存量11不符合規(guī)則，將撤銷表的插入操作：不符合規(guī)則，將撤銷表的插入操作：5.3.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器2.DELETE2.DELETE觸發(fā)器觸發(fā)器 該觸發(fā)器在從基本表中刪除數(shù)據(jù)時觸發(fā)執(zhí)行，在該觸發(fā)器在從基本表中刪除數(shù)據(jù)時觸發(fā)執(zhí)行，在用戶執(zhí)行了用戶執(zhí)行了DELETEDE

38、LETE觸發(fā)器后，觸發(fā)器后，SQL ServerSQL Server將刪除將刪除的數(shù)據(jù)行保存在的數(shù)據(jù)行保存在DELETEDDELETED表中，即數(shù)據(jù)行并沒有消表中，即數(shù)據(jù)行并沒有消失，還可在失，還可在SQLSQL語句中引用。語句中引用。 DELETEDELETE觸發(fā)器主要用于以下兩種情況：防止刪除觸發(fā)器主要用于以下兩種情況：防止刪除數(shù)據(jù)庫中的某些數(shù)據(jù)行、級聯(lián)刪除數(shù)據(jù)庫中其他數(shù)據(jù)庫中的某些數(shù)據(jù)行、級聯(lián)刪除數(shù)據(jù)庫中其他表中的數(shù)據(jù)行。表中的數(shù)據(jù)行。 DELETEDDELETED表用于存儲表用于存儲DELETEDELETE和和UPDATEUPDATE語句所影響語句所影響的行的復(fù)本。在執(zhí)行的行的復(fù)本。在

39、執(zhí)行DELETEDELETE或或UPDATEUPDATE語句時，行語句時，行從觸發(fā)器表中刪除，并傳輸?shù)綇挠|發(fā)器表中刪除，并傳輸?shù)紻ELETEDDELETED表中，表中，DELETEDDELETED表和原數(shù)據(jù)表通常沒有相同的行。表和原數(shù)據(jù)表通常沒有相同的行。 5.3.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器【例例5.135.13】創(chuàng)建一個創(chuàng)建一個DELETEDELETE觸發(fā)器，當(dāng)用戶從觸發(fā)器，當(dāng)用戶從stockstock表中刪表中刪除數(shù)據(jù)時，同時將除數(shù)據(jù)時，同時將out_stockout_stock表中相關(guān)物資的出庫情況一表中相關(guān)物資的出庫情況一并刪除。并刪除。CREATE TRIGGER tr3_stockC

40、REATE TRIGGER tr3_stock ON stock ON stock FOR DELETE FOR DELETEASAS BEGIN TRANSACTION BEGIN TRANSACTION DECLARE mat_num char(8) DECLARE mat_num char(8) SELECT mat_num=mat_num SELECT mat_num=mat_num FROM DELETED FROM DELETED DELETE DELETE FROM out_stock FROM out_stock WHERE mat_num=mat_num WHERE mat_

41、num=mat_num COMMIT TRANSACTION COMMIT TRANSACTION注意：使用觸發(fā)器作級聯(lián)刪除注意：使用觸發(fā)器作級聯(lián)刪除, ,前提是前提是out_stockout_stock表沒有定義和表沒有定義和stockstock表相表相關(guān)的外鍵。關(guān)的外鍵。修改上述觸發(fā)器，使觸發(fā)器適合刪除了多條記錄的修改上述觸發(fā)器，使觸發(fā)器適合刪除了多條記錄的情況：情況：CREATE TRIGGER tr3_stockCREATE TRIGGER tr3_stock ON stock ON stock FOR DELETE FOR DELETEASAS DELETE DELETE FROM

42、out_stock FROM out_stock WHERE mat_num in WHERE mat_num in ( select mat_num from deleted) ( select mat_num from deleted)3.UPDATE3.UPDATE觸發(fā)器觸發(fā)器 該觸發(fā)器在用戶發(fā)出該觸發(fā)器在用戶發(fā)出UPDATEUPDATE語句后觸發(fā)執(zhí)行，即語句后觸發(fā)執(zhí)行，即為用戶修改數(shù)據(jù)行增加限制規(guī)則。為用戶修改數(shù)據(jù)行增加限制規(guī)則。 UPDATEUPDATE觸發(fā)器合并了觸發(fā)器合并了DELETEDELETE觸發(fā)器和觸發(fā)器和INSERTINSERT觸發(fā)觸發(fā)器的作用。器的作用。 在用戶執(zhí)行了在

43、用戶執(zhí)行了UPDATEUPDATE語句后，原來的數(shù)據(jù)行從基語句后，原來的數(shù)據(jù)行從基本表中刪除，但保存在本表中刪除，但保存在DELETEDDELETED表中，同時基本表表中，同時基本表更新后的新數(shù)據(jù)行也在更新后的新數(shù)據(jù)行也在INSERTEDINSERTED表中保存了一個表中保存了一個副本。副本。 可利用可利用DELETEDDELETED表和表和INSERTEDINSERTED表，獲取更新前后的表，獲取更新前后的數(shù)據(jù)行，完成比較操作。數(shù)據(jù)行，完成比較操作。 5.3.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器【例例5.145.14】創(chuàng)建一個創(chuàng)建一個UPDATEUPDATE觸發(fā)器，當(dāng)用戶更新觸發(fā)器，當(dāng)用戶更新stock

44、stock表中的數(shù)據(jù)時，從表中的數(shù)據(jù)時，從INSERTEDINSERTED表中讀取修改的表中讀取修改的新的新的amountamount值，如果該值小于值，如果該值小于1 1，將撤銷更新操作；，將撤銷更新操作；觸發(fā)器從觸發(fā)器從DELETEDDELETED表中查詢中修改前的值，將其重表中查詢中修改前的值，將其重新更新到新更新到stockstock表中。表中。5.3.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器CREATE TRIGGER tr4_stock ON stock FOR UPDATEAS DECLARE amount_new int,amount_old int, mat_num char(10) SEL

45、ECT amount_new=amount,mat_num=mat_num FROM INSERTED IF amount_new1 BEGIN SELECT amount_old=amount FROM DELETED UPDATE stock set amount=amount_old WHERE mat_num=mat_num PRINT the row can not be UPDATED! END 【例例5.155.15】修改前面創(chuàng)建的修改前面創(chuàng)建的UPDATEUPDATE觸發(fā)器，使其先觸發(fā)器，使其先檢測更新的列，當(dāng)更新檢測更新的列，當(dāng)更新warehousewarehouse列時，禁

46、止更新；列時，禁止更新；當(dāng)更新庫存量當(dāng)更新庫存量amountamount列時，設(shè)置更新規(guī)則，若更列時，設(shè)置更新規(guī)則，若更新后的值小于新后的值小于1 1，則撤銷該更新操作。，則撤銷該更新操作。 5.3.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器CREATE TRIGGER tr5_stock ON stock FOR UPDATECREATE TRIGGER tr5_stock ON stock FOR UPDATEASAS DECLARE amount int DECLARE amount int IF UPDATE(warehouse) IF UPDATE(warehouse) BEGIN BEGIN ROL

47、LBACK TRAN ROLLBACK TRAN PRINT PRINT 不允許修改物資存放倉庫！不允許修改物資存放倉庫！ END END IF UPDATE(amount) IF UPDATE(amount) BEGIN BEGIN SELECT amount=amount SELECT amount=amount FROM INSERTED FROM INSERTED IF amount1 IF amount1 BEGIN BEGIN ROLLBACK TRAN ROLLBACK TRAN PRINT PRINT 庫存量小于庫存量小于1 1，不允許更新！，不允許更新！ END END EN

48、D END 例：審計表：對學(xué)生數(shù)據(jù)庫，當(dāng)用戶修改成績時，系統(tǒng)自動記錄如下信息：修改前的成績、修改后的成績，學(xué)號、課號、修改該記錄的用戶名，修改的日期時間。 創(chuàng)建一個審計表audit(user_name,date,sno,cno,new_grade,old_grade) 當(dāng)修改成績時: update sc set grade=90 where . 自動往audit表中添加一條記錄。Create trigger audit_sc on sc after updateAs begin declare new_grade int,old_grade int declare sno char(8),cn

49、o char(8) select sno=sno,cno=cno,new_grade=grade from inserted select old_grade=grade from deleted insert into audit(user_name,date,sno,cno,new_grade,old_grade) values(username(),getdate(),sno,cno,new_grade,old_grade)end4. INSTEAD OF觸發(fā)器觸發(fā)器 INSTEAD OFINSTEAD OF觸發(fā)器為觸發(fā)器為替代操作觸發(fā)器替代操作觸發(fā)器，用于視圖，用于視圖操作。因為視圖有

50、時顯示的是表中的部分列，因操作。因為視圖有時顯示的是表中的部分列，因此用視圖修改基本表中的數(shù)據(jù)行時有可能導(dǎo)致失此用視圖修改基本表中的數(shù)據(jù)行時有可能導(dǎo)致失敗。解決方法之一就是針對視圖建立敗。解決方法之一就是針對視圖建立INSTEAD OFINSTEAD OF觸發(fā)器，通過觸發(fā)器插入所缺的列值，完成更新。觸發(fā)器，通過觸發(fā)器插入所缺的列值，完成更新。 當(dāng)視圖執(zhí)行到對基本表的插入、刪除和更新操作當(dāng)視圖執(zhí)行到對基本表的插入、刪除和更新操作時，用觸發(fā)器的操作替代視圖的操作。時，用觸發(fā)器的操作替代視圖的操作。 注意：視圖只能使用注意：視圖只能使用INSTEAD OFINSTEAD OF觸發(fā)器，而不能觸發(fā)器，而

51、不能直接使用直接使用INSERTINSERT、UPDATEUPDATE和和DELETEDELETE觸發(fā)器。觸發(fā)器。 5.3.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器【例例5.165.16】創(chuàng)建一個創(chuàng)建一個INSTEAD OFINSTEAD OF觸發(fā)器，在視圖往基本表中觸發(fā)器，在視圖往基本表中插入數(shù)據(jù)行時，補(bǔ)充插入數(shù)據(jù)行時，補(bǔ)充mat_nummat_num的列值。的列值。 5.3.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器首先生成基于首先生成基于stockstock表的視圖表的視圖view_stockview_stock，代碼如下：，代碼如下：CREATE VIEW view_stockCREATE VIEW view_stoc

52、kASAS SELECT mat_name,speci,warehouse,amount,unit SELECT mat_name,speci,warehouse,amount,unit FROM stock FROM stock 若通過下面的語句向基本表中插入數(shù)據(jù)若通過下面的語句向基本表中插入數(shù)據(jù): :INSERT INTO view_stockINSERT INTO view_stockVALUES(VALUES(護(hù)套絕緣電線護(hù)套絕緣電線,BVV-120,BVV-120,供電局供電局1#1#倉庫倉庫,10,110),10,110) 由于視圖中不包括由于視圖中不包括mat_nummat_nu

53、m列，而基本表中主鍵列，而基本表中主鍵mat_nummat_num不能為空，則該語句會出現(xiàn)錯誤。不能為空，則該語句會出現(xiàn)錯誤。解決辦法：解決辦法： 創(chuàng)建一個創(chuàng)建一個INSTEAD OFINSTEAD OF觸發(fā)器，在通過視圖往基本表觸發(fā)器，在通過視圖往基本表中插入數(shù)據(jù)時，補(bǔ)充中插入數(shù)據(jù)時，補(bǔ)充mat_nummat_num列的值。列的值。CREATE TRIGGER tr_viewstock ON view_stockCREATE TRIGGER tr_viewstock ON view_stockINSTEAD OF INSERT INSTEAD OF INSERT ASAS DECLARE m

54、at_num char(10),mat_name char(50), DECLARE mat_num char(10),mat_name char(50),speci char(50),warehouse char(50), amount int,speci char(50),warehouse char(50), amount int,unit decimal(18,2)unit decimal(18,2) SELECT mat_name=mat_name,speci=speci,SELECT mat_name=mat_name,speci=speci, warehouse=warehous

55、e,amount=amount,unit=unit warehouse=warehouse,amount=amount,unit=unit FROM INSERTED FROM INSERTED SET mat_num=m040 SET mat_num=m040 INSERT INTO INSERT INTO stock(mat_num,mat_name,speci,warehouse,amount,unit) stock(mat_num,mat_name,speci,warehouse,amount,unit) VALUES(mat_num,mat_name,speci,warehouse,

56、amount,VALUES(mat_num,mat_name,speci,warehouse,amount,unit)unit)5. 5. 復(fù)合觸發(fā)器復(fù)合觸發(fā)器 多個觸發(fā)器可以組合在一起形成復(fù)合觸發(fā)器。多個觸發(fā)器可以組合在一起形成復(fù)合觸發(fā)器。【例例5.175.17】創(chuàng)建一個復(fù)合觸發(fā)器，不允許修改或刪除存儲在供創(chuàng)建一個復(fù)合觸發(fā)器，不允許修改或刪除存儲在供電局電局1#1#倉庫的物資信息。倉庫的物資信息。CREATE TRIGGER tr6_stock ON stockCREATE TRIGGER tr6_stock ON stock FOR DELETE,UPDATE FOR DELETE,UPD

57、ATE AS AS DECLARE warehouse char(50) DECLARE warehouse char(50) SELECT warehouse=warehouse FROM DELETED SELECT warehouse=warehouse FROM DELETED IF warehouse= IF warehouse=供電局供電局1#1#倉庫倉庫 BEGIN BEGIN ROLLBACK TRAN ROLLBACK TRAN PRINT PRINT 不允許修改或刪除供電局不允許修改或刪除供電局1#1#倉庫的物資信息！倉庫的物資信息！ END END5.3.2 創(chuàng)建觸發(fā)器創(chuàng)

58、建觸發(fā)器5.3.3 5.3.3 修改和刪除觸發(fā)器修改和刪除觸發(fā)器只有數(shù)據(jù)庫所有者才能修改觸發(fā)器，修改觸發(fā)器的語句是：只有數(shù)據(jù)庫所有者才能修改觸發(fā)器，修改觸發(fā)器的語句是：ALTER TRIGGER ALTER TRIGGER ON ON 表名表名 | | 視圖名視圖名 WITH ENCRYPTION WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE UPDATE , DELETE NOT FOR REPLICATION NOT FOR REPLIC

59、ATION ASAS SQL SQL 語句語句 刪除觸發(fā)器的語句是：刪除觸發(fā)器的語句是：DROP TRIGGER DROP TRIGGER 觸發(fā)器名觸發(fā)器名注意：在刪除表時，依存于該表的觸發(fā)器也將同時被刪除。注意：在刪除表時，依存于該表的觸發(fā)器也將同時被刪除。 5.4 5.4 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性 數(shù)據(jù)庫的完整性是數(shù)據(jù)的數(shù)據(jù)庫的完整性是數(shù)據(jù)的正確性和相容性正確性和相容性。 它包括保持?jǐn)?shù)據(jù)的正確性、準(zhǔn)確性和有效性三方它包括保持?jǐn)?shù)據(jù)的正確性、準(zhǔn)確性和有效性三方面的含義。面的含義。 為了維護(hù)數(shù)據(jù)庫的完整性，為了維護(hù)數(shù)據(jù)庫的完整性，DBMSDBMS必須提供一種機(jī)必須提供一種機(jī)制來檢查數(shù)據(jù)庫的完整性

60、。實現(xiàn)的方式主要有兩制來檢查數(shù)據(jù)庫的完整性。實現(xiàn)的方式主要有兩種：一種是通過定義和使用完整性約束規(guī)則，另種：一種是通過定義和使用完整性約束規(guī)則，另一種是通過觸發(fā)器和存儲過程等來實現(xiàn)。一種是通過觸發(fā)器和存儲過程等來實現(xiàn)。 5.4.1 5.4.1 約束約束 約束通過限制列中的數(shù)據(jù)、行中的數(shù)據(jù)和表之間數(shù)據(jù)約束通過限制列中的數(shù)據(jù)、行中的數(shù)據(jù)和表之間數(shù)據(jù)來保證數(shù)據(jù)完整性。來保證數(shù)據(jù)完整性?！纠?.185.18】 在創(chuàng)建表在創(chuàng)建表salvagingsalvaging時創(chuàng)建約束。時創(chuàng)建約束。CREATE TABLE salvagingCREATE TABLE salvaging( prj_num char