《第六章 触发器》由会员分享，可在线阅读，更多相关《第六章 触发器（27页珍藏版）》请在金锄头文库上搜索。
1、1,第十章 存储过程与触发器,6-1 存储过程 6-2 触发器 6-3 存储过程与触发应用 6-4 本章小结在大型数据库系统中，存储过程和触发器具有很重要的作用。存储过程是SQL语句和控制流语句组成的集合，触发器是一种特殊的存储过程。本章主要介绍存储过程和触发器的概念，优点和基本操作。,2,思考：当学生报名选修SQL Server实用技术课程时，人数应自动加1，如何处理？,3,6.2.1 触发器基础,一、概念1.触发器(trigger)是一种特殊的存储过程,2.它与表紧密相连,基于表而建立,可视作表的一部分,用户创建触发器后,就能控制与触发器关联的表.3.当表中的数据发生插入、删除或修改时,触
2、发器自动运行。触发器是一种维持数据引用完整性的极好方法。4.当触发器所保护的数据发生改变时,触发器会自动激活,从而防止数据的不正确修改.5.同一个表可以使用多个触发器,即使同一类型的触发器,也可以使用多个.,4,6.2.1 触发器基础,二、触发器与存储过程的区别： 1、 触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名而被直接调用。 2、触发器是一个功能强大的工具，它使每个站点可以在有数据修改时自动强制执行其业务规则。触发器可以用于 SQL Server 约束、默认值和规则的完整性检查。 3、而存储过程是SQL语句和可选控制流语句的预编译集合，SQL Server 会将该集
3、合中的语句编译成一个执行单位。,5,6.2.1 触发器基础,三、触发器分类,INSTEAD OF : 替代触发,执行一个触发器,而不执行引发触发器的SQL语句AFTER : 后触发,引发触发器的修改操作,操作 （FOR）完成后才执行触发器,1.触发器分三个类别:,2.触发方式,6,四、触发器中特殊的两个表,6.2.1 触发器基础,Inserted表：,Deleted表：,提问：如果触发了一个删除触发器，那么被删除的记录在哪里？如果触发了一个修改触发器，修改前的记录在哪里？修改后的记录在哪里？,7,触发器中特殊的两个表,1、这两个表是逻辑表，且由SQL Server管理。2、不像其他的表是存储在
4、数据库中的，这两个表是存储在内存中的，因而用户是不能对其直接进行修改。3、两个表的结构总是与被该触发器作用的表有相同的表结构4、这两个表是动态驻留在内存中，当触发器完成时，这两个表也被自动删除,inserted和deleted表中的值只限于在触发器中使用，一旦触发器完成就无法再使用。,Inserted表：,Deleted表：,8, 5、从表中的内容上看，这两个表主要保存了因用户操作而被影响到的原数据值或新数据值。 deleted表会储存因 DELETE 及 UPDATE 语句而受影响的行副本， inserted表会储存因INSERT 及 UPDATE 语句影响的行副本。 6、 另外这两个表是只
5、读的，即用户不能向这两个表写入内容，但可以引用表中的数据。例如可用如下语句查看DELETED 表中的信息： select * from deleted,触发器中特殊的两个表,Inserted表：,Deleted表：,9,6.2.1 触发器基础,五、关于触发器的一些其它规定(1)触发器只在触发它的语句完成后执行。 (2)如果语句在表中执行违反条件约束或引起错误,触发器不会触动。(3)触发器视为单一事务中的一部份,因此可以由原触发器复原事务,如果在事务过程中侦测到严重的错误(如用户中断联机),则会自动复原整个事务。(4)一个语句只能触动一次触发器。,10,6.2.2 创建触发器,一、在创建触发器之
6、前，用户必须考虑到以下几方面的因素：（1）【CREATE TRIGGER】语句必须是批处理的第一个语句。（2）表的所有者（DBO）具有创建触发器的缺省权限，表的所有者不能把该权限传给其它用户。（3）触发器是数据库对象，所以其命名必须符合命名规则。（4）触发器只能创建在当前数据库中，但是触发器的Transact-SQL语句可以参照其它数据库中的对象。（5）不能在视图上定义 AFTER 触发器。,11,（6）一个触发器只能对应一个表，这是由触发器的机制决定的。当创建一个触发器时，必须指定触发器的名字在哪一个表上定义触发器。（7）两个或三个不同的修改语句，也可以都触发同一个触发器，如【INSERT】
7、和【UPDATE】语句都能激活同一个触发器 二、触发器的主要内容包含： 触发器名称，与触发器关联的表，激活触发器的语句和条件，触发器应完成的操作等。 1、在资源管理器中创建触发器 单击将在其上创建触发器的表 右击触发器 新建触发器输入创建触发器的语句关闭并保存。,6.2.2 创建触发器,12,6.2.2 创建触发器,2、创建触发器可以使用CREATE TRIGGER语句,其语法格式如下：CREATE TRIGGER 触发器名ON表名|视图名 WITH ENCRYPTION FOR|AFTER|INSTEAD OF DELETE,INSERT,UPDATEAS sql_statement .n
8、,13,6.2.2 创建触发器(补充),数据库的系统文件sysobjects文件中存放的是所有数据对象的相关信息,其中xtype字段代表的是对象的类型：,触发器存储过程表文件视图主键约束外键约束检查约束默认值,TRPUVPKFCKD,14,INSERT 触发器: 利用insert 语句可激活表的数据插入。 【例1】 :建立一个触发器插入系部，在系部表中插入一个新系的系部信息，显示插入行的记录内容。create trigger 插入系部 on 系部表 for insert as select * from inserted,6.3.2 触发器的应用,应用触发器：insert into 系部表 v
9、alues(15,我们的系),15,6.2.1 触发器基础,【例2】：用T-SQL语言创建一个触发器，向系部表修改或添加数据时 ，该触发器向客户端显示一条信息“本操作已成功完成插入或更新表数据” Create trigger 更新数据 on 系部表 for insert,update as print 本操作已成功完成插入或更新表数据 执行命令： update 系部表 set 系部编号=08 where 系部名称=信息与计算机科学或者 insert into 系部表 values (11,体育系) 时，触发器在结果窗口中显示了信息内容。,Set nocount on,16,6.2.2 创建触发
10、器,UPDATE:通过更新表中数据来实现触动触发器。【例3】设计一个触发器：当系部表更新系部编号时，超过20时，就提示“系部编号”超出范围了，并回滚修改语句。Create trigger up_c on 系部表 after updateas if (select 系部编号 from inserted )20begin print 已超过系部编号的要求了！ rollback end,update 系部表 set 系部编号=96 where 系部名称=信息与计算机科学,17,6.3.2 触发器的创建,DELETE 触发器: 利用DELETE语句可激活表的串联删除。【例4】建立一个触发器删除学生，当
11、从学生表中删除一个学生信息后，成绩表中该学生信息会级联删除。(考虑存在性）if exists(select name from sysobjects where name=删除学生 and type=tr)drop trigger 删除学生gocreate trigger 删除学生on 学生表for deleteasDelete from 成绩表 where 学号=(select 学号 from deleted ),执行触发器：delete 学生表 where 学号=000045,instead of delete,18,课程作业：删除一个系部记录，触发删除班级表中相关的记录,6.3.2 触发
12、器的创建,结论？,19,触发器可以调用存储过程,(1)先建立一个查询系部表所有信息的存储过程create proc cxbasselect * from 系部表(2)创建一个触发器，当修改或插入数据到系部表，触发执行存储过程,显示系部表修改后的所有信息。Create trigger up_a on 系部表 for insert,update as exec cxb,执行：update 系部表 set 系部编号=16 where 系部名称=信息与计算机科学实现了对系部表修改之后，立即显示系部表所有信息，同时也看到修改后的记录信息。,20,6.3.2 触发器的嵌套,【例5】建立一个基于“学生表”，
13、“课程信息表”，”成绩表“的一个嵌套触发器。if exists(select name from sysobjects where name=删除学生 and type=tr)drop trigger 删除学生gocreate trigger 删除学生on 学生表for deleteasdelete 成绩表 from 成绩表,deleted where 成绩表.学号=deleted.学号go,21,if exists(select name from sysobjects where name=删除课程 and type=tr)drop trigger 删除课程gocreate trigger
14、 删除课程on 课程信息表for deleteasdelete 成绩表 from 成绩表,deleted where 成绩表.课程编号=deleted.课程编号,6.3.2 触发器的嵌套,22,6.3.2 UPDATE触发器的应用,【例6】建立一个基本于更新触发器嵌套触发器。ALTER trigger 成绩表触发器 on 成绩表 for update asdeclare oldcj numeric,newcj numericselect oldcj=成绩 from deletedprint 更新前成绩:+cast(oldcj as char(5)select newcj=成绩 from ins
15、ertedprint更改后的成绩:+cast(newcj as char(5)if newcj(oldcj*1.1) begin print 成绩涨幅太大,更改失败 rollback endelse print 成绩更改成功,update 成绩表 set 成绩=成绩*1.2 where 学号=000001 and 课程编号=001,23,6.2.3 管理触发器,三、管理触发器1、资源管理器中查看触发器信息（1）右击触发器所在的表/【所有任务】【管理触发器】 (2) 右击触发器所属的表/【所有任务】【显示相关性】2使用系统存储过程查看触发器(1)sp_helptrigger表名:返回指定表中定义的当前数据库的触发器类型。例：查看成绩表所建立的所有触发器Sp_helptrigger 成绩表,
