數(shù)據(jù)庫觸發(fā)器學(xué)習(xí)教案

1、數(shù)據(jù)庫觸發(fā)器數(shù)據(jù)庫觸發(fā)器第一頁，共29頁。第1頁/共28頁第二頁，共29頁。問題(wnt)的引入增加一個(gè)(y )記錄刪除一個(gè)記錄(“95003”,”002”,56)95001 006 9041l在學(xué)生管理數(shù)據(jù)庫中,某學(xué)生的選課門數(shù)是一個(gè)經(jīng)常訪問的屬性,因此在student表中增加一個(gè)屬性sselnum (smallint）l在實(shí)際應(yīng)用l實(shí)例展示第2頁/共28頁第三頁，共29頁。l可否(kfu)用嵌套的SQL語句來實(shí)現(xiàn)?l問題的解決：使用觸發(fā)器triggerlupdate student set sselnum=(select count(*) from s_c where s_c.sno=st

2、udent.sno)l存在的問題l觸發(fā)器的主要作用就是其能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性 第3頁/共28頁第四頁，共29頁。l 觸發(fā)器觸發(fā)原因(yunyn)對(duì)表進(jìn)行插入、更新或刪除操作觸發(fā)事件。l 觸發(fā)結(jié)果：另一段程序的執(zhí)行。l 思考 ：在上例中，觸發(fā)原因是什么？觸發(fā)結(jié)果是什么？第4頁/共28頁第五頁，共29頁。l 觸發(fā)器和存儲(chǔ)(cn ch)過程的區(qū)別：INSERT INTO s_c VALUES(95001,006,90) EXEC Getgrade 95001 觸發(fā)器可以用于 SQL Server 約束(yush)、默認(rèn)值和規(guī)則的完整性檢查。 觸發(fā)器主要是通過

3、事件觸發(fā)而執(zhí)行的，而存儲(chǔ)過程是通過調(diào)用存儲(chǔ)過程名稱而執(zhí)行的。第5頁/共28頁第六頁，共29頁。第6頁/共28頁第七頁，共29頁。觸發(fā)器可以實(shí)現(xiàn)比較復(fù)雜的完整性約束：l 擴(kuò)展約束、默認(rèn)值和規(guī)則對(duì)象的完整性檢查。l 自動(dòng)生成數(shù)據(jù)。l 檢查數(shù)據(jù)的修改，防止對(duì)數(shù)據(jù)不正確的修改，保證數(shù)據(jù)表之間數(shù)據(jù)的正確性和一致性。l 自定義復(fù)雜的安全(nqun)權(quán)限。 觸發(fā)器作為一種(y zhn)數(shù)據(jù)庫對(duì)象，在syscomment系統(tǒng)表中存儲(chǔ)其完整的定義信息，在sysobject系統(tǒng)表中有該對(duì)象的記錄。第7頁/共28頁第八頁，共29頁。1）觸發(fā)器自動(dòng)執(zhí)行(zhxng) 在對(duì)表的數(shù)據(jù)作了任何修改（比如手工輸入或者應(yīng)用程序

4、采取的操作）之后立即被激活。3）觸發(fā)器可以實(shí)現(xiàn)比CHECK約束更為復(fù)雜的數(shù)據(jù)完整性約束 在數(shù)據(jù)庫中為了(wi le)實(shí)現(xiàn)數(shù)據(jù)完整性約束，可以使用CHECK約束或觸發(fā)器。CHECK約束不允許引用其它表中的列來完成檢查工作，而觸發(fā)器可以引用其它表中的列。 2）觸發(fā)器能夠?qū)?shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改 觸發(fā)器是基于一個(gè)表創(chuàng)建的，但是可以針對(duì)多個(gè)表進(jìn)行操作，實(shí)現(xiàn)數(shù)據(jù)庫中相關(guān)表的級(jí)聯(lián)更改。第8頁/共28頁第九頁，共29頁。觸發(fā)器的分類(fnli)lINSTEADOF觸發(fā)器:l并不執(zhí)行其所定義的操作（INSERT、UPDATE、DELETE），而僅是執(zhí)行觸發(fā)器本身。l既可在表上定義INSTEADOF觸發(fā)器

