《new第10章触发器的创建和使用.ppt》由会员分享，可在线阅读，更多相关《new第10章触发器的创建和使用.ppt（37页珍藏版）》请在三一办公上搜索。
1、2023/11/8,1,第10章 触发器的创建和使用,2,第10章 触发器的创建和使用,本章学习目标了解触发器和一般存储过程的主要区别了解使用触发器的优点掌握创建触发器的方法掌握查看触发器信息的方法掌握修改和删除触发器的方法,3,第10章 触发器的创建和使用,10.1 概述,1,10.2 创建触发器,2,10.3 查看、修改和删除触发器,2,3,3,4,10.1 概述,10.1.1 触发器的概念触发器是一种特殊类型的存储过程。它在指定的表中的数据发生变化时自动生效。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过当某个事件发生时自动被触发执行的。触发器可以用于SQL Server约
2、束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。,5,10.1 概述,触发器与存储过程的区别：一般的存储过程由用户根据需要通过存储过程名称被调用调用的，而触发器主要是通过事件（执行INSERT、UPDATE或DELETE语句）进行触发而自动执行的。存储过程不依赖于某个表而单独存在，而触发器必须基于一个表或视图创建。,6,10.1 概述,10.1.2 触发器的优点 使用触发器有以下优点：触发器是自动执行的。触发器可以通过数据库中的相关表进行层叠更改。触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同，触发器可以引用其它表中的列。触发器也可以评
3、估数据修改前后的表状态，并根据其差异采取对策。,7,10.1 概述,10.1.3 触发器的类型 在SQL Server 2005中，触发器可以分为DML触发器和DDL触发器两种：DDL触发器用于响应各种数据定义语言(DDL)事件。这些事件主要对应于 Transact-SQL 中的CREATE、ALTER 和 DROP 语句，以及执行类似 DDL 操作的某些系统存储过程。它们用于执行管理任务，并强制影响数据库的业务规则。DML触发器是在用户使用数据操作语言(DML)事件编辑数据时发生。DML 事件是针对表或视图的 INSERT、UPDATE 或 DELETE 语句。DML触发器有助于在表或视图中
4、修改数据时强制业务规则，扩展数据完整性。,8,10.1 概述,DML触发器又分为AFTER触发器和INSTEAD OF触发器两种：AFTER触发器：这种类型的触发器将在数据变动（INSERT、UPDATE和DELETE操作）完成以后才被触发。AFTER触发器只能在表上定义。INSTEAD OF触发器：INSTEAD OF触发器将在数据变动以前被触发，并取代变动数据的操作，而去执行触发器定义的操作。,本章首页,9,10.2 创建触发器,在创建触发器之前应该考虑以下几个问题：CREATE TRIGGER 必须是批处理中的第一条语句。触发器只能在当前的数据库中创建。TRUNCATE TABLE语句不
5、会引发DELETE触发器。WRITETEXT语句不会引发INSERT或UPDATE触发器。,10,10.2 创建触发器,当创建一个触发器时必须指定以下几项内容：触发器的名称；在其上定义触发器的表；触发器将何时激发；执行触发操作的编程语句。,11,10.2.1 使用SQL Server管理控制台创建DML触发器方法：打开相应表 右击“触发器”文件夹 新建触发器,10.2 创建触发器,12,【例10-1】创建一个INSERT触发器“TR_Stu_Insert”，当在T_STUDENT表中插入一条新记录时，触发该触发器，并给出“你插入了一条新记录！”的提示信息。见P183-185,10.2 创建触发
6、器,13,10.2 创建触发器,【例10-2】创建了TR_Stu_Insert触发器后，查看向T_STUDENT表中插入数据时，此触发器所完成的功能。,USE STUDENTGOinsert into T_STUDENT(S_NUMBER,S_NAME,SEX)values(0554115,王小帆,女)GO,14,10.2 创建触发器,10.2.2 使用Transact-SQL语句创建DML触发器可以使用CREATE TRIGGER命令创建DML触发器。语法形式：CREATE TRIGGER trigger_name ON table|view WITH,.n FOR|AFTER|INSTEA
7、D OF INSERT,UPDATE,DELETE AS sql_statement,n:=ENCRYPTION EXECUTE AS Clause,15,10.2 创建触发器,参数说明：WITH ENCRYPTION：对CREATE TRIGGER语句的文本进行加密处理。EXECUTE AS：指定用于执行该触发器的安全上下文。FOR|AFTER：AFTER指定触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被触发。不能对视图定义AFTER触发器。INSTEAD OF：指定执行DML触发器而不是触发SQL语句。DELETE,INSERT,UPDATE：指定数据修改语句。必须至少指定一个
8、选项。sql_statement：触发条件和操作。,16,10.2 创建触发器,inserted表和deleted 表：在创建触发器时可使用两个特殊的临时表：deleted 表和 inserted表，它们存在于内存中；inserted 表和 deleted表是逻辑（概念）表。它们在结构上类似于定义触发器的表（也就是用户在其中尝试操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。inserted表存储被INSERT和UPDATE语句影响的新的数据行；deleted表存储被DELETE和UPDATE语句影响的旧的数据行。一个UPDATE操作相当于一个删除旧记录、插入新记录的组合操作，所以
9、要用到INSERTED和DELETED表。,17,inserted表和deleted表存放的信息,10.2 创建触发器,18,（1）inserted 表：临时保存被INSERT和UPDATE语句影响的新的数据行。即在执行 INSERT或UPDATE语句时，新的数据行被添加到数据表中，同时这些新的数据行也被复制到inserted临时表中。,10.2 创建触发器,inserted表工作示意图,19,10.2 创建触发器,（2）deleted 表：临时保存了被DELETE和UPDATE语句影响旧的数据行。即在执行DELETE或UPDATE语句时，从数据表中删除旧的数据行，然后将这些旧的数据行存入de
10、leted表中。,deleted表工作示意图,20,10.2 创建触发器,【例10-3】创建一个AFTER触发器，要求实现以下功能：在T_SCORE表上创建一个插入、更新类型的触发器TR_ScoreCheck，当在SCORE字段中插入或修改考试分数后，触发该触发器，检查分数是否在0-100之间。,21,10.2 创建触发器,CREATE TRIGGER TR_ScoreCheckON T_SCOREFOR INSERT,UPDATE ASIF UPDATE(SCORE)PRINT AFTER触发器开始执行BEGIN DECLARE ScoreValue real SELECT ScoreVal
11、ue=(SELECT SCORE FROM inserted)IF ScoreValue100 OR ScoreValue0 PRINT 输入的分数有误，请确认输入的考试分数！END,22,10.2 创建触发器,（接上页）-验证TR_ScoreCheck触发器的功能USE studentGO PRINT 在t_score中插入记录时触发器执行结果：PRINT INSERT INTO t_scoreVALUES(0554112,10010218,-35)INSERT INTO t_scoreVALUES(0554113,100102183,152)GO（转下页）,23,10.2 创建触发器,（接
12、上页）PRINT 在t_score中修改记录时触发器执行结果：PRINT UPDATE t_scoreSET score=115WHERE s_number=0554112UPDATE t_scoreSET score=-65WHERE s_number=0554113GO,24,10.2 创建触发器,【例10-4】创建一个AFTER触发器，要求如下：在T_STUDENT表上创建一个删除类型的触发器TR_Stu_Delete，当在T_STUDENT表中删除某一条记录后，触发该触发器，在T_SCORE表中删除与此学号对应的记录。（设t_student表与t_score表之间无外键约束）,25,1
13、0.2 创建触发器,CREATE TRIGGER TR_Stu_DeleteON T_STUDENT FOR DELETEAS PRINT 删除触发器开始执行DECLARE StuNum char(10)PRINT 把在T_STUDENT表中删除的记录的S_NUMBER赋值给局部变量StuNum。SELECT StuNum=S_NUMBER FROM deletedPRINT 开始查找并删除T_SCORE表中的相关记录DELETE FROM T_SCORE WHERE S_NUMBER=StuNumPRINT 删除了T_SCORE表中的学号为+RTRIM(StuNum)+的记录。,26,10.
14、2 创建触发器,-验证Stu_Delete触发器的功能USE studentGO PRINT 在t_score中插入记录时触发器执行结果：PRINT DELETE FROM t_student WHERE s_number=0554112DELETE FROM t_student WHERE s_number=0554113GO,27,10.2 创建触发器,【例10-5】创建一个INSTEAD OF触发器，要求实现以下功能：在T_COURSE表上创建一个删除类型的触发器TR_NotAllowDelete，当在T_COURSE表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息。,28
15、,10.2 创建触发器,USE STUDENTGOCREATE TRIGGER TR_NotAllowDeleteON T_COURSEINSTEAD OF DELETEAS PRINT INSTEAD OF 触发器开始执行 PRINT 本表中的数据不允许被删除！不能执行删除操作!GO-验证NotAllowDelete触发器的功能USE studentGO DELETE FROM t_course WHERE c_number=20010203GO,本章首页,29,10.3 查看、修改和删除触发器,10.3.1 查看并修改触发器1使用SQL Server管理控制台查看并修改触发器（1）查看并修
16、改触发器定义信息,30,10.3 查看、修改和删除触发器,（2）查看与触发器有依赖关系的其他数据库对象,31,10.3 查看、修改和删除触发器,2使用系统存储过程查看触发器sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。sp_help 触发器名称sp_helptext：用于查看触发器的正文信息。sp_helptext 触发器名称sp_depends：用于查看指定触发器所引用的表。sp_depends 触发器名称,32,10.3 查看、修改和删除触发器,【例10-6】使用系统存储过程查看TR_Stu_Insert触发器的一般信息。sp_help TR_Stu_In
17、sert【例10-7】使用系统存储过程查看T_STUDENT表涉及到的所有触发器。sp_depends TR_Stu_Delete,33,10.3 查看、修改和删除触发器,10.3.2 删除触发器1使用SQL Server管理控制台删除触发器,34,10.3 查看、修改和删除触发器,2使用Transact-SQL语句删除触发器使用系统命令DROP TRIGGER删除指定的触发器。语法形式：DROP TRIGGER trigger,.n【例10-8】使用系统命令删除TR_Stu_Insert触发器。DROP TRIGGER TR_Stu_Insert,35,10.3 查看、修改和删除触发器,3直接删除触发器所在的表删除触发器的另一个方法是直接删除触发器所在的表。删除表时，SQL Server将会自动删除与该表相关的所有触发器。,本章首页,36,10.4 思考与练习,1触发器与一般的存储过程的主要区别是什么？2简述触发器的分类及各自的特点。3使用触发器有哪些优点？4使用Transact-SQL语句创建一个INSERT触发器，完成的功能是当在T_SCORE表中插入或修改S_NUMBER列的数据时，检测在T_STUDENT表中是否存在相应的S_NUMBER值，如果不存在，则给出相应的提示信息。5删除触发器的方法有哪几种？并分别举例说明。,2023/11/8,37,谢谢使用，再见!,
