觸發(fā)器全解析.doc

據(jù)庫(kù)程序設(shè)計(jì)中的約束、觸發(fā)器和存儲(chǔ)過程 分類： 數(shù)據(jù)庫(kù)技術(shù) 2012-08-16 21:41 3020人閱讀 評(píng)論(35) 收藏 舉報(bào) 上篇博客中所說的對(duì)于表操作的幾種限制少分析了觸發(fā)器。這次從對(duì)表設(shè)計(jì)的角度來著重分析約束和觸發(fā)器的關(guān)系，并進(jìn)一步擴(kuò)展比較觸發(fā)器和存儲(chǔ)過程。但在看該篇博客前強(qiáng)烈建議大家好好讀下我的上一篇博客約束與數(shù)據(jù)庫(kù)對(duì)象規(guī)則、默認(rèn)值的探究 首先，從圖上來比較三者的關(guān)系： 觸發(fā)器不僅能夠保證數(shù)據(jù)的完整性，而且還可以封裝復(fù)雜的T-SQL邏輯處理語(yǔ)句，在功能上類似于存儲(chǔ)過程，所以觸發(fā)器又是一種特殊的存儲(chǔ)過程。但是存儲(chǔ)過程的執(zhí)行是我們使用Exec主觀調(diào)用的，而觸發(fā)器是經(jīng)過一種事件操作后自動(dòng)被調(diào)用的。 在拆開分析約束和觸發(fā)器、觸發(fā)器和存儲(chǔ)過程之前我們穿插點(diǎn)外話。在數(shù)據(jù)庫(kù)程序設(shè)計(jì)中包含有多種數(shù)據(jù)模型：20世紀(jì)60年代后期，在文件系統(tǒng)基礎(chǔ)上發(fā)展起來的層次模型、網(wǎng)狀模型和關(guān)系模型等傳統(tǒng)數(shù)據(jù)模型；20世紀(jì)70年代后期產(chǎn)生的ER數(shù)據(jù)模型；20世紀(jì)80年代以來又相繼推出面向?qū)ο髷?shù)據(jù)模型、基于邏輯的數(shù)據(jù)模型等新的模型。下圖關(guān)系數(shù)據(jù)庫(kù)中的關(guān)鍵術(shù)語(yǔ)和語(yǔ)義對(duì)象模型及ER圖中使用的術(shù)語(yǔ)之間的映射關(guān)系：數(shù)據(jù)庫(kù)中正式術(shù)語(yǔ)文件SOME-R表關(guān)系文件類實(shí)體集合行元組記錄對(duì)象實(shí)體列屬性域?qū)傩詫傩?上面的內(nèi)容只存在了解而已，不用深究。數(shù)據(jù)完整性和業(yè)務(wù)規(guī)則 在上篇博客我已經(jīng)簡(jiǎn)單介紹了數(shù)據(jù)完整性，接下來我們?cè)敿?xì)說下數(shù)據(jù)完整性和業(yè)務(wù)規(guī)則。一、數(shù)據(jù)完整性 數(shù)據(jù)完整性=可靠性+準(zhǔn)確性，這里我們要清楚一下兩點(diǎn)： 數(shù)據(jù)存放在表中 創(chuàng)建表的時(shí)候，就應(yīng)當(dāng)保證以后數(shù)據(jù)輸入是正確的（錯(cuò)誤的數(shù)據(jù)、不符合要求的數(shù)據(jù)不允許輸入） 為了保證數(shù)據(jù)的完整性我們經(jīng)常使用完整性約束來確保數(shù)據(jù)的完整性。數(shù)據(jù)完整性，主要包括下面四部分： 二、業(yè)務(wù)規(guī)則 業(yè)務(wù)規(guī)則聽起來很難理解，當(dāng)然它也是值得我們深究東西，通俗的講它其實(shí)是符合實(shí)際條件。如：某商店規(guī)定一個(gè)售貨員在一個(gè)月內(nèi)售出10個(gè)以上的熱浴盆，那么獎(jiǎng)勵(lì)2000元；某公司的訂單上必須含有客戶的姓名和聯(lián)系方式等等，這些都是簡(jiǎn)單的業(yè)務(wù)規(guī)則。從數(shù)據(jù)庫(kù)的角度看，業(yè)務(wù)規(guī)則就是約束。約束和觸發(fā)器MS SQL Server提供了兩種主要的機(jī)制進(jìn)行強(qiáng)制業(yè)務(wù)規(guī)則和數(shù)據(jù)的完整性：約束和觸發(fā)器。在作用上約束支持的觸發(fā)器都可以實(shí)現(xiàn)，它們兩者是相容的關(guān)系，如下圖。雖然兩者在作用關(guān)系上有重合的地方，但是相較兩者的執(zhí)行效率和維護(hù)難易來說，觸發(fā)器是遠(yuǎn)遠(yuǎn)不如約束的。所以約束能實(shí)現(xiàn)的情況下編程人員是不會(huì)選擇觸發(fā)器的。 一、約束，上篇博客我已經(jīng)著重講解了約束的概念，這里不再深究。 SQL Server中存在五種約束： 約束的目的：確保表中數(shù)據(jù)的完整型 常用的約束類型：主鍵約束（Primary Key Constraint）：要求主鍵列數(shù)據(jù)唯一，并且不允許為空唯一約束（Unique Constraint）：要求該列唯一，允許為空，但只能出現(xiàn)一個(gè)空值。檢查約束（Check Constraint）：某列取值范圍限制、格式限制等，如有關(guān)年齡的約束默認(rèn)約束（Default Constraint）：某列的默認(rèn)值，如我們的男性學(xué)員較多，性別默認(rèn)為“男”外鍵約束（Foreign Key Constraint）：用于兩表間建立關(guān)系，需要指定引用主表的那列 二、觸發(fā)器，首先在下表中來看觸發(fā)器的基本結(jié)構(gòu)。 觸發(fā)器是一種對(duì)表進(jìn)行插入、刪除、更改的時(shí)候自動(dòng)運(yùn)行的特殊的存儲(chǔ)過程。它一般用在比核查約束更為復(fù)雜的約束中。但能用約束實(shí)現(xiàn)的功能，一般不用觸發(fā)器。 接下來我們從代碼中認(rèn)識(shí)下幾種觸發(fā)器。sql view plaincopyprint?1. -#Update型觸發(fā)器 2. Ifexits(selectnamefromsysobjectswherename=tgr_update)3. Droptriggertgr_update4. Go5. Createtriggertgr_updateonstudent6. forupdate7. As8. If(Update(student_ID)9. Print更改成功！10. Else11. Begin12. Raiserror(系統(tǒng)提示：更新發(fā)生錯(cuò)誤,16,1)13. Rollbacktran14. End15. Go16. -測(cè)試 17. Updatestudentsetstudent_ID=10002wherestudent_ID=10001 -#Update型觸發(fā)器If exits(select name from sysobjects where name=tgr_update)Drop trigger tgr_updateGoCreate trigger tgr_update on studentfor updateAsIf (Update(student_ID)Print 更改成功！ElseBegin Raiserror(系統(tǒng)提示：更新發(fā)生錯(cuò)誤,16,1)Rollback tranEndGo-測(cè)試Update student set student_ID=10002 where student_ID=10001 注意：在創(chuàng)建觸發(fā)器時(shí)，創(chuàng)建觸發(fā)器必須是批處理的第一行，存儲(chǔ)過程也是如此。html view plaincopyprint?1. -#insteadof觸發(fā)器2. if(object_id(tgr_classes_inteadOf,TR)isnotnull)3. droptriggertgr_classes_inteadOf4. go5. createtriggertgr_classes_inteadOf6. onclasses7. insteadofdelete/*,update,insert*/8. as9. declareidint,namevarchar(20);10. -查詢被刪除的信息，病賦值11. selectid=id,name=namefromdeleted;12. printid:+convert(varchar,id)+,name:+name;13. -先刪除student的信息14. deletestudentwherecid=id;15. -再刪除classes的信息16. deleteclasseswhereid=id;17. print刪除id:+convert(varchar,id)+,name:+name+的信息成功！;18. go19. -test20. select*fromstudentorderbyid;21. select*fromclasses;22. deleteclasseswhereid=7;23. -# instead of 觸發(fā)器 if (object_id(tgr_classes_inteadOf, TR) is not null) drop trigger tgr_classes_inteadOf go create trigger tgr_classes_intead Of on classes instead of delete/*, update, insert*/ as declare id int, name varchar(20); -查詢被刪除的信息，病賦值 select id = id, name = name from deleted; print id: + convert(varchar, id) + , name: + name; -先刪除student的信息 delete student where cid = id; -再刪除classes的信息 delete classes where id = id; print 刪除 id: + convert(varchar, id) + , name: + name + 的信息成功！; go -test select * from student order by id; select * from classes; delete classes where id = 7; # 啟用、禁用觸發(fā)器sql view plaincopyprint?1. -禁用觸發(fā)器 2. disabletriggertgr_messageonstudent;3. -啟用觸發(fā)器 4. enabletriggertgr_messageonstudent;5. #顯示自定義消息raiserror -禁用觸發(fā)器 disable trigger tgr_message on student; -啟用觸發(fā)器 enable trigger tgr_message on student; # 顯示自定義消息raiserrorsql view plaincopyprint?1. if(object_id(tgr_message,TR)isnotnull)2. droptriggertgr_message3. go4. createtriggertgr_message5. onstudent6. afterinsert,update7. asraisError(tgr_message觸發(fā)器被觸發(fā),16,10);8. go9. -test 10. insertintostudentvalues(lily,22,1,7);11. updatestudentsetsex=0wherename=lucy;12. select*fromstudentorderbyid;13. if (object_id(tgr_message, TR) is not null) drop trigger tgr_message go create trigger tgr_message on student after insert, update as raisError(tgr_message觸發(fā)器被觸發(fā), 16, 10); go -test insert into student values(lily, 22, 1, 7); update student set sex = 0 where name = lucy; select * from student order by id; 觸發(fā)器的應(yīng)用種類繁多上面的幾個(gè)示例都是比較常用的，當(dāng)然最好的熟練方法就是多用，多練。觸發(fā)器和存儲(chǔ)過程 觸發(fā)器是一種特殊的存儲(chǔ)過程，不是由用戶直接調(diào)用。而存儲(chǔ)過程是一組T-SQL語(yǔ)句，經(jīng)過編譯后可以被多次調(diào)用。類似于其它編程語(yǔ)言中的過程。它可以接收輸入?yún)?shù)、輸出參數(shù)、 返回單個(gè)或多個(gè)結(jié)果集以及返回值。 存儲(chǔ)過程分為三類： 1.系統(tǒng)存儲(chǔ)過程：以sp_開頭,用來進(jìn)行系統(tǒng)的各項(xiàng)設(shè)定.取得信息.相關(guān)管理工作, 如 sp_help就是取得指定對(duì)象的相關(guān)信息 2.擴(kuò)展存儲(chǔ)過程以XP_開頭,用來調(diào)用操作系統(tǒng)提供的功能 exec master.xp_cmdshell ping 10.8.16.1 3.用戶自定義的存儲(chǔ)過程,這是我們所指的存儲(chǔ)過程 常用格式delphi view plaincopyprint?1. CreatePRocedureprocedue_name2. parameterdata_typeoutput3. withrecompile|encryption4. as5. sql_statement6. -解釋:7. -output：表示此參數(shù)是可傳回的8. -withrecompile|encryption9. -recompile:表示每次執(zhí)行此存儲(chǔ)過程時(shí)都重新編譯一次10. -encryption:所創(chuàng)建的存儲(chǔ)過程的內(nèi)容會(huì)被加密Create PRocedure procedue_name parameter data_typeoutput withrecompile|encryption as sql_statement-解釋: -output：表示此參數(shù)是可傳回的-with recompile|encryption-recompile:表示每次執(zhí)行此存儲(chǔ)過程時(shí)都重新編譯一次-encryption:所創(chuàng)建的存儲(chǔ)過程的內(nèi)容會(huì)被加密舉例： 有如下表量表result_Info： Student_IDNameresult1張紅70 2馬力80 Student_InfoStudent_IDSexGrade1女一年級(jí)2男二年級(jí) #創(chuàng)建返回參數(shù)的存儲(chǔ)過程sql view plaincopyprint?1. Ifexists(selectnamefromsysobjectswherename=proc_returnandtype=P)2. Dropprocproc_return3. Go4. Createprocproc_return5. param1int,6. param2char(10),7. param3char(10)8. param4intoutput9. Withencryption-加密 10. As11. Insertintostudent_Info(student_ID,name,result)values(param1,param2,param3)12. Selectparam4=sum(result)fromstudent_Info13. Print總分為：&convert(char,param)14. Go15. -調(diào)用測(cè)試 16. Declaresumresultint17. Execproc_return12,王剛,80,sumresult18. GoIf exists(select name from sysobjects where name=proc_return and type=P)Drop proc proc_returnGoCreate proc proc_return param1 int,param2 char(10),param3 char(10)param4 int outputWith encryption -加密AsInsert into student_Info(student_ID,name,result) values(param1,param2,param3)Select param4=sum(result) from student_InfoPrint 總分為： & convert(char,param)Go-調(diào)用測(cè)試Declare sumresult intExec proc_return 12,王剛,80,sumresultGo 存儲(chǔ)過程的3種傳回值: 1.以Return傳回整數(shù) 2.以output格式傳回參數(shù) 3.Recordset 傳回值的區(qū)別: output和return都可在批次程式中用變量接收,而recordset則傳回到執(zhí)行批次的客戶端中 #創(chuàng)建一個(gè)存儲(chǔ)過程，實(shí)現(xiàn)將表一和表二合并，該表只含Student_ID、Name、sex、result，將臨時(shí)表存放在存儲(chǔ)過程中。sql view plaincopyprint?1. Ifexists(sel