数据库原理实验报告S11-数据库触发器的创建

v1.0可编辑可修改v1.0可编辑可修改实验11数据库触发器的建立实验日期和时间： 实验室：软件工程室班级：12计科3 学号： 姓名：张翔实验环境：.硬件：内存处理器.软件：Windows7旗舰版MicrosoftSQLServer2005实验原理：理解触发器的概念、和一般存储过程的区别、触发器的优点和使用原则。掌握触发器的创建、执行、查看、修改和删除方法。一、触发器的概念：触发器是一种特殊类型的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过当某个事件发生时自动被触发执行的。触发器可以用于 SQLServer约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。当创建数据库对象或在数据表中插入记录、修改记录或者删除记录时， SQLServer就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些 SQL语句所定义的规则。触发器和引起触发器执行的 SQL语句被当作一次事务处理，如果这次事务未获得成功，SQLServer会自动返回该事务执行前的状态。使用触发器有以下优点：触发器是自动的执行的，。触发器可以通过数据库中的相关表进行层叠更改，如可以在“学生表”中的学号列上创建一个删除触发器，当删除“学生表”的记录时，自动级联删除“成绩表”中的个匹配行。触发器可以强制限制。这些限制比用 CHECK勺束所定义的更复杂，与 CHECK勺束不同的是，触发器可以引用其他表中的列。触发器可以分为DMLS发器和DDLM发器两种1第1页 2011年5月4日v1.0可编辑可修改v1.0可编辑可修改1717第页 2011年5月4日DDL触发器用于响应各种数据定义语言(DDL)事件。这些事件主要对应于Transact-SQL中的CREATEALTER和DROP语句，以及执行类似DDL操作的某些系统存储过程。它们用于执行管理任务，并强制影响数据库的业务规则。DML触发器是在用户使用数据操作语言 (DML)事件编辑数据时发生。 DML事件是针对表或视图的INSERT、UPDATE^DELETE语句。DMLt虫发器有助于在表或视图中修改数据时强制业务规则，扩展数据完整性。口乂1＞发器又分为AFTE例发器和INSTEADO触发器两种：AFTER虫发器：这种类型的触发器将在数据变动( INSERTUPDATEDDELET腺作)完成以后才被触发。可以对变动的数据进行检查，如果发现错误，将拒绝接受或回滚变动的数据。AFTER虫发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。INSTEADOF触发器:INSTEADOF触发器将在数据变动以前被触发，并取代变动数据的操作，而去执行触发器定义的操作。 INSTEADOF触发器可以在表或视图上定义。每个INSERTUPDATEDDELETE^句最多定义一个INSTEADOF触发器。二、创建触发器.使用SQLServer管理控制台创建口“1＞发器在SQLServer管理控制台中，展开指定的服务器和数据库，单击要创建触发器的数据表，右击其中的“触发器”文件夹。从弹出的快捷菜单中选择“新建触发器”选项，会出现新建触发器模板窗口，在新建的触发器模板窗口中单击“查询”菜单中“指定模板参数的值”选项，会弹出“指定模板参数的值”对话框，可以在这里设置参数的具体值。.使用Transact-SQL语句创建DML＞发器使用Transact-SQL语言中的CREATETRIGGER令也可以创建DMLj虫发器：CREATETRIGGER[schema_name.]trigger_nameON{table|view}[WITH＜dml_trigger_option＞[，…n]]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}AS{sql_statement[；][，…n]}<dml_trigger_option>:k[ENCRYPTION][EXECUTEASClause]其中，各参数的说明如下：schema_nameDML1虫发器所属架构的名称。trigger_name:触发器的名称。table|view:对其执行DML^发器的表或视图。WITHENCRYPTION对CREATETRIGGE语句的文本进行力口密处理。EXECUTEAS指定用于执行该触发器的安全上下文。FOR|AFTER:AFTER指定触发器仅在触发 SQL语句中指定的所有操作都已成功执行时才被触发。不能对视图定义 AFTER虫发器。INSTEADOF指定执行口“1>发器而不是触发SQL语句。{[DELETE][,][INSERT][,][UPDATE]}:指定数据修改语句。必须至少指定一个选项。sql_statement:触发条件和操作。当创建一个触发器时必须指定以下几项内容：触发器的名称；在其上定义触发器的表；触发器将何时激发；执行触发操作的编程语句。注意DMLia发器时生成的两个幻表：DML>发器可以使用两个特殊的临时表，它们分别是 inserted表和deleted表。这两个表都存在于内存中。它们在结构上类似于定义了触发器的表。在inserted表中存储着被INSERT^UPDATED句影响的新的数据行。在执行INSERT或UPDATED句时，新的数据行被添加到基本表中，同时这些数据行的备份被复制到inserted临时表中。在deleted表中存储着被DELETE^UPDATED句影响的旧数据行。在执行DELETE^UPDATED句时，指定的数据行从基本表中删除，然后被转移到了 deleted表中。在基本表和deleted表中一般不会存在相同的数据行。一个UPDATED作实际上是由一个DELETE^作和一个INSERT操作组成的。在执行UPDAT骤作时，旧的数据行从基本表中转移到 deleted表中，然后将新的数据行同时插入基本表和inserted表中。实验主要任务：导入学生成绩数据库，并建立相关表的关系，对数据库实施如下操作：一、利用SQLServer管理控制台创建口乂1＞发器中的INSERT触发器的后（AFTER触发器.利用SQL-SERVERT理控制台：创建一个INSERT触发器“TR_Course_Insert”，当在课程表中插入一条新记录时，触发该触发器，并给出“你插入了一条新的课程记录！”的提示信息。NM3-0H-1445.学生SQLqueryl叫I*援要createtriggerTRCourselnjerton课程torinsertprmc・你插入了一条新的课程记录!二消息你插入了一条新的谍程记录!.创建了TR_Course_Insert触发器后，查看向课程表中插入数据时，此触发器所完成的功2013-0227-1445, 二EQLQueryTftql*捕赛insertinto课程values'000122£卜「•数据库'mLj'W你插入了一条新的课程记录!u行受影团二、使用Transact-SQL语句创建DML的后（FORAFTER触发器（一）实现检查约束.创建一个AFTER触发器，要求实现以下功能：在成绩表上创建一个插入、更新类型的触发器TR_ScoreCheck,当在成绩字段中插入或修改考试分数后，触发该触发器，检查分数是否在0-100之间。createtriggerTR_ScoreCheckon成绩forinsert,updateasif（select成绩frominserted）between0and100beginprint'你成功的对成绩表插入（更新）了一条记录 ‘endelsebeginprint'（插入）更新的成绩数据错误 ’rollbacktransactionend在创建了TRScoreChec触发器之后，在查tU窗口中输入以下SQ踣句验证：USE学生成弟¥知—脸证插入触发器一PRIMT।在成绩表中插入记录时3跋器的执行结果/“FR工国工12IMSERTINTO咸绩4V^AIiUES（'2005226146 '1D80D24',9E0）^IJI5ERTIET0麻缆〃VALUESt'20052261461,10110961\'65）*JSO'一晚证更新触发器一PRIWT1在成赞表中更新记录时触发器的执行结果：”PHIWT1UPDATE成篇小SET成绩=-72中WERE'200522614f1A1JD奥程号:1口口5口2193UPDATE成绩+1SET成绩二78皿WERE学号=TQQ5M6L41XNP飕程号=PD5口£19"2，（独立完成：4.创建一个AFTER触发器，要求实现以下功能：在 2003学生名单表上创建一个插入、更新类型的触发器 TR_Sex_Check,当在性别字段中插入或修改性别后，触发该触发器，检查性别是否是’男’或‘女’，并验证）setANSI_NULLSONsetQUOTED_IDENTIFIERONgoALTERtrigger[TR_Sex_Check]on[dbo].[学生名单]forinsert,updateasif（select性别frominserted）in（'男'，'女'）beginprint'插入（更新）数据‘endelsebeginprint '插入或更新的性别错误’rollbacktransactionend2013-0257-1445.^...-SQLQueryd.sqh指要updare学生名单where学号一口口口口口口型£电一性别男，where学号一口口口口口口型■rT4 --S- ：'4 -IPlI1".国消息插入（更新）颤1S（1彳TS影喻2013-0227-1445J¥i_SQLQuetyS.sqr*insertSg学生名单(学号.性别卜valuea(1Q0000011,1 1)陌消息 插入（更新）数据U行受影脸（二）实现层叠修改5.创建一个AFTER虫发器，要求如下：在学生表上创建一个删除类型的触发器 TR_Stu_Delete,当在学生表中删除某一条记录后，触发该触发器，在成绩表中删除与此学号对应的记录。createtriggerTR_Stu_Deleteon学生fordeleteasbegindeletefrom成绩where学号=（select学号fromdeleted）end在创建了TR_Stu_Delete触发器之后，在查询窗口中输入以下SQ晤句验证：USE学生成绩-GOJSELECT*卡FROM成解WHERE学号二12Q052261514-GODELETEFROMWHERE12005226151*GO+JSELECTFROM感第♦WHSRE学号二'2005226151^GO'去-db)*近密201茶0227444s.学生…YQLQuoryl工wqF吊学生成绩goselect汽frcm成绩where学号l20GS2261S21godelate工工oh学生wn^re学号='z口。$工工后二52'g口select*fmm成绩where学号=1200522£L52'go1百果|如肖息学君课程号成露■ BraMin■■■»■■■■■MiiB■■■1 i20052?6152jD0M21于582 漏通支012072934字号课程成篇（独立完成：6.创建一个AFTER触发器，要求实现以下功能在 2003班级表上创建一个删除类型的触发器TR_Class_Delete,当在2003班级表中删除某一条记录后，触发该触发器，在2003学生名单中删除与此学号对应的记录。并验证）setANSI_NULLSONsetQUOTED_IDENTIFIERONgoALTERtrigger[TR_Class_Delete]on[dbo].[班级]fordeleteasbegindeletefrom学生名单where班级=(select班级fromdeleted)end课程号课程名iinnnirimii^r1 111000133秘书学三、使用Transact-SQL语句创建DML的前（INSTEADOF触发器7.创建一个INSTEADOF触发器，要求实现以下功能：在课程表上创建一个删除类型的触发器TR_NotAllowDelete,当在课程表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息。在创建了TR_Stu_Delete触发器之后，在查询窗口中输入以下SQ晤句验证：USE学生成绩小SELECT”FROM课程/WHERE曝程号='110口口133GOPDELETEFROM课程WHERE课程号11W0013"GWSELECT”FROM课程/WHERB课程号」T1口口CILW3四、查看和修改触发器（一）利用SQLServer管理控制平台查看8.使用SQLServer管理控制台查看TR_Course_Insert触发器并查看与该触发器有依赖关系的其他数据库对象9依赖于的对象电）CTR_CSt.Insert］依赖的对象置）依赖关系・立TR_Course_Lusert（二）使用系统存储过程查看触发器可以使用系统存储过程sp_help、sp_helptext和sp_depends分别查看触发器的不同信息。它们的具体用途和语法形式如下：sp_help:用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。sp_help '触发器名称’sp_helptext:用于查看触发器的正文信息。sp_depends:用于查看触发器涉及到的所有表。9.使用系统存储过程查看TR_Course_Insert触发器的一般信息2013-02"-W45.学生--SQLQuerylS.Eql*推要spnelptrcaurseinsert4 nr日结果I为消息Name Own*rTypeCre.Bted_daftetimeTR_Couree_bisert!dtwtrigger2014-12-101G;1G;M.G37n&ianiHiiiimjiiii&iinuiiuiiiiu■1■■uiHiiMiiir10.使用系统存储过程查看 TR_Stu_Delete触发器涉及到的所有表。2013-0227-1445^^...-S^LQueryl&^ql*播要sp_d.ependstr_scu_delete□结果口消息nametypeupdatedseiectaicolumn|dM.四德Iu网tableyesnoNULL11.使用系统存储过程查看 TR_Stu_Delete触发器的正文信息•"人届-"痴痴『.■”mi于工田-jiascsp_helpuex€tr_s€ii_deleLe|n结果也肖息Text1 createtriggerTR_Stu_Ddete**Liii：Eii：^-iBii—Liiis-^ai：--ii:s_jiiiii.^—aia.-sii：a.&：ii-iiib-dii叩学生fwdeleteasbegindeletefrom成where学号・财set学号frondeleted)end五、删除触发器12.用系统命令DROPTRIGGER虫发器名删除TR_Course_Inser

