數(shù)據(jù)庫原理實(shí)驗(yàn)報(bào)告S11

1、實(shí)驗(yàn)11數(shù)據(jù)庫觸發(fā)器的建立實(shí)驗(yàn)日期和時(shí)間：實(shí)驗(yàn)室：軟件工程室班級(jí)：12計(jì)科3學(xué)號(hào)：姓名：張翔實(shí)驗(yàn)環(huán)境:1. 硬件：內(nèi)存 處理器2. 軟件： Windows 7 旗艦版 Microsoft SQL Server 2005實(shí)驗(yàn)原理:1. 理解觸發(fā)器的概念、和一般存儲(chǔ)過程的區(qū)別、觸發(fā)器的優(yōu)點(diǎn)和使用原則。2. 掌握觸發(fā)器的創(chuàng)建、執(zhí)行、查看、修改和刪除方法。一、觸發(fā)器的概念：觸發(fā)器是一種特殊類型的存儲(chǔ)過程。一般的存儲(chǔ)過程通過存儲(chǔ)過程名稱被直接調(diào)用，而觸發(fā)器主要是通過當(dāng)某個(gè)事件發(fā)生時(shí)自動(dòng)被觸發(fā)執(zhí)行的。觸發(fā)器可以用于SQL Server約束、默認(rèn)值和規(guī)則的完整性檢查，還可以完成難以用普通約束實(shí)現(xiàn)的復(fù)雜功能。

2、當(dāng)創(chuàng)建數(shù)據(jù)庫對(duì)象或在數(shù)據(jù)表中插入記錄、修改記錄或者刪除記錄時(shí)，SQL Server就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的 SQL語句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。觸發(fā)器和引起觸發(fā)器執(zhí)行的SQL語句被當(dāng)作一次事務(wù)處理，如果這次事務(wù)未獲得成功，SQL Server會(huì)自動(dòng)返回該事務(wù)執(zhí)行前的狀態(tài)。使用觸發(fā)器有以下優(yōu)點(diǎn)：觸發(fā)器是自動(dòng)的執(zhí)行的，。觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進(jìn)行層疊更改，如可以在“學(xué)生表”中的學(xué)號(hào)列 上創(chuàng)建一個(gè)刪除觸發(fā)器，當(dāng)刪除“學(xué)生表”的記錄時(shí)，自動(dòng)級(jí)聯(lián)刪除“成績表”中 的個(gè)匹配行。觸發(fā)器可以強(qiáng)制限制。這些限制比用CHECK勺束所定義的更復(fù)雜，與 CHECK勺束不同的

3、是，觸發(fā)器可以引用其他表中的列。觸發(fā)器可以分為DML觸發(fā)器和DDLJ觸發(fā)器兩種DDL觸發(fā)器用于響應(yīng)各種數(shù)據(jù)定義語言(DDL)事件。這些事件主要對(duì)應(yīng)于Transact-SQL 中的CREATE ALTER和DROP語句，以及執(zhí)行類似 DDL操作的某些 系統(tǒng)存儲(chǔ)過程。它們用于執(zhí)行管理任務(wù)，并強(qiáng)制影響數(shù)據(jù)庫的業(yè)務(wù)規(guī)則。DML觸發(fā)器是在用戶使用數(shù)據(jù)操作語言(DML)事件編輯數(shù)據(jù)時(shí)發(fā)生。 DML事件是針對(duì)表或視圖的INSERT、UPDATE或DELETE語句。DML觸發(fā)器有助于在表或視圖中修 改數(shù)據(jù)時(shí)強(qiáng)制業(yè)務(wù)規(guī)則，擴(kuò)展數(shù)據(jù)完整性。DML觸發(fā)器又分為AFTER觸發(fā)器和INSTEAD OF觸發(fā)器兩種：AFT

