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

1、实验11 数据库触发器的建立实验日期和时间：2014.11.28实验室：软件工程室班级：12计科3学号：20124122姓名：张翔实验环境：1.硬件：内存 4.00gb 处理器2.50hz2.软件：windows 7旗舰版microsoft sql server 2005实验原理：1. 理解触发器的概念、和一般存储过程的区别、触发器的优点和使用原则。2. 掌握触发器的创建、执行、查看、修改和删除方法。一、触发器的概念：触发器是一种特殊类型的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过当某个事件发生时自动被触发执行的。触发器可以用于sql server约束、默认值和规则

2、的完整性检查，还可以完成难以用普通约束实现的复杂功能。当创建数据库对象或在数据表中插入记录、修改记录或者删除记录时，sql server就会自动执行触发器所定义的sql语句，从而确保对数据的处理必须符合由这些sql语句所定义的规则。触发器和引起触发器执行的sql语句被当作一次事务处理，如果这次事务未获得成功，sql server会自动返回该事务执行前的状态。使用触发器有以下优点： 触发器是自动的执行的，。 触发器可以通过数据库中的相关表进行层叠更改，如可以在“学生表”中的学号列上创建一个删除触发器，当删除“学生表”的记录时，自动级联删除“成绩表”中的个匹配行。 触发器可以强制限制。这些限制比用

3、check约束所定义的更复杂，与check约束不同的是，触发器可以引用其他表中的列。触发器可以分为dml触发器和ddl触发器两种 ddl触发器用于响应各种数据定义语言 (ddl) 事件。这些事件主要对应于 transact-sql 中的create、alter 和 drop 语句，以及执行类似 ddl 操作的某些系统存储过程。它们用于执行管理任务，并强制影响数据库的业务规则。 dml触发器是在用户使用数据操作语言 (dml) 事件编辑数据时发生。dml 事件是针对表或视图的 insert、update 或 delete 语句。dml触发器有助于在表或视图中修改数据时强制业务规则，扩展数据完整性

4、。dml触发器又分为after触发器和instead of触发器两种： after触发器：这种类型的触发器将在数据变动（insert、update和delete操作）完成以后才被触发。可以对变动的数据进行检查，如果发现错误，将拒绝接受或回滚变动的数据。after触发器只能在表上定义。在同一个数据表中可以创建多个after触发器。 instead of触发器：instead of触发器将在数据变动以前被触发，并取代变动数据的操作，而去执行触发器定义的操作。instead of触发器可以在表或视图上定义。每个insert、update和delete语句最多定义一个instead of触发器。二、创

5、建触发器1. 使用sql server管理控制台创建dml触发器在sql server管理控制台中，展开指定的服务器和数据库，单击要创建触发器的数据表，右击其中的“触发器”文件夹。从弹出的快捷菜单中选择“新建触发器”选项，会出现新建触发器模板窗口，在新建的触发器模板窗口中单击“查询”菜单中“指定模板参数的值”选项，会弹出“指定模板参数的值”对话框，可以在这里设置参数的具体值。2. 使用transact-sql语句创建dml触发器使用transact-sql语言中的create trigger命令也可以创建dml触发器：create trigger schema_name . trigger_n

6、ame on table | view with ,.n for | after | instead of insert , update , delete as sql_statement ; ,.n := encryption execute as clause 其中，各参数的说明如下： schema_name：dml触发器所属架构的名称。 trigger_name：触发器的名称。 table | view：对其执行dml触发器的表或视图。 with encryption：对create trigger语句的文本进行加密处理。 execute as：指定用于执行该触发器的安全上下文。 fo

7、r | after：after指定触发器仅在触发sql语句中指定的所有操作都已成功执 行时才被触发。不能对视图定义after触发器。 instead of：指定执行dml触发器而不是触发sql语句。 delete , insert , update：指定数据修改语句。必须至少指定一个选项。 sql_statement：触发条件和操作。当创建一个触发器时必须指定以下几项内容： 触发器的名称； 在其上定义触发器的表； 触发器将何时激发； 执行触发操作的编程语句。注意dml触发器时生成的两个幻表： dml触发器可以使用两个特殊的临时表，它们分别是inserted表和deleted表。这两个表都存在于

8、内存中。它们在结构上类似于定义了触发器的表。 在inserted表中存储着被insert和update语句影响的新的数据行。在执行insert或update语句时，新的数据行被添加到基本表中，同时这些数据行的备份被复制到inserted临时表中。 在deleted表中存储着被delete和update语句影响的旧数据行。在执行delete或update语句时，指定的数据行从基本表中删除，然后被转移到了deleted表中。在基本表和deleted表中一般不会存在相同的数据行。 一个update操作实际上是由一个delete操作和一个insert操作组成的。在执行update操作时，旧的数据行从基

9、本表中转移到deleted表中，然后将新的数据行同时插入基本表和inserted表中。实验主要任务：导入学生成绩数据库，并建立相关表的关系，对数据库实施如下操作：一、利用sql server管理控制台创建dml触发器中的insert触发器的后（after）触发器1. 利用sql-server管理控制台：创建一个insert触发器“tr_course_insert”，当在课程表中插入一条新记录时，触发该触发器，并给出“你插入了一条新的课程记录！”的提示信息。2. 创建了tr_course_insert触发器后，查看向课程表中插入数据时，此触发器所完成的功能。二、使用transact-sql语句创

