资源预览内容
第1页 / 共27页
第2页 / 共27页
第3页 / 共27页
第4页 / 共27页
第5页 / 共27页
第6页 / 共27页
第7页 / 共27页
第8页 / 共27页
第9页 / 共27页
第10页 / 共27页
亲,该文档总共27页,到这儿已超出免费预览范围,如果喜欢就下载吧!
资源描述
1,第十章 存储过程与触发器,6-1 存储过程 6-2 触发器 6-3 存储过程与触发应用 6-4 本章小结在大型数据库系统中,存储过程和触发器具有很重要的作用。存储过程是SQL语句和控制流语句组成的集合,触发器是一种特殊的存储过程。本章主要介绍存储过程和触发器的概念,优点和基本操作。,2,思考:当学生报名选修SQL Server实用技术课程时,人数应自动加1,如何处理?,3,6.2.1 触发器基础,一、概念1.触发器(trigger)是一种特殊的存储过程,2.它与表紧密相连,基于表而建立,可视作表的一部分,用户创建触发器后,就能控制与触发器关联的表.3.当表中的数据发生插入、删除或修改时,触发器自动运行。触发器是一种维持数据引用完整性的极好方法。4.当触发器所保护的数据发生改变时,触发器会自动激活,从而防止数据的不正确修改.5.同一个表可以使用多个触发器,即使同一类型的触发器,也可以使用多个.,4,6.2.1 触发器基础,二、触发器与存储过程的区别: 1、 触发器主要是通过事件进行触发而被执行的,而存储过程可以通过存储过程名而被直接调用。 2、触发器是一个功能强大的工具,它使每个站点可以在有数据修改时自动强制执行其业务规则。触发器可以用于 SQL Server 约束、默认值和规则的完整性检查。 3、而存储过程是SQL语句和可选控制流语句的预编译集合,SQL Server 会将该集合中的语句编译成一个执行单位。,5,6.2.1 触发器基础,三、触发器分类,INSTEAD OF : 替代触发,执行一个触发器,而不执行引发触发器的SQL语句AFTER : 后触发,引发触发器的修改操作,操作 (FOR)完成后才执行触发器,1.触发器分三个类别:,2.触发方式,6,四、触发器中特殊的两个表,6.2.1 触发器基础,Inserted表:,Deleted表:,提问:如果触发了一个删除触发器,那么被删除的记录在哪里?如果触发了一个修改触发器,修改前的记录在哪里?修改后的记录在哪里?,7,触发器中特殊的两个表,1、这两个表是逻辑表,且由SQL Server管理。2、不像其他的表是存储在数据库中的,这两个表是存储在内存中的,因而用户是不能对其直接进行修改。3、两个表的结构总是与被该触发器作用的表有相同的表结构4、这两个表是动态驻留在内存中,当触发器完成时,这两个表也被自动删除,inserted和deleted表中的值只限于在触发器中使用,一旦触发器完成就无法再使用。,Inserted表:,Deleted表:,8, 5、从表中的内容上看,这两个表主要保存了因用户操作而被影响到的原数据值或新数据值。 deleted表会储存因 DELETE 及 UPDATE 语句而受影响的行副本, inserted表会储存因INSERT 及 UPDATE 语句影响的行副本。 6、 另外这两个表是只读的,即用户不能向这两个表写入内容,但可以引用表中的数据。例如可用如下语句查看DELETED 表中的信息: select * from deleted,触发器中特殊的两个表,Inserted表:,Deleted表:,9,6.2.1 触发器基础,五、关于触发器的一些其它规定(1)触发器只在触发它的语句完成后执行。 (2)如果语句在表中执行违反条件约束或引起错误,触发器不会触动。(3)触发器视为单一事务中的一部份,因此可以由原触发器复原事务,如果在事务过程中侦测到严重的错误(如用户中断联机),则会自动复原整个事务。(4)一个语句只能触动一次触发器。,10,6.2.2 创建触发器,一、在创建触发器之前,用户必须考虑到以下几方面的因素:(1)【CREATE TRIGGER】语句必须是批处理的第一个语句。(2)表的所有者(DBO)具有创建触发器的缺省权限,表的所有者不能把该权限传给其它用户。(3)触发器是数据库对象,所以其命名必须符合命名规则。(4)触发器只能创建在当前数据库中,但是触发器的Transact-SQL语句可以参照其它数据库中的对象。(5)不能在视图上定义 AFTER 触发器。,11,(6)一个触发器只能对应一个表,这是由触发器的机制决定的。当创建一个触发器时,必须指定触发器的名字在哪一个表上定义触发器。(7)两个或三个不同的修改语句,也可以都触发同一个触发器,如【INSERT】和【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 ,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 系部表 values(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 创建触发器,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】建立一个触发器删除学生,当从学生表中删除一个学生信息后,成绩表中该学生信息会级联删除。(考虑存在性)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 触发器的创建,结论?,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】建立一个基于“学生表”,“课程信息表”,”成绩表“的一个嵌套触发器。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 删除课程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 insertedprint更改后的成绩:+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 成绩表,
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号