4、ER觸發(fā)器：這種類型的觸發(fā)器將在數(shù)據(jù)變動(dòng)(INSERT UPDATED DELETE操作)完成以后才被觸發(fā)?？梢詫?duì)變動(dòng)的數(shù)據(jù)進(jìn)行檢查，如果發(fā)現(xiàn)錯(cuò)誤，將拒絕接受或回滾變動(dòng)的數(shù)據(jù)。AFTER觸發(fā)器只能在表上定義。在同一個(gè)數(shù)據(jù)表中可以創(chuàng)建多個(gè)AFTER 觸發(fā)器。INSTEAD OF觸發(fā)器：INSTEAD OF觸發(fā)器將在數(shù)據(jù)變動(dòng)以前被觸發(fā)，并取代變動(dòng)數(shù)據(jù) 的操作，而去執(zhí)行觸發(fā)器定義的操作。INSTEAD OF觸發(fā)器可以在表或視圖上定義。每個(gè)INSERT UPDATED DELETE語句最多定義一個(gè)INSTEAD OF觸發(fā)器。二、創(chuàng)建觸發(fā)器1. 使用SQL Server管理控制臺(tái)創(chuàng)建 DML觸發(fā)器在SQ

5、LServer管理控制臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫，單擊要?jiǎng)?chuàng)建觸發(fā)器的數(shù) 據(jù)表，右擊其中的“觸發(fā)器”文件夾。從彈出的快捷菜單中選擇“新建觸發(fā)器”選項(xiàng)， 會(huì)出現(xiàn)新建觸發(fā)器模板窗口，在新建的觸發(fā)器模板窗口中單擊“查詢”菜單中“指定模 板參數(shù)的值”選項(xiàng)，會(huì)彈出“指定模板參數(shù)的值”對(duì)話框，可以在這里設(shè)置參數(shù)的具體 值。2. 使用Transact-SQL語句創(chuàng)建 DML觸發(fā)器使用Transact-SQL語言中的 CREATE TRIGGE命令也可以創(chuàng)建 DML觸發(fā)器：CREATE TRIGGER schema_ name . trigger_name ON table | view WITH ，n F

6、OR | AFTER| INSTEAD OF INSERT ,UPDATE , DELETEAS sql_stateme nt ;，n := ENCRYPTION EXECUTE AS Clause 其中，各參數(shù)的說明如下：schema_name DML觸發(fā)器所屬架構(gòu)的名稱。trigger_name :觸發(fā)器的名稱。table | view:對(duì)其執(zhí)行 DML觸發(fā)器的表或視圖。WITH ENCRYPTION對(duì)CREATE TRIGGE語句的文本進(jìn)行加密處理。EXECUTE AS指定用于執(zhí)行該觸發(fā)器的安全上下文。FOR | AFTER: AFTER指定觸發(fā)器僅在觸發(fā)SQL語句中指定的所有操作都已成

7、功執(zhí)行時(shí)才被觸發(fā)。不能對(duì)視圖定義AFTER觸發(fā)器。INSTEAD OF指定執(zhí)行DML觸發(fā)器而不是觸發(fā) SQL語句。DELETE , INSERT , UPDATE:指定數(shù)據(jù)修改語句。必須至少指定一個(gè)選項(xiàng)。sql_stateme nt :觸發(fā)條件和操作。當(dāng)創(chuàng)建一個(gè)觸發(fā)器時(shí)必須指定以下幾項(xiàng)內(nèi)容：觸發(fā)器的名稱；在其上定義觸發(fā)器的表；觸發(fā)器將何時(shí)激發(fā)；執(zhí)行觸發(fā)操作的編程語句 。注意DML觸4發(fā)器時(shí)生成的兩個(gè)幻表：DML觸發(fā)器可以使用兩個(gè)特殊的臨時(shí)表，它們分別是inserted 表和deleted表。這兩個(gè)表都存在于內(nèi)存中。它們?cè)诮Y(jié)構(gòu)上類似于定義了觸發(fā)器的表。在inserted 表中存儲(chǔ)著被INSERT

8、和UPDATE語句影響的新的數(shù)據(jù)行。在執(zhí)行INSERT或UPDATE語句時(shí)，新的數(shù)據(jù)行被添加到基本表中，同時(shí)這些數(shù)據(jù)行的備份被復(fù)制到in serted 臨時(shí)表中。在deleted 表中存儲(chǔ)著被 DELETE和UPDATE語句影響的舊數(shù)據(jù)行 。在執(zhí)行 DELETE或 UPDATE語句時(shí)，指定的數(shù)據(jù)行從基本表中刪除，然后被轉(zhuǎn)移到了deleted表中。在基本表和deleted表中一般不會(huì)存在相同的數(shù)據(jù)行。一個(gè)UPDATE操作實(shí)際上是由一個(gè)DELETE操作和一個(gè)INSERT操作組成的。在執(zhí)行UPDATE操作時(shí)，舊的數(shù)據(jù)行從基本表中轉(zhuǎn)移到deleted表中，然后將新的數(shù)據(jù)行同時(shí)插入基本表和in sert

