《数据库原理实验报告S11数据库触发器的创建》由会员分享,可在线阅读,更多相关《数据库原理实验报告S11数据库触发器的创建(15页珍藏版)》请在金锄头文库上搜索。
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_depends分别查看触发器的不同信息。它们的具体用途和语法形式如下:sp_help:用于查看触发器的一般信息,如触发器的名称、属性、类型和创建时间。sp_help 触发器名称sp_helptext:用于查看触发器的正文信息。sp_depends:用于查看触发器涉及到的所有表。9. 使用系统存储过程查看TR_Course_Insert触发器的一般信息10. 使用系统存储过程查看TR_Stu_Delete触发器涉及到的所有表。11. 使用系统存储过程查看TR_Stu_Delete触发器的正文信息五、删除触发器12.用系统命令DR