5、，也可以在視圖上定義INSTEADOF觸發(fā)器。第9頁/共28頁第十頁，共29頁。 Inserted和Deleted是邏輯（概念）表。當(dāng)在定義了觸發(fā)器的表上發(fā)生(fshng)了修改操作時(shí)會(huì)自動(dòng)派生出這兩個(gè)視圖。 當(dāng)在表上發(fā)生插入操作時(shí)，新插入的行將出現(xiàn)在插入視圖中。 當(dāng)在表上發(fā)生刪除操作時(shí)，被刪除的行將出現(xiàn)在刪除視圖中。 當(dāng)在表上發(fā)生更新操作時(shí)，舊行出現(xiàn)在刪除視圖中，新行出現(xiàn)在插入視圖中。第10頁/共28頁第十一頁，共29頁。S_C表第11頁/共28頁第十二頁，共29頁。v觸發(fā)器的基本操作創(chuàng)建v在創(chuàng)建觸發(fā)器前，必須注意以下幾點(diǎn)：v1、CREATE TRIGGER 語句必須是批處理中的第一條語句。

6、v2、只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器，名稱必須遵循標(biāo)識(shí)符的命名規(guī)則。v3、表的所有者具有創(chuàng)建觸發(fā)器的默認(rèn)(mrn)權(quán)限，不能將該權(quán)限轉(zhuǎn)給其他用戶。v4、不能在臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時(shí)表，但是不能引用系統(tǒng)表。第12頁/共28頁第十三頁，共29頁。創(chuàng)建觸發(fā)器命令(mng lng)格式：CREATE TRIGGER trigger_name ON tabel FOR | AFTER | INSTEAD OF INSERT,UPDATE,DELETE AS IF UPDATE(column) AND|OR UPDATE (column) sql_statement 一、企業(yè)(q

7、y)管理器二、查詢分析器第13頁/共28頁第十四頁，共29頁。說明(shumng)：第14頁/共28頁第十五頁，共29頁。例：當(dāng)在倉庫表上發(fā)生插入操作時(shí)會(huì)輸出(shch)“插入了一個(gè)倉庫元組”的信息。USE倉庫(cngk)管理GOCREATETRIGGERWH_INON倉庫(cngk)FORINSERTASPRINT插入了一個(gè)倉庫(cngk)元組INSERT倉庫VALUES(WH6,南京,700)第15頁/共28頁第十六頁，共29頁。例：在倉庫表上定義一個(gè)刪除(shnch)觸發(fā)器，使得當(dāng)刪除(shnch)倉庫記錄時(shí)，同時(shí)將所屬所有職工記錄的倉庫號(hào)字段值置為空值NULL。CREATETRIGGE

8、RWH_DELON倉庫(cngk)FORDELETEASUPDATE職工SET倉庫(cngk)號(hào)=NULLWHERE倉庫(cngk)號(hào)IN(SELECT倉庫(cngk)號(hào)FROMdeleted)DELETE倉庫WHERE倉庫號(hào)=WH6倉庫（倉庫號(hào)，城市，面積）職工（倉庫號(hào)，職工號(hào)，工資）第16頁/共28頁第十七頁，共29頁。例：對(duì)S表定義一個(gè)(y)刪除觸發(fā)器，使得當(dāng)刪除學(xué)生記錄時(shí)，將S_C表中相應(yīng)的選課記錄刪除。S（SNO,SNAME）S_C（SNO,CNO,SCORE）第17頁/共28頁第十八頁，共29頁。例：對(duì)職工表定義一個(gè)插入觸發(fā)器，使得當(dāng)插入職工記錄(jl)時(shí)，檢查所參照的倉庫元組是否