9、ed 表中。實(shí)驗(yàn)主要任務(wù)：導(dǎo)入學(xué)生成績數(shù)據(jù)庫，并建立相關(guān)表的關(guān)系，對(duì)數(shù)據(jù)庫實(shí)施如下操作：一、利用SQLServer管理控制臺(tái)創(chuàng)建DML觸發(fā)器中的INSERT觸發(fā)器的后（AFTER 觸發(fā)器1.利用SQL-SERVE管理控制臺(tái)：創(chuàng)建一個(gè) INSERT觸發(fā)器“ TR_Course_lnsert ”，當(dāng)在課 程表中插入一條新記錄時(shí)，觸發(fā)該觸發(fā)器，并給出“你插入了一條新的課程記錄！”的提示 信息。p 2013-0227-1445.生-SQLQu&ryLsqhcrea匸亡 trigger TR_匚亡工匸 on i果環(huán)呈Cor insertasi_f select co-nt * fxon: inaezte

10、d - =1beginpr inr 你插入了一條新的課程記錄！，2.創(chuàng)建了 TR_Course_lnsert觸發(fā)器后，查看向課程表中插入數(shù)據(jù)時(shí)，此觸發(fā)器所完成的功2013 -0227 -1445,- SQLQueryT tql*insertinto課程values 000122 目 $ 數(shù)據(jù)庫 * jLj W你插入了一條制的課程記錄！u行受影響）二、使用Transact-SQL語句創(chuàng)建DML的后（FOR AFTER觸發(fā)器（一）實(shí)現(xiàn)檢查約束3. 創(chuàng)建一個(gè) AFTER觸發(fā)器，要求實(shí)現(xiàn)以下功能：在成績表上創(chuàng)建一個(gè)插入、更新類型的觸 發(fā)器TR_ScoreCheck,當(dāng)在成績字段中插入或修改考試分?jǐn)?shù)后，

11、觸發(fā)該觸發(fā)器，檢查分?jǐn)?shù)是 否在0-100之間。create trigger TR_ScoreCheckon成績for insert , updateasif （select 成績 from inserted ） between 0 and 100beginprint 你成功的對(duì)成績表插入（更新）了一條記錄endelsebeginprint （插入）更新的成績數(shù)據(jù)錯(cuò)誤rollback tran sacti onend在 創(chuàng)建了 TR_ScoreChec觸發(fā)器之后，在查詢窗口中輸入以下SQ語句驗(yàn)證：USE學(xué)生成績卩-驗(yàn)證插入融發(fā)器4PRIMT，在成義表中插入記錄時(shí)4煖器的執(zhí)行結(jié)果？*PR1WI 1

12、*IMSERT INTO 成績dVAIiUES （ 200522614611D80D24 ,9E0）IJI5ERT IET0 戍績 2VALUES t 2OO522614S 1 , 0110961 -65）+JSO-驗(yàn)證更新觸發(fā)器4APRIWT臨咸績表中更新記錄時(shí)竝發(fā)器的執(zhí)行結(jié)果：屮PHIWT 1 屮UPDATE成績余SET成韁二-7決WERE200522 614f 1 AUD 課程號(hào)0050219UPDATE咸績屮SET成績二肝EWERE 學(xué)號(hào)=200522614S託肛 課程號(hào)=00502 19*-込，（獨(dú)立完成：4.創(chuàng)建一個(gè)AFTER觸發(fā)器，要求實(shí)現(xiàn)以下功能：在2003學(xué)生名單表上創(chuàng)建一個(gè)

