第5章数据库完整性之触发器(new).ppt

1、An Introduction to Database System,江西师范大学软件学院,第五章 数据库完整性,2,2,本次课教学重点： 触发器的概念 触发器的功能和类型 使用Transact SQL语句创建和管理触发器,3,3,引子,问题描述： 在某校教务管理系统中有一个S_T数据库。 学生student(学号sno，姓名sname，性别ssex，年龄sage,所在系 sdept） 课程course（课程号cno，课程名cname，学分ccredit） 选修sc（学号sno，课程号cno，成绩grade）,4,4,复习,1,对于选修课sc中的成绩要控制在0到100分之间，该如何实现？,5,

2、5,引子,1,张杰老师新开设了一门“数据库”选修课，由于教室容量有限，这个班的人数不能超过60人。,那我们又该如何实现呢？ 还可以用check可以吗？,6,6,触发器的定义,触发器是一种特殊类型的存储过程，与表或视图紧密相连。当用户对表或视图进行INSERT、UPDATE或DELETE操作时，触发器将被自动触发并执行。它也可以用来实现完整性约束，而且可以实现主键、外键、约束及默认值所不能保证的复杂的数据完整性。 存储过程需要用EXECUTE语句执行，而触发器主要由操作事件（insert、update、delete等）触发并由SQL Server自动执行，不能直接调用执行，也不能被传送和接收参数

3、。 触发器创建时，总是与特定的SQL Server事件相关联 。,7,触发器的优点,使用触发器的优点： 1、触发器能够实现数据库间数据的级联更新和级联删除，从而实现主键与外键间的级联，确保参照完整性。 2、触发器能够强制执行比外键参照完整性、CHECK约束更为复杂的业务逻辑。如可以使用IFELSE语句。 3、触发器可以比较表修改前后数据间的差别，并根据差别采取相应的操作。 4、对一个表中的多个不同类的触发器允许采取多种不同的对策，以响应一个修改语句。,8,触发器的类型,触发器的类型 1、AFTER触发器（后触发） 后触发是指执行某一操作（如insert、delete、update）之后，触发器

4、才被触发。SQL Server在引发触发器的操作语句已经完成，并通过各类约束验证后才会执行触发器的语句；如果操作语句因语法有错或违反了约束而导致执行失败，触发器将不会执行。 注意：after类型的触发器只能定义在表上，不能定义在视图上。,9,触发器的类型,触发器的类型 2、INSTEAD OF触发器（替代触发） 该类型触发器并不去执行诸如insert、update、delete语句所定义的操作，而只是去执行触发器本身。即由触发器的代码去替代引发触发器的T-SQL语句的执行，而该语句自身却不再被执行。 注意：instead of触发器既可定义在表上，也可定义在视图上。 一个表上可以定义多个aft

5、er触发器；但只能在一个表或视图上定义一个instead of 触发器。,10,触发器中的特殊表,触发器中的两个特殊表 每个触发器触发时，系统都将在内存中自动创建两个特殊的临时表inserted表和deleted表。这两个表的结构和触发器所关联的表的结构一致。 可以在触发器内部用这两个表去测试某些数据修改的效果，或设置触发器操作的条件。这两个表的内容只能查询，不能修改。 当触发器执行完毕后， inserted表和deleted表由系统自动删除。,11,触发器中的特殊表,表5.1 Inserted表与deleted表存放的信息,12,12,定义触发器,语法格式： CREATE TRIGGER O

6、N with encryption FOR | AFTER | INSTEAD OF AS - with encryption子句指定将create trigger 语句文本的条目进行加密。 -FOR与AFTER关键字含义相同，都是指后触发器。 - INSTEAD OF指替代触发器，指定用触发器中的操作代替触发语句的操作； - 指 INSERT、DELETE、UPDATE操作，一次可以指定多个触发操作，它们之间用逗号分隔，但至少要指定一种触 发操作。 - 用来定义触发器所执行的操作与行为，是触发器的核心。,13,After Insert 触发器,当向表中插入数据时，若该记录满足各种约束条件，将

7、触发Insert触发器。 Insert触发器执行下列操作： 新行会同时插入到Inserted表及触发器关联的表中。 执行触发器代码，检查inserted表中的新行，确定是否要阻止该插入操作。若要阻止，由从触发器表中回滚新插入的行。 注意：如果在表中既设置有约束又定义了触发器，则对表执行相应的修改操作时，约束将优先于触发器。如果在操作中触发器与约束发生冲突，触发器将被屏蔽，不再执行。,13,14,例1：,14,15,例2：对于数据库stu，创建一个触发器，当向sc表中插入数据时，显示inserted表和sc表中的数据。,15,16,16,例3：对于数据库stu，创建一个触发器，student 表

8、中插入的学生的年龄不能大于35，如果大于35，禁止插入数据，并在客户端显示学生年纪不能大于35。,以上例子对插入一条记录正确，但插入多条记录就会报错，因为查询出来的sage是个集合，集合不能与一个常量比较，应该把sage改为max(sage),17,运行结果如下：,注意：若一个表的某种更新操作上创建了多个after类型的触发器，则执行该更新语句时，这些触发器会按创建的时间顺序依次触发。,18,18,现在我们可以来解决这个问题了,1,张杰老师新开设了一门“数据库”选修课，由于教室容量有限，这个班的人数不能超过60人。,第一步先得到选修了“数据库”课程的学生人数,19,19,现在我们可以来解决这个

9、问题了,1,张杰老师新开设了一门“数据库”选修课，由于教室容量有限，这个班的人数不能超过60人。,20,20,例4：对于数据库stu中的数据表sc，创建一个触发器，插入记录时，当字段grade小于60时，自动改为0；,21,After Update 触发器,当试图更新表中的数据时，将执行update触发器 Update 触发器执行下列操作： 将原始数据行移到逻辑表的deleted表中 将一个新行插入inserted表中，然后插入触发器表中 计算deleted表和inserted表中的值以确定是否需要进行干预 可以创建update触发器以验证对单个列或整个表的更新,21,22,例5：对于数据库S

10、_T，创建一个触发器，当向sc表中修改数据时，显示inserted表，deleted和sc表中的数据。,22,23,23,例6：对于数据库S_T，创建一个触发器，禁止修改student 表中的学号，并在客户端显示不能修改学号。,24,例7：对于数据库S_T，创建一个触发器，学生表中的年龄修改范围在10岁之内。,24,如果一次不只修改一条语句，就不能这样做，要用以下语句实现。,25,例7适合多条记录的修改,26,After Delete 触发器,当试图从表中删除数据时，将执行delete触发器 Delete触发器执行下列操作： 从触发器表中删除行。 将删除的行插入到deleted表中。 检查de

11、leted表中的行，以确定是否需要或应如何执行触发器操作。,26,27,例8：对于数据库s_t，创建一个触发器，当从sc表中删除数据时，显示deleted和sc表中的数据。,27,28,例9：对于数据库S_T，创建一个触发器，当从sc表中删除数据时，禁止删除200215121学生的成绩的数据。,28,29,例10：对于表student的插入、删除、修改操作创建一个触发器，当执行相应的这些更新操作时，都会触发该触发器。,30,Instead of 触发器,包含代替原始数据操作语句的代码，也包括三种类型：instead of insert触发器、instead of delete触发器、inste

12、ad of update触发器。 不仅可以针对表，也可以针对视图创建instead of触发器，每个表的每种操作只能创建一个instead of触发器。 主要优点是可以使不能更新的视图支持更新 另外，可以拒绝批处理中的某些部分而允许批处理的其它部分成功 如果一个表的外键在delete与update操作上定义了参照完整级联，则不能在该表上再定义instead of delete触发器和instead of update触发器。,30,31,例11：对于表student创建一个instead of触发器，如果插入的学生是CS系就允许插入，否则不插入。,32,当企图向student表插入记录时，触发

13、器student_ins_instead被触发，新数据行的备份被存储到inserted表中，并且触发器中定义的行为将替代该插入语句。 注意：当一个表的某些更新操作既有after触发器，又有instead of 触发器，则instead of 触发器优先执行。,33,33,例11：对于数据库S_T，创建一个触发器，不允许对student删除或修改记录时，同时客户端显示不允许增加或修改记录。,34,34,例12：已知系统中存在视图view_sc （姓名，课程名，成绩），如何实现通过该视图来插入学生选课成绩(李贵,数学,79)。,能否通过这条语句来实现呢？,35,35,36,36,例13：对于数据库

14、S_T，创建一个触发器，实现删除学生信息时，级联删除该学生的选课信息。,37,37,38,使触发器失效或重新有效,在有些情况下，用户希望暂停触发器的作用，但不删除它，这时候可以通过disable trigger使其失效。,38,39,使触发器失效或重新有效,可以通过enable trigger使其重新有效。,39,40,用系统存储过程查看触发器,sp_help： 系统存储过程“sp_help”可以了解如触发器名称、类型、创建时间等基本信息，其语法格式为： sp_help 触发器名 sp_helptext： 系统存储过程“sp_helptext”可以查看触发器的文本信息，其语法格式为： sp_helptext 触发器名,40,41,41,42,42,43,43,2）修改触发器,语法格式： Alter trigger trigger_name on ( table | view ) with encryption for delete , insert , update As sql_statement n ,44,例14：对于数据库stu，修改del_student_sc触发器的定义，将其定义进行加密。,44,45,3)删除触发器,语法格式： drop trigger trigger_name ,n 该语句可