触发器的创建与使用.ppt

《触发器的创建与使用.ppt》由会员分享，可在线阅读，更多相关《触发器的创建与使用.ppt（42页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库基础与应用第10章 触发器的创建和使用 相关知识回顾相关知识回顾1、什么是存储过程？、什么是存储过程？2、创建存储过程的基本语法如何？、创建存储过程的基本语法如何？3、如何调用存储过程？、如何调用存储过程？4、数据库中有哪、数据库中有哪6种约束？种约束？5/26/2023触发器与存储过程、约束的关系触发器与存储过程、约束的关系触发器是建立在表上的特殊的触发器是建立在表上的特殊的存储过程存储过程。当对表进行插入、删除、更新操作时，触发器将会当对表进行插入、删除、更新操作时，触发器将会自自动执行动执行。利用触发器可以保证表中数据的利用触发器可以保证表中数据的一致性一致性和和完整性完整性，功，

2、功能比约束更强。能比约束更强。5/26/2023本章学习目标本章学习目标了解触发器的概念和优点理解触发器的工作原理熟练掌握如何创建熟练掌握如何创建INSERT触发器、触发器、UPDATE触发器、触发器、DELETE触发器触发器掌握如何查看、修改和删除触发器5/26/2023第第10章章 触发器的创建和使用触发器的创建和使用 10.1 概述概述10.2 创建触发器创建触发器10.3 查看、修改和删除触发器查看、修改和删除触发器10.4 综合实例综合实例10.5 上机实训上机实训本章小结本章小结 5/26/202310.1 概述概述 触发器是一种特殊类型的存储过程，它是与特定的表相关联的。当使用U

3、PDATE、INSERT或DELETE中的一种或多种操作在指定表中对数据进行修改时，触发器会生效并自动执行。一个表可以有多个触发器。5/26/2023触发器的主要功能：触发器的主要功能：n使用触发器有助于强制保持数据库的数据完整性。例如，在触发器中可以完成如下功能：不允许删除或更新特定的记录。不允许插入不符合逻辑关系的记录。在删除一条记录的同时删除其他表中与其相关的记录。在修改一条记录的同时修改其他表中与其相关的记录。5/26/2023触发器的主要优点：触发器的主要优点：触发器是自动执行自动执行的，不需要管理员手动维护数据库的数据完整性。触发器可以对数据库中的相关表进行级联更改级联更改。例如，

4、可以在“院系”表中定义触发器，当用户删除表“院系”中的记录时，触发器将删除“学生”表和“教师”表中对应院系的记录。触发器可以限制限制向表中插入无效的数据无效的数据，与CHECK约束的功能相似。但在CHECK约束中不能使用到其他表中的字段，而在触发器中则没有此限制。5/26/2023思考？思考？触发器如何保证数据的完整性和一致性？触发器有哪些类型？如何创建触发器？触发器是怎么自动执行的？5/26/2023示例数据库示例数据库Student的结构的结构学生表S(Snum,Sname,Ssex,Sage,Sphone,Dnum)教师表T(Tnum,Tname,Tsex,Tbirth,Ttitle,T

5、salary,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)5/26/2023项目：学籍管理系统项目：学籍管理系统任务1：新生入学时，如何自动调整表n中相应系部的数据。任务2：有学生退学时，如何自动调整表n中相应系部的数据。任务3：学生转系时，如何自动调整表n中相应班级的数据。任务4：学生选修某门课程取得成绩后，如何自动更

6、新该课程的平均成绩；任务5：发现某学生登记的成绩有误，进行更正后，如何自动调整该课程的平均成绩；任务6：某个系不存在了，要删除D表中相应记录该如何进行？5/26/2023理解触发器的类型理解触发器的类型（1）对应数数据据操操作作的三种类型：INSERT、DELETE和UPDATE，分别有三种不同类型的触发器，在执行相应的操作时自动触发。一个表可以同时有多个触发器，这些触发器可以是不同类型，也可以是同一类型的。5/26/2023（2）从执行时间执行时间来看，SQL SERVER有两类触发器：AFTER触发器：触发器：触发器在触发它们的操作完成后执行。一张表中可以为每个触发操作(INSERT、DE

7、LETE和UPDATE)创建多个AFTER触发器。INSTEAD OF触发器：触发器：该触发器代替触发操作执行，原来的操作不再执行。一个表只能创建一个INSTEAD OF触发器。5/26/20235/26/202310.2 创建触发器创建触发器 创建触发器的方法：1使用使用Transact-SQL语句创建触发器语句创建触发器2使用企业管理器创建触发器 5/26/2023使用使用Transact-SQL语句创建触发器语句创建触发器CREATE TRIGGER ON FOR|AFTER|INSTEAD OF DELETE,INSERT ,UPDATE AS 注意：默认创建的触发器是注意：默认创建的