13、插入、更新類型的觸發(fā)器 TR_Sex_Check,當(dāng)在性別字段中插入或修改性別后，觸發(fā)該觸發(fā) 器，檢查性別是否是男或女，并驗(yàn)證）set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER trigger TR_Sex_Checkon dbo.學(xué)生名單for insert , updateasif （select 性別 from inserted ） in （男,女）beginprint 插入（更新）數(shù)據(jù)endelsebeginprint 插入或更新的性別錯(cuò)誤rollbacktran sacti onend2013-0257-144S.學(xué)生-SQLQueryd.

14、sqh 摺塞Kpdare學(xué)牛名單 性別-男， where 學(xué)號(hào)=55001 丨由消息插入（更計(jì)D姻口 fTS影響】USE學(xué)生成績?cè)bGOJSELECT *FROM咸歸WHERE 學(xué)號(hào)二 200522 6151盼DELETE FPON WHERE1 2005226151 *JGO+JSELECT *FROM阪績7WHS口E 學(xué)號(hào)二2005226151GO喪-dbo.=? 2013-0237-1445.學(xué)生-EQLQ誕ryH 虛qN Ji 皿學(xué)*成績g select frcm成績where 學(xué)號(hào) l20GS2261S2 1godelate 工9口 學(xué)生 wnre 學(xué)號(hào)=2 noeZEE二$2 gos

15、elect from成績where= 1 2DOS226152 1god 13結(jié)果上a消息課程是 MBaii Braaaiii ! maBniaMB| 1 20052?6152 iU0M213582 5226152_r 0120729 Sd學(xué)號(hào)課程 成幾（獨(dú)立完成：6.創(chuàng)建一個(gè)AFTER觸發(fā)器，要求實(shí)現(xiàn)以下功能在2003班級(jí)表上創(chuàng)建一個(gè)刪除類型的觸發(fā)器TR_Class_Delete，當(dāng)在2003班級(jí)表中刪除某一條記錄后，觸發(fā)該觸發(fā)器，在 2003學(xué)生名單中刪除與此學(xué)號(hào)對(duì)應(yīng)的記錄。并驗(yàn)證）set ANSI_NULLSONset QUOTED_IDENTIFIERON goALTER trigge

16、r TR_Class_Deleteon dbo.班級(jí)for deleteasbegindeletefrom學(xué)生名單where 班級(jí)=(select 班級(jí) from deleted )end課程號(hào)遲徨名11100011 1秘書學(xué)三、使用Transact-SQL語句創(chuàng)建DML的前（INSTEAD OF觸發(fā)器7. 創(chuàng)建一個(gè)INSTEAD OF觸發(fā)器，要求實(shí)現(xiàn)以下功能：在課程表上創(chuàng)建一個(gè)刪除類型的觸發(fā) 器TR_NotAllowDelete，當(dāng)在課程表中刪除記錄時(shí)，觸發(fā)該觸發(fā)器，顯示不允許刪除表中數(shù) 據(jù)的提示信息。在 創(chuàng)建了 TR_Stu_Delete觸發(fā)器之后，在查詢窗口中輸入以下SQ語句驗(yàn)證：ITS

17、E學(xué)生亦績JSELECT %FROM諜程1WHERE 課程號(hào)-1100013GO PDELETE FROM 諜程閃HERE諜程號(hào) 1 1W0013 QGWSELECT 2FROM課程屮UJHERH 諜程號(hào)-1 11DOOL3 +J四、查看和修改觸發(fā)器（一）禾U用SQL Server管理控制平臺(tái)查看8. 使用SQL Server管理控制臺(tái)查看TR_Course_lnsert觸發(fā)器并查看與該觸發(fā)器有依賴關(guān)系的其他數(shù)據(jù)庫對(duì)象9餓賴于TR.CcrarmUTaaerf 的対象電）TK_C.Insert依報(bào)的對(duì)象 4）TR_Course_Lusert（二）使用系統(tǒng)存儲(chǔ)過程查看觸發(fā)器可以使用系統(tǒng)存儲(chǔ)過程sp

18、_help、sp_helptext 和sp_depends分別查看觸發(fā)器的不同信息。它們的具體用途和語法形式如下：sp_help :用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)建時(shí)間。sp_help觸發(fā)器名稱sp_helptext :用于查看觸發(fā)器的正文信息。sp_depends :用于查看觸發(fā)器涉及到的所有表。9. 使用系統(tǒng)存儲(chǔ)過程查看TR_Course_Insert觸發(fā)器的一般信息2013-0227-1-145. - SQLQuerylS.Eql* 擅要sp nelp tr caurse insert1Mt _Q結(jié)果I上占消息NameOwner Type CrBatedjdat

