触发器的创建与使用.ppt

数据库基础与应用 第10章 触发器的创建和使用,2019/5/31,相关知识回顾,1、什么是存储过程？ 2、创建存储过程的基本语法如何？ 3、如何调用存储过程？ 4、数据库中有哪6种约束？,2019/5/31,触发器与存储过程、约束的关系,触发器是建立在表上的特殊的存储过程。 当对表进行插入、删除、更新操作时，触发器将会自动执行。 利用触发器可以保证表中数据的一致性和完整性，功能比约束更强。,2019/5/31,本章学习目标,了解触发器的概念和优点 理解触发器的工作原理 熟练掌握如何创建INSERT触发器、UPDATE触发器、DELETE触发器 掌握如何查看、修改和删除触发器,2019/5/31,第10章 触发器的创建和使用,10.1 概述 10.2 创建触发器 10.3 查看、修改和删除触发器 10.4 综合实例 10.5 上机实训 本章小结,2019/5/31,10.1 概述,触发器是一种特殊类型的存储过程，它是与特定的表相关联的。 当使用UPDATE、INSERT或DELETE中的一种或多种操作在指定表中对数据进行修改时，触发器会生效并自动执行。 一个表可以有多个触发器。,2019/5/31,触发器的主要功能：,使用触发器有助于强制保持数据库的数据完整性。例如，在触发器中可以完成如下功能： 不允许删除或更新特定的记录。 不允许插入不符合逻辑关系的记录。 在删除一条记录的同时删除其他表中与其相关的记录。 在修改一条记录的同时修改其他表中与其相关的记录。,2019/5/31,触发器的主要优点：,触发器是自动执行的，不需要管理员手动维护数据库的数据完整性。 触发器可以对数据库中的相关表进行级联更改。例如，可以在 “院系”表中定义触发器，当用户删除表“院系”中的记录时，触发器将删除 “学生”表和“教师”表中对应院系的记录。 触发器可以限制向表中插入无效的数据，与CHECK约束的功能相似。但在CHECK约束中不能使用到其他表中的字段，而在触发器中则没有此限制。,2019/5/31,思考？,触发器如何保证数据的完整性和一致性？ 触发器有哪些类型？ 如何创建触发器？ 触发器是怎么自动执行的？,2019/5/31,示例数据库Student的结构,学生表S(Snum,Sname,Ssex,Sage,Sphone,Dnum) 教师表T(Tnum,Tname,Tsex,Tbirth,Ttitle,Tsalary,Tphone,Dnum) 院系表D(Dnum,Dname,Ddirector) 课程表C(Cnum,Cname,Cfreq) 选课表SC(Snum,Cnum,Score) 授课表TC(Tnum,Cnum) 学生统计表N（Dnum,Num,Man,Woman） 课程平均成绩CPJ(Cnum,Cname,Pjcj),2019/5/31,项目：学籍管理系统,任务1：新生入学时，如何自动调整表n中相应系部的数据。 任务2：有学生退学时，如何自动调整表n中相应系部的数据。 任务3：学生转系时，如何自动调整表n中相应班级的数据。 任务4：学生选修某门课程取得成绩后，如何自动更新该课程的平均成绩； 任务5：发现某学生登记的成绩有误，进行更正后，如何自动调整该课程的平均成绩； 任务6：某个系不存在了，要删除D表中相应记录该如何进行？,2019/5/31,理解触发器的类型,（1）对应数据操作的三种类型：INSERT、DELETE和UPDATE，分别有三种不同类型的触发器，在执行相应的操作时自动触发。一个表可以同时有多个触发器，这些触发器可以是不同类型，也可以是同一类型的。,上述6个任务所需要的触发器分别属于哪种？,2019/5/31,（2）从执行时间来看，SQL SERVER有两类触发器： AFTER触发器： 触发器在触发它们的操作完成后执行。一张表中可以为每个触发操作(INSERT、DELETE和UPDATE)创建多个AFTER触发器。 INSTEAD OF触发器： 该触发器代替触发操作执行，原来的操作不再执行。一个表只能创建一个INSTEAD OF触发器。,上述6个任务所需要的触发器分别属于哪种？,2019/5/31,2019/5/31,10.2 创建触发器,创建触发器的方法： 1使用Transact-SQL语句创建触发器 2使用企业管理器创建触发器,2019/5/31,使用Transact-SQL语句创建触发器,CREATE TRIGGER ON FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS 注意：默认创建的触发器是AFTER类型的。,2019/5/31,任务1 创建insert触发器,例1 在表s上创建一个INSERT 触发器，实现当表s插入一个学生时，自动调整表n中相应系部的统计数据，即实现表s插入数据时与表n间数据的一致性。 方法一：用SQL语句实现 方法二：用企业管理器实现,2019/5/31,方法一：用SQL语句创建触发器,CREATE TRIGGER s_insert ON s FOR INSERT AS begin declare dnum char(20),ssex char(2) -检索插入的记录值 select dnum=dnum,ssex=ssex from inserted -如果在N表中不存在该学生对应的系，则在N表中添加该系的信息 if not exists(select dnum from n where dnum=dnum) insert into n(dnum,num) values (dnum,0) -根据该学生的性别更新相应的统计信息 if ssex=男 update n set num=num+1,man=isnull(man,0)+1 where dnum=dnum else update n set num=num+1,woman=isnull(woman,0)+1 where dnum=dnum end,2019/5/31,方法一：用企业管理器创建触发器,2019/5/31,如何验证触发器是否正常工作？,课堂练习： 1、利用SQL语句在S表上创建触发器（例1.txt）； 2、观察N表中的统计数据并进行记录； 3、利用企业管理器在S表中插入一条记录； 4、观察N表中的统计数据是否自动发生变化；,2019/5/31,任务2 创建Delete触发器,例2 在表s上创建一个DELETE 触发器，实现当表s删除一个学生时，自动调整表n中相应系部的数据，即实现表s删除数据时与表n间数据的一致性。 方法一：用SQL语句实现 方法二：用企业管理器实现,2019/5/31,提示:,-检索插入的记录值 select dnum=dnum,ssex=ssex from inserted -检索删除的记录值 select dnum=dnum,ssex=ssex from deleted,2019/5/31,触发器的执行原理（P214）,insert(delete)触发器：对表插入(删除)记录时，触发器被自动执行。首先将插入(删除)的记录放入inserted(deleted)表中，该表为一逻辑表(结构与原表相同)，保存插入(删除)的记录，然后执行触发器指定的操作。,update：相当于先delete，再insert。先将原记录保存在deleted表中，再将新记录保存在inserted表中。,2019/5/31,任务3 创建Update触发器,例3 在表s上创建一个UPDATE触发器，实现当表s修改一个学生信息时，自动调整表n中相应班级的数据，即实现表s修改数据时与表n间数据的一致性。 方法一：用SQL语句实现 方法二：用企业管理器实现,2019/5/31,分析,Update操作相当于先Delete，再Insert。 Update触发器中定义的SQL语句也应该由两部分所构成： （1）Delete触发器中定义的语句； （2）Insert触发器中定义的语句。,课堂练习：根据例1和例2，创建Update触发器,2019/5/31,课后练习：创建触发器实现以下功能,任务4：学生选修某门课程取得成绩后，如何自动更新该课程的平均成绩； 任务5：发现某学生登记的成绩有误，进行更正后，如何自动调整该课程的平均成绩； 任务6：某个系不存在了，要删除D表中相应记录该如何进行？（instead of触发器）,2019/5/31,10.3 查看、修改和删除触发器,触发器建立后，可查看、修改和删除触发器的定义。,2019/5/31,10.3.1 查看触发器,可以通过企业管理器查看触发器，也可通过系统存储过程查看触发器。 有4个系统存储过程，可以显示有关触发器的信息： sp_help 触发器名: 显示触发器的所有者和创建时间 sp_helptext 触发器名：显示触发器的源代码 sp_helptrigger 表名：显示某个表定义的触发器清单 sp_depends 触发器名：显示该触发器引用的对象清单,2019/5/31,10.3.2 修改触发器,触发器建立后可对其进行修改，既可修改其名称，也可以修改其定义、有效性等。 1修改触发器名称 语法格式： sp_rename 触发器原名, 触发器新名,2019/5/31,2修改触发器内容 （1）使用T-SQL修改触发器内容，语法格式如下： 语法格式： Alter TRIGGER ON FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS ,2019/5/31,（2）使用企业管理器修改触发器内容,2019/5/31,10.3.3 删除触发器,当不再需要某个触发器时，可将其删除。可以通过除去触发器或除去触发器表删除触发器。除去表时，也将除去所有与表关联的触发器。 （1）使用T-SQL删除触发器，语法格式如下： 语法格式： DROP TRIGGER 触发器名 ,.n ,2019/5/31,（2）使用企业管理器删除触发器 在图所示的“触发器属性”对话框的“名称”下拉菜单中选择要删除的触发器名称（默认是“触发器”），然后单击【删除】按钮并确认。,2019/5/31,10.4 综合实例,数据库管理信息系统中，常常需要监控敏感数据的更改（如用户的计费信息、员工的工资信息等），可使用触发器实现详细、细致的日志记录。,2019/5/31,【例10-9】用户信息表Users中包含敏感的用户积分和用户等级信息，现在需要对其进行监控，如果更改了用户积分和用户等级，则需要生成审计记录并插入到审核表，记录更新前后的数据，并记录修改者的登录名和客户机名，以及修改时间。 【分析】： （1）产生跟踪敏感信息的审核表 （2）用户信息表Users上创建UPDATE触发器 （3）判断是否修改用户积分和用户等级，是则执行触发器内容；否则不做任何处理。 （4）从deleted临时表中获取修改前的数据，插入审核表。 （5）从inserted临时表中获取修改后的数据，插入审核表。 （6）使用 SUSER_SNAME()返回登录名。 （7）使用 HOST_NAME（）返回工作站名称。 （8）使用 GETDATE() 返回修改时间。,2019/5/31,代码如下： （1）产生跟踪敏感信息的审核表 CREATE TABLE auditUsers( audit_Id int identity(1,1) primary key, -审计编号，标识列 -审计数据类型：OLD为修改前数据，NEW为修改后数据 Auit_Type char(3) not null, Uid int not null, -记录修改的用户号 Upoint int null, -记录用户积分 UState int null, -记录用户等级 audit_user varchar(40) default SUSER_SNAME(), -记录修改者的登录号 audit_pc varchar(40) default HOST_NAME(), -记录修改者的客户机名 audit_time datetime default GETDATE() -记录修改发生时间 ),2019/5/31,（2）在用户信息表Users上创建UPDATE触发器 CREATE TRIGGER users_Update ON USERS FOR UPDATE as if update(Upoint) or update(ustate) begin insert into auditUsers(Auit_Type, Uid, Upoint, UState) SELECT OLD, del. Uid, del.Upoint, del.Ustate FROM DELETED del insert into auditUsers(Auit_Type, Uid, Upoint, UState) SELECT NEW, ins. Uid, ins.Upoint, ins.Ustate FROM INSERTED ins End GO,2019/5/31,（3）验证此触发器，修改第2号用户的积分为20。 update Users set Upoint=20 where UID=2 （4）查看审计表