20133Z7-1445学生i5qLQuery154qF捷寿droprrigga*TRC廿uh占和InsertSi消息命令已成功完成°利用商品销售练习数据库阅读下面的插入存储器、触发器实例，并把代码敲入自己的界面上熟悉：FK_商品消害.商品库存商品铺售?自动墉号商品编号商品名秫商品就档单价数里金颗消生日期数据库【商品销售练习】关系图

冏品库存PK冏品库存PK商口口编p冏品名称商品规格单价库存数量最低库存数量商品销售PK自动编pFK1冏口口编会冏品名称商品规格单价数量金额销售日期用visio2010画的关系图i.）创建一个存储过程，它接收一个商品编号，显示该商品的的所有销售记录及销售总数量、金额总计值、现有库存等相关信息。-打开数据库商品销售练习USE商品销售练习GO先查询商品编号为‘005‘学生信息select*from商品销售where商品编号=’005’-执行刚刚创建的存储过程 Pro_各科成绩单EXECPro_商品编号'005'2.）创建一个触发器，能够在 “商品销售”表中插入一条记录时（只输入“商品编号"和“商品数量"）通过触发器的作用，能自动填写该记录“商品编号"对应的"商品名称"、"商品规格"、"单价"、"销售日期"，能自动计算"金额"（=单彳^*数量）填入。并实现库存不足报警。在“商品销售"表中插入记录时，能根据“商品库存”表中的对应商品记录的“库存数量”进行检查，凡销售"数量”已超出库存数量的商品记录不能插入， 并显示现有库存量并提示“库存不足” /“库存空”，凡是库存量低于最低库存量的也提示"现有库存量低于最低库量”。创建触发器代码如下：插入代码：--插入一条记录USE商品销售练习GOselect*