10、建dml的后（for、after）触发器（一）实现检查约束3. 创建一个after触发器，要求实现以下功能：在成绩表上创建一个插入、更新类型的触发器tr_scorecheck，当在成绩字段中插入或修改考试分数后，触发该触发器，检查分数是否在0-100之间。create trigger tr_scorecheckon 成绩for insert,updateasif (select 成绩from inserted) between 0 and 100 beginprint 你成功的对成绩表插入（更新）了一条记录endelsebeginprint （插入）更新的成绩数据错误rollback tran

11、sactionend 在 创建了tr_scorecheck触发器之后，在查询窗口中输入以下sql语句验证： （独立完成：4.创建一个after触发器，要求实现以下功能：在2003学生名单表上创建一个插入、更新类型的触发器tr_sex_check，当在性别字段中插入或修改性别后，触发该触发器，检查性别是否是男或女，并验证）set ansi_nulls onset quoted_identifier ongoalter trigger tr_sex_checkon dbo.学生名单for insert,updateasif (select 性别from inserted) in (男,女)begi

12、nprint 插入（更新）数据endelsebeginprint 插入或更新的性别错误rollback transactionend(二）实现层叠修改5.创建一个after触发器，要求如下：在学生表上创建一个删除类型的触发器tr_stu_delete，当在学生表中删除某一条记录后，触发该触发器，在成绩表中删除与此学号对应的记录。create trigger tr_stu_deleteon 学生for deleteasbegindeletefrom 成绩where 学号=(select 学号from deleted)end在 创建了tr_stu_delete触发器之后，在查询窗口中输入以下sql

13、语句验证：（独立完成：6.创建一个after触发器，要求实现以下功能在2003班级表上创建一个删除类型的触发器tr_class_delete，当在2003班级表中删除某一条记录后，触发该触发器，在2003学生名单中删除与此学号对应的记录。并验证）set ansi_nulls onset quoted_identifier ongoalter trigger tr_class_deleteon dbo.班级for deleteasbegindeletefrom 学生名单where 班级=(select 班级from deleted)end三、使用transact-sql语句创建dml的前（ins

14、tead of）触发器7.创建一个instead of触发器，要求实现以下功能：在课程表上创建一个删除类型的触发器tr_notallowdelete，当在课程表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息。在 创建了tr_stu_delete触发器之后，在查询窗口中输入以下sql语句验证：四、查看和修改触发器 （一）利用sql server管理控制平台查看8. 使用sql server管理控制台查看tr_course_insert触发器并查看与该触发器有依赖关系的其他数据库对象（二）使用系统存储过程查看触发器可以使用系统存储过程sp_help、sp_helptext和sp_de

15、pends分别查看触发器的不同信息。它们的具体用途和语法形式如下：sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。sp_help 触发器名称sp_helptext：用于查看触发器的正文信息。sp_depends：用于查看触发器涉及到的所有表。9. 使用系统存储过程查看tr_course_insert触发器的一般信息10. 使用系统存储过程查看tr_stu_delete触发器涉及到的所有表。11. 使用系统存储过程查看tr_stu_delete触发器的正文信息五、删除触发器12.用系统命令drop trigger 触发器名 删除tr_course_inser利用商

16、品销售练习数据库阅读下面的插入存储器、触发器实例，并把代码敲入自己的界面上熟悉：数据库【商品销售练习】关系图用visio 2010画的关系图1.)创建一个存储过程，它接收一个商品编号，显示该商品的的所有销售记录及销售总数量、金额总计值、现有库存等相关信息。-打开数据库商品销售练习use 商品销售练习go-先查询商品编号为005学生信息select * from 商品销售 where 商品编号=005-执行刚刚创建的存储过程pro_各科成绩单exec pro_商品编号 0052.）创建一个触发器，能够在商品销售表中插入一条记录时(只输入商品编号和商品数量)通过触发器的作用，能自动填写该记录商品编

17、号对应的商品名称、商品规格、单价、销售日期，能自动计算金额（=单价*数量）填入。并实现库存不足报警。在商品销售表中插入记录时，能根据商品库存表中的对应商品记录的库存数量进行检查，凡销售数量已超出库存数量的商品记录不能插入，并显示现有库存量并提示“库存不足”/“库存空”，凡是库存量低于最低库存量的也提示现有库存量低于最低库量。创建触发器代码如下：插入代码：-插入一条记录use 商品销售练习goselect * from 商品库存 where 商品编号=005goselect * from 商品销售goinsert into 商品销售(商品编号,数量) values(005,18)goselect

18、 * from 商品库存 where 商品编号=005goselect * from 商品销售 go将以上任务的实验完成情况、实验结果、实验原理、总结分栏一一填写到下表中，格式参考任务1或者自定。回答问题： 1触发器与一般的存储过程的主要区别是什么？ 触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如update、insert、delete这些操作时，sqlserver就会自动执行触发器所定义的sql语句，从而确保对数据的处理必须符合这些sql语句所定义的规则 2简述触发器的分类及各自的特点。 dml触发器，ddl触发器 3使用触发器有哪些优点？ 自动执行 级联更新 强化约束 跟踪变化 强制业务逻辑小结：（不少于100字）你在完成查询任务的过程中遇到的问题及解决的方法有：要求：1. 报告格式和内容要求： a. 内容和格式整齐。大标题采用黑体四号字加粗，小标题采用小四号字加粗。正文采用五号宋体，单倍行距。 b. 贴图时请剪裁到适当大小，要保证打印时可以看清，