8、触发器是AFTER类型的。类型的。5/26/2023任务任务1 创建创建insert触发器触发器 例例例例1 1 1 1 在表在表在表在表s s上创建一个上创建一个上创建一个上创建一个INSERT INSERT 触发器，实现当表触发器，实现当表触发器，实现当表触发器，实现当表s s插入一个插入一个插入一个插入一个学生时，自动调整表学生时，自动调整表学生时，自动调整表学生时，自动调整表n n中相应系部的统计数据，即实现表中相应系部的统计数据，即实现表中相应系部的统计数据，即实现表中相应系部的统计数据，即实现表s s插入插入插入插入数据时与表数据时与表数据时与表数据时与表n n间数据的一致性。间数

9、据的一致性。间数据的一致性。间数据的一致性。方法一：用SQL语句实现方法二：用企业管理器实现5/26/2023方法一：用方法一：用SQLSQL语句创建触发器语句创建触发器CREATE TRIGGER s_insert ON s FOR INSERTAS 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)in

10、sert 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 end5/26/2023方法一：用企业管理器创建触发器方法一：用企业管理器创建触发器5/26/2023如何验证触发器是否正常工作？如何验证触发器是否正常工作？课堂练习：1、利用SQL语句在S表上创建触发器（例1.txt

11、）；2、观察N表中的统计数据并进行记录；3、利用企业管理器在S表中插入一条记录；4、观察N表中的统计数据是否自动发生变化；5/26/2023任务任务2 创建创建Delete触发器触发器 例例例例2 2 2 2 在表在表在表在表s s s s上创建一个上创建一个上创建一个上创建一个DELETE DELETE DELETE DELETE 触发器，实现当表触发器，实现当表触发器，实现当表触发器，实现当表s s s s删除一个删除一个删除一个删除一个学生时，自动调整表学生时，自动调整表学生时，自动调整表学生时，自动调整表n n n n中相应系部的数据，即实现表中相应系部的数据，即实现表中相应系部的数据

12、，即实现表中相应系部的数据，即实现表s s s s删除数据时删除数据时删除数据时删除数据时与表与表与表与表n n n n间数据的一致性。间数据的一致性。间数据的一致性。间数据的一致性。方法一：用SQL语句实现方法二：用企业管理器实现5/26/2023提示提示:-检索插入的记录值 select dnum=dnum,ssex=ssex from inserted-检索删除的记录值检索删除的记录值select dnum=dnum,ssex=ssex from deleted5/26/2023触发器的执行原理（触发器的执行原理（P214）insert(delete)触触发发器器：对对表表插插入入(删删

13、除除)记记录录时时，触触发发器器被被自自动动执执行行。首首先先将将插插入入(删删除除)的的记记录录放放入入inserted(deleted)表表中中，该该表表为为一一逻逻辑辑表表(结结构构与与原原表表相相同同)，保保存存插插入入(删删除除)的的记记录录，然然后后执执行行触触发发器器指指定定的的操作。操作。update：相当于先：相当于先delete，再，再insert。先将原记录保存在。先将原记录保存在deleted表中，再将新记录保存在表中，再将新记录保存在inserted表中。表中。5/26/2023任务任务3 创建创建Update触发器触发器 例例例例3 3 3 3 在表在表在表在表s

14、s s s上创建一个上创建一个上创建一个上创建一个UPDATEUPDATEUPDATEUPDATE触发器，实现当表触发器，实现当表触发器，实现当表触发器，实现当表s s s s修改一个修改一个修改一个修改一个学生信息时，自动调整表学生信息时，自动调整表学生信息时，自动调整表学生信息时，自动调整表n n n n中相应班级的数据，即实现表中相应班级的数据，即实现表中相应班级的数据，即实现表中相应班级的数据，即实现表s s s s修改数修改数修改数修改数据时与表据时与表据时与表据时与表n n n n间数据的一致性。间数据的一致性。间数据的一致性。间数据的一致性。方法一：用SQL语句实现方法二：用企业

15、管理器实现5/26/2023分析分析Update操作相当于先Delete，再Insert。Update触发器中定义的SQL语句也应该由两部分所构成：（1）Delete触发器中定义的语句；（2）Insert触发器中定义的语句。5/26/2023课后练习：创建触发器实现以下功能课后练习：创建触发器实现以下功能任务4：学生选修某门课程取得成绩后，如何自动更新该课程的平均成绩；任务5：发现某学生登记的成绩有误，进行更正后，如何自动调整该课程的平均成绩；任务6：某个系不存在了，要删除D表中相应记录该如何进行？（instead of触发器）5/26/202310.3 查看、修改和删除触发器查看、修改和删除