9、存在，如果不存在，則撤消所做的插入操作。倉庫（倉庫號(hào)，城市，面積(minj)）職工（倉庫號(hào)，職工號(hào)，工資）第18頁/共28頁第十九頁，共29頁。CREATETRIGGERWH_INSON職工(zhgng)FORINSERTASIFNOTEXISTS(SELECT*FROM倉庫WHERE倉庫號(hào)=(SELECT倉庫號(hào)FROMINSERTED)BEGINRAISERROR(非法倉庫號(hào)!,1,1)ROLLBACKTRANSACTIONENDINSERT職工(zhgng)VALUES(WH5,E10,1600)IN第19頁/共28頁第二十頁，共29頁。例：對(duì)S_C表定義一個(gè)插入觸發(fā)器，使得當(dāng)插入選課記錄

10、時(shí)，檢查所參照的學(xué)生學(xué)號(hào)和課程號(hào)是否(shfu)存在，如果不存在，則撤消所做的插入操作。S（SNO,SNAME）C（CNO，CNAME）S_C（SNO,CNO,SCORE）第20頁/共28頁第二十一頁，共29頁。例：對(duì)職工表定義一個(gè)更新觸發(fā)器，使得當(dāng)職工變換所屬倉庫時(shí)，檢查所參照的倉庫元組是否存在，如果不存在，則撤消所做的更新操作(cozu)，如果新的倉庫號(hào)是WH2則將工資提高10%。第21頁/共28頁第二十二頁，共29頁。CREATETRIGGERTR_UPDATEON職工FORUPDATEASDECLAREWHNOCHAR(4)IFUPDATE(倉庫(cngk)號(hào))BEGINIFNOTEX

11、ISTS(SELECT*FROM倉庫(cngk)WHERE倉庫(cngk)號(hào)=(SELECT倉庫(cngk)號(hào)FROMINSERTED)BEGINRAISERROR(非法倉庫(cngk)號(hào)!,16,1)ROLLBACKTRANSACTIONENDELSEBEGINSELECTWHNO=倉庫(cngk)號(hào)FROMINSERTEDIFWHNO=WH2UPDATE職工SET工資=工資*1.1WHERE職工號(hào)=(SELECT職工號(hào)FROMINSERTED)ENDEND第22頁/共28頁第二十三頁，共29頁。例：當(dāng)插入或更新學(xué)生成績時(shí)，觸發(fā)器檢查(jinch)該課程是否為考查課，若是，則通過的成績只能以

12、60分計(jì)，未通過的只能以40分計(jì)。涉及(shj)的表結(jié)構(gòu)：C1(Cno,Cname,Ctype) S_C(Sno,Cno,Score) 第23頁/共28頁第二十四頁，共29頁。CREATETRIGGERSCORE_KCONS_CFORINSERT,UPDATEASDECLARESCOREINT,CTYPECHAR(4)SELECTSCORE=SCORE,CTYPE=CTYPEFROMC1,INSERTEDWHEREINSERTED.CNO=C1.CNOIF(CTYPE=考查)AND(SCORE60ANDSCORE40)BEGINRAISERROR(該課程(kchng)為考查課，成績以60或40

13、計(jì)!,16,1)ROLLBACKTRANSACTIONEND第24頁/共28頁第二十五頁，共29頁。CREATETRIGGERSCORE_KCONS_CFORINSERT,UPDATEASDECLARESCOREINT,CTYPECHAR(4)SELECTSCORE=SCORE,CTYPE=CTYPEFROMC1,INSERTEDWHEREINSERTED.CNO=C1.CNOIF(CTYPE=考查(koch)BEGINIFSCORE=60UPDATES_CSETSCORE=60WHERECNO=(SELECTCNOFROMINSERTED)ANDSNO=(SELECTSNOFROMINSERTED)ELSEUPDATE