19、etime TR_Couree_bisert | dtw trigger 22K-12-1D 1G;1G;M.G37iim jiii niEaiii iiai10. 使用系統(tǒng)存儲(chǔ)過程查看TR_Stu_Delete觸發(fā)器涉及到的所有表。13-0227-1445. - &QLQueryLf.iqP 播委spdepends tr_s匸屮irrC3結(jié)果匕満息nametypeupdated seieatai colunnm a BllSal：!.S1 j dbc.1 I us&fUbl yes no NULL11. 使用系統(tǒng)存儲(chǔ)過程查看TR_Stu_Delete觸發(fā)器的正文信息wy 鼻總* 巧丁于：-利

20、p Jiascsp_helpuex tr_sii_deleLe|3結(jié)杲LjaaTextJ:create tn號(hào)ger T R_Stu_Delete23on學(xué)生fur delate4BS5begh6delete7from成蠱8wfiere 學(xué) prelect 學(xué)暑 fron deleted)9end五、刪除觸發(fā)器12. 用系統(tǒng)命令DROP TRIGGERS發(fā)器名刪除TR_Course_lnser2013*0227-1445.學(xué)住 + SQLQuerylS.sql*drop rrigga* TRInsert血消息命令已成切完成。利用商品銷售練習(xí)數(shù)據(jù)庫 閱讀下面的插入存儲(chǔ)器、觸發(fā)器實(shí)例，并把代碼敲入

21、自 己的界面上熟悉：F電商品梢售商品庫存裔呂庫存皿商品銷售數(shù)據(jù)庫【商品銷售練習(xí)】關(guān)系圖商品庫存PK商口口編號(hào)商品名稱 商品規(guī)格 單價(jià)庫存數(shù)量 最低庫存數(shù)量商品銷售PK自動(dòng)編號(hào)FK1商品編號(hào) 商品名稱 商品規(guī)格 單價(jià) 數(shù)量 金額銷售日期用visio 2010畫的關(guān)系圖顯示該商品的的所有銷售記錄及銷售1.）創(chuàng)建一個(gè)存儲(chǔ)過程， 它接收一個(gè)商品編號(hào),總數(shù)量、金額總計(jì)值、現(xiàn)有庫存等相關(guān)信息。-打開數(shù)據(jù)庫商品銷售練習(xí)USE商品銷售練習(xí)GO-先查詢商品編號(hào)為005學(xué)生信息select * from 商品銷售 where商品編號(hào)=005-執(zhí)行剛剛創(chuàng)建的存儲(chǔ)過程Pro_各科成績單EXECPro_ 商品編號(hào)005

22、2.）創(chuàng)建一個(gè)觸發(fā)器，能夠在 商品銷售”表中插入一條記錄時(shí)（只輸入”商品編號(hào)” 和商品數(shù)量”）通過觸發(fā)器的作用，能自動(dòng)填寫該記錄”商品編號(hào)”對(duì)應(yīng)的”商品名稱”、”商品規(guī)格”、”單價(jià)”、”銷售日期”，能自動(dòng)計(jì)算”金額（= 單價(jià)*數(shù)量）填入。并實(shí)現(xiàn)庫存 不足報(bào)警。在”商品銷售”表中插入記錄時(shí)，能根據(jù) ”商品庫存”表中的對(duì)應(yīng)商品記錄的” 庫存數(shù)量”進(jìn)行檢查，凡銷售”數(shù)量”已超出庫存數(shù)量的商品記錄不能插入，并顯示現(xiàn)有庫存量并提示“庫存不足”/ “庫存空”，凡是庫存量低于最低庫存量的也提示”現(xiàn)有庫存量 低于最低庫量”。創(chuàng)建觸發(fā)器代碼如下：插入代碼：-插入一條記錄USE商品銷售練習(xí)GOselect * from 商品庫存 where商品編號(hào)=005GOselect * from商品銷售GOINSERT INTO商品銷售（商品編號(hào)，數(shù)量）VALUES005 , 18）GOselect * fro