数据库系统及应用：触发器及实例

1、旧课回顾 触发器定义 触发器的创建（重点） 触发器的工作过程（难点） 课堂小结,第9讲 触发器,旧课回顾,例1：创建存储过程返回学生的基本情况（学号，姓名，性别，班级名，家庭住址） 例2：创建存储过程，通过输入学生的姓名返回学生选修课程的信息（课程名，成绩） 例3：创建存储过程，通过输入学号，返回两个参数cid，score分别代表此学生的课程号和成绩,无参数的存储过程,带output返回参数的存储过程,带输入参数的存储过程,触发器定义,什么是触发器？,发,触,请同学们从字 面上理解一下！,触发器定义,触发器是一类特殊的存储过程，它不能显示地被调用，它是在指定的表或视图中插入（INSERT）记录

2、，更改（UPDATE）记录或者删除（DELETE）记录时，被自动激活。,触发器的定义,触发器特点： 和特定表或视图关联 自动调用 不能被直接调用 是一个事务的部分,触发器的创建,使用Transact_SQL创建触发器 基本语法： Create trigger 触发器名 On 表名 / 视图名 For / after / instead of insert / update / delete As Sql_statement FOR 和 AFTER 是完全相等的，创建相同类型的触发器，在INSERT、UPDATE 或 DELETE 语句执行后触发P196 INSTEAD OF 触发器取消触发动作

3、，执行替代操作,触发器的创建,使用Transact_SQL创建触发器 举例：在数据库students中，创建一触发器，在 表stu_info中插入，删除记录时，能自动显示表中 c01班的学生信息。,触发器的工作过程,Inserted表和deleted表 在触发器执行的时候，会产生两个临时表：inserted 表和deleted表 Inserted表用于存储INSERT和UPDATE语句所影响的行 的副本 Deleted表用于存储DELETE和UPDATE语句所影响的行 的副本,触发器的工作过程,在对触发器的表进行操作时，其操作过程如下： 执行INSERT操作：插入到触发器表中的新行被插入到 i

4、nserted 表中。 执行DELETE操作：从触发器表中删除的行被插入到 deleted 表中。 执行UPDATE操作：先从触发器表中删除旧行，然后再 插入新行。其中被删除的旧行被插入到deleted 表中，插 入的新行被插入到inserted表中。,触发器的工作过程,在班级表上创建触发器，测试进行插入，修改，删除数据时触发器表中的数据的情况。,INSERT 触发器的工作过程,例1：创建一触发器，在class表中插入班级人数的同时更新department表中的系别中的人数,INSERT 触发器的工作过程（续）,INSERT 触发器的工作过程（续）,在定义了 INSERT 触发器的表上执行 I

5、NSERT 语句,INSERT INTO class VALUES (c12,06net2,d02,45),class,Class_ID,c01 c02 c03,Class_name, . .,Dept_id,d01 d01 d01,Class_rs,4239 45,.t, ,UPDATE department Set dept_rs = dept_rs +inserted.class_rs FROM inserted,department WHEREinserted.dept_id =department.dept_id,INSERT 触发器的工作过程（续）,请同学们完成下列触发器的创建：

6、1、先创建course表的副本course_bak，在course表 上创建触发器当在course表中插入数据的同时在 course_bak表中备份相同数据。 2、创建一触发器，当向stu_score表插入一记录时， 检查该记录的学号在stu_info表中是否存在且课程号 在course表中是否存在，如有一项不成立，则不允许 插入,INSERT 触发器的工作过程（续）,参考答案： 2、create trigger tri_insert on stu_score for insert as If exists(select * from inserted a where a.s_id not i

7、n(select b.s_id from stu_inf b) or a.c_id not in (select c.c_id from course c) Begin raiserror(违背数据的完整性,16,1) rollback transaction End,DELETE 触发器的工作过程,举例： 创建一触发器，当删除stu_info学生表中一位学生信息 同时更新学生所在班级的class表中的班级人数。,DELETE 触发器的工作过程（续）,在定义了 DELETE 触发器的表上执行 DELETE 语句,DELETE stu_info WHERE Stu_ID = 4,UPDATE c

8、lass SET class_rs =class_rs- 1 FROM deleted,class WHERE deleted.class_id=class.class_id,DELETE 触发器的工作过程（续）,请同学们完成下列题目： 创建触发器，当删除学生信息表中某学生的信息的同时删除学生成绩表中此学生的相关信息,DELETE 触发器的工作过程（续）,参考答案： Create trigger tri_del On stu_info For delete As Delete stu_score From stu_score,deleted Where stu_score.s_id=delet

9、ed.s_id go,UPDATE 触发器的工作过程,举例： 创建一触发器，当修改学生信息表的班级编号时，如果班级编号在班级表中不存在，提示错误，撤消操作,UPDATE 触发器的工作过程（续）,在定义了 UPDATE 触发器的表上执行 UPDATE 语句,UPDATE stu_info SET class_ID = c03 WHERE s_ID = 2,UPDATE 语句被作为 INSERT 和 DELETE 语句记录,Stu_info,S_ID,S_Name,sex,Class_id,.,1 2 3 4,谢梦麟 黄亮 钟雄红 叶荣斌,男 男 男 男,c01. c01 C01 c01, ,UP

10、DATE 触发器的工作过程（续）,参考答案： CREATE TRIGGER stu_Update ON stu_info FOR UPDATE AS IF UPDATE (class_ID) begin if exists (select class_id from inserted where class_id not in (select class_id from class) begin rollback tran raiserror(编级号不存在，不允许修改,16,1) end end,UPDATE 触发器的工作过程（续）,请同学们完成下列题目： 1、创建一触发器，用来维护数据完整性

11、，检查stu_score中插入或修改的成绩是否在0到100之间。 2、创建一触发器，如果修改stu_score表的学号列，课程号列以及成绩列，则给出提示信息，并取消修改操作 3、创建一触发器，如果将不及格的成绩做修改时，给出提示信息不及格成绩不允许修改并取消操作。 4、创建一触发器，用于在向stu_info表中插入或修改数据时，检查stu_phone字段的长度不大于10位。,UPDATE 触发器的工作过程（续）,参考答案： 1、create trigger check_score On stu_score For insert,update As Declare score int Selec

12、t score=score from inserted If score100 Begin rollback raiserror(成绩必须在0到100之间，16，1) end,UPDATE 触发器的工作过程（续）,参考答案： 2、create trigger check_update On stu_score For update As If(columns_updated()&7)0 Begin raiserror(数据不允许修改，16，1) rollback transaction end,UPDATE 触发器的工作过程（续）,If(columns_updated()&7)0 If(col

13、umns_update()子句用于测试是否插入或更新了指定的列。 &：用于比较运算的位运算符 7：为整型的位屏蔽码，若检查第一列，第二列，第三列数据是否都有更新，可指定位屏蔽码00000111（20+21+22=7） ：检查指定的任一列或某些列是否已更新 0：为列屏蔽码，用来检查是否已更新或插入了对应列,触发器综合实例,利用触发器维护业务规则： 创建员工表emp（emp_id,emp_name,age,work_year）和员工工资表emp_salary(emp_id,salary)，当用户向emp中插入一个新员工时，就会触发其上面的触发器tri_emp，然后触发器再向表emp_salary中插入员工的工资信息，工资=基本工资+工龄工资，工龄工资=40*工龄，基本工资根据年龄来确定，30岁以下2000元，30到50岁2500元，50岁以上3000元。,触发器综合实例,create trigger tri_emp on emp for insert as declare age int,workyear int,empid char(8) select empid=emp_id,age=age,workyear=workyear from inserted if(age30) begi