16、触发器 触发器建立后，可查看、修改和删除触发器的定义。5/26/202310.3.1 查看触发器查看触发器 可以通过企业管理器查看触发器，也可通过系统存储过程查看触发器。有4个系统存储过程，可以显示有关触发器的信息：sp_help 触发器名:显示触发器的所有者和创建时间sp_helptext 触发器名：显示触发器的源代码sp_helptrigger 表名：显示某个表定义的触发器清单sp_depends 触发器名：显示该触发器引用的对象清单5/26/202310.3.2 修改触发器修改触发器 触发器建立后可对其进行修改，既可修改其名称，也可以修改其定义、有效性等。1修改触发器名称语法格式：sp_

17、rename 触发器原名,触发器新名5/26/20232修改触发器内容（1）使用T-SQL修改触发器内容，语法格式如下：语法格式：Alter TRIGGER ON FOR|AFTER|INSTEAD OF DELETE,INSERT ,UPDATE AS 5/26/2023（2）使用企业管理器修改触发器内容5/26/202310.3.3 删除触发器删除触发器 当不再需要某个触发器时，可将其删除。可以通过除去触发器或除去触发器表删除触发器。除去表时，也将除去所有与表关联的触发器。（1）使用T-SQL删除触发器，语法格式如下：语法格式：DROP TRIGGER 触发器名 ,.n 5/26/2023

18、（2）使用企业管理器删除触发器在图所示的“触发器属性”对话框的“名称”下拉菜单中选择要删除的触发器名称（默认是“触发器”），然后单击【删除】按钮并确认。5/26/202310.4 综合实例综合实例 数据库管理信息系统中，常常需要监控敏感数据的更改（如用户的计费信息、员工的工资信息等），可使用触发器实现详细、细致的日志记录。5/26/2023【例10-9】用户信息表Users中包含敏感的用户积分和用户等级信息，现在需要对其进行监控，如果更改了用户积分和用户等级，则需要生成审计记录并插入到审核表，记录更新前后的数据，并记录修改者的登录名和客户机名，以及修改时间。【分析】：（1）产生跟踪敏感信息的审

19、核表（2）用户信息表Users上创建UPDATE触发器（3）判断是否修改用户积分和用户等级，是则执行触发器内容；否则不做任何处理。（4）从deleted临时表中获取修改前的数据，插入审核表。（5）从inserted临时表中获取修改后的数据，插入审核表。（6）使用 SUSER_SNAME()返回登录名。（7）使用 HOST_NAME（）返回工作站名称。（8）使用 GETDATE()返回修改时间。5/26/2023代码如下：（1）产生跟踪敏感信息的审核表CREATE TABLE auditUsers(audit_Id int identity(1,1)primary key,-审计编号，标识列 -

20、审计数据类型：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()-记录修改发生时间)5/26/2023（2）在用户信息表Users上

21、创建UPDATE触发器CREATE TRIGGER users_UpdateON USERSFOR UPDATE asif update(Upoint)or update(ustate)begininsert into auditUsers(Auit_Type,Uid,Upoint,UState)SELECT OLD,del.Uid,del.Upoint,del.UstateFROM DELETED delinsert into auditUsers(Auit_Type,Uid,Upoint,UState)SELECT NEW,ins.Uid,ins.Upoint,ins.UstateFROM

22、 INSERTED insEndGO5/26/2023（3）验证此触发器，修改第2号用户的积分为20。update Users set Upoint=20 where UID=2（4）查看审计表auditUsers，发现新增加了两行数据，如图所示。5/26/202310.5 上机实训上机实训实训目的实训目的1创建INSERT、DELETE、UPDATE和INSTEAD OF触发器2列出与某张表有关的触发器3查看某触发器的信息4让触发器失效/生效5/26/2023实训要求实训要求1复习T-SQL程序设计有关知识。2复习DML语句。3复习视图有关知识。4复习存贮过程有关知识。5/26/2023实训

23、内容实训内容1创建INSERT触发器2创建DELETE触发器3创建UPDATD触发器4创建INSTEAD OF触发器5显示触发器及触发器的详细信息6使触发器失效/生效7综合实训练习5/26/2023本章小结本章小结触发器是对表和视图中的数据进行增删改（Insert,delete,update）时，自动执行的存储过程。触发器是通常用于实现和强制业务规则和数据完整性。创建触发器使用CREATE TRIGGER语句。从执行时间来区分，SQL SERVER触发器包括：AFTER触发器和INSTEAD OF触发器。从触发条件来区分，SQL SERVER触发器包括：INSERT触发器，DELETE触发器和UPDATE触发器。deleted 和 inserted 是逻辑（概念）表。这两个表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；它们用于保存用户操作更改的旧行数据和新行数据。5/26/2023