关系数据库与SQL Server 2005 教学课件 ppt 作者 龚小勇 第27讲 触发器

第9章 存储过程和触发器 触发器的使用,主讲人：段利文,第27讲,复习:,存储过程的含义、优点 类型：系统存储过程、用户存储过程 创建存储过程：需要确定存储过程的三个组成部分： 所有的输入参数、传给调用程序的输出参数。 被执行的针对数据库操作的语句，包括调用其它存储过程的语句( 嵌套)。 返回给调用程序的状态值，以指明调用是成功或失败(reture n),复习:,格式 Create procedure p_cj 学号 char(6), 课程名 output, 成绩 output as SQL语句 调用存储过程 Declare xh char(6) , kcm char(16), cj int Set xh =020102 Exec p_cj xh , kcm output , cj output Print .,输入实参将学号传递给输入形参,输出形参将课程名和成绩返回给输出实参,引:,实现数据完整性三个方面： 实现“实体完整性”的方法 primary key约束、unique约束、标识列、惟一索引 实现“域完整性”的方法 Check 约束、规则对象、 default 约束、默认值对象、not null 约束 实现“参照完整性”的方法 foreign key约束 请思考： 当用户对表进行了相应的操作后，我们希望系统能自动撤消该操作，并给出用户提示信息，请问能否用以上的方法来实现？,不能实现。因为约束只能进行简单条件的限制，对于复杂的条件，只能通过触发器来实现。,第9章 存储过程和触发器 触发器的使用 教学内容,基础知识： 触发器概述 创建触发器 触发(激活)触发器 触发器的更新 触发器的删除,第9章 存储过程和触发器 触发器的使用 学习目标,认知目标： 理解触发器的概念和作用 了解触发器的触发方式 了解inserted和deleted临时表 能力目标: 触发器的创建(重点) 创建不同类型的触发器(难点) 根据触发器的功能正确检验触发器的作用 (难点),一、触发器概述 1.触发器的概念,含义 是一种表或视图执行insert、 delete、update操作时，被系统自动执行的特殊的存储过程。 创建触发器的目的 对表实现复杂的数据完整性约束，以防止不正确的操作。,一、触发器概述 2.触发器的类型,按触发器激活的时机不同，分为： 后触发器(掌握) 只有执行了insert、 delete、update操作后,才激发的触发器。 替代触发器(了解) 不执行命令所定义的insert、 delete、update操作,而仅执行触发器中定义的代码。 按引起触发的事件不同，分为： insert触发器 delete触发器 update触发器,一、触发器概述 3.临时表,每个触发器被激活时，系统都为它自动创建两个临时表： inserted表 deleted表 临时表的特点： 是逻辑表，由系统来维护，用户不能对它们进行修改。 其结构与源表 (激活触发器的表)结构相同 触发器执行完成后，该临时表会自动被删除。,一、触发器概述 3.临时表,deleted 临时表的作用： 存放执行delete或update语句后，要从源表中删除的行 特点：源表和delete表不会有相同的行。 inserted临时表的作用： 存放执行insert或update语句后，要在源表中插入的新行(新行同时插入了源表和inserted表中) 特点：源表和inserted表有相同的行。 演示图,二、创建和激活触发器,创建格式： create trigger 触发器名 on 表名| 视图名 for | after | instead of insert , update, delete as SQL语句 说明： 1个表上可有多个触发器。 每个触发器只能作用在一个表上。,指定了触发器激活的时机 for、after创建后触发器 instead of创建替代触发器,激活触发器的事件类型,二、创建和激活触发器 1.insert触发器,实例1：在xscj库的xs_kc表上创建1个名为tr_insert_cj的触发器，当向xs_kc表进行插入操作时激发该触发器，并给出提示信息“有新成绩插入到xs_kc表中!” create trigger tr_insert_cj on xs_kc after insert as print 有新成绩信息插入到xs_kc表! go insert into xs_kc values( '020105', '101', 87, null ),说明：该语句执行后，会激活触发器，显示提示信息。当再执行一次该插入语句，请思考：该触发器能不能被激活？为什么？,原因：当插入的数据违反了先前定义的约束规则时，则约束优先于触发器起作用，该插入触发器不会被激发。,二、创建和激活触发器 1.insert触发器,实例2：阅读教材P183例7-10 。 【题意分析】：要在xscj库的xsqk表中插入下面所示的2条信息，如果在该表上未创建触发器，则可成功插入到xsqk表中。但我们希望插入的信息必须是“计算机” 系，否则就撤消刚才的插入，并返回错误信息。用触发器实现该约束。 use xscj insert into xsqk(学号,姓名,性别,出生日期,专业名,所在系) values(020401, 陈希, 1, 1980-1-1, 中国近代史, 物理 ) insert into xsqk(学号,姓名,性别,出生日期,专业名,所在系) values(020402, 黄梅, 0, 1980-1-1, 信息管理, 计算机 ),二、创建和激活触发器 1.insert触发器,请分析：该题中Select语句的作用? where子句可否不要? create trigger tr_insert_xsqk on xsqk after insert as declare 所在系 char(10) select 所在系= xsqk.所在系 from xsqk, inserted where xsqk.学号 = inserted.学号 IF 所在系 '计算机 ' begin rollback transaction - 撤消插入操作 raiserror(不能插入非计算机系的学生信息！,16 ,10) end else print 有新信息插入到xsqk表中! ,select 所在系= inserted.所在系 from inserted,提取插入信息中的所在系所对应的值,二、创建和激活触发器 1.insert触发器,补充说明： raiserror语句的作用 将错误信息显示在屏幕上，并可记录在日志中 例： raiserror(不能插入非计算机系的学生信息！, 16 , 1),错误的严重等级。018可被任何用户引发，1925只能由系统管理员引发。,发生错误时的状态信息(0127)。,用户以字符串形式给出的错误信息。,二、创建和激活触发器 2.delete触发器,课堂练习1：在xscj库的xsqk表上创建1个名为tr_delete_xsqk的触发器，当要删除指定学号的行时，激发该触发器，撤消删除操作，并给出提示信息“不能删除xsqk表中的信息!”。 create trigger tr_delete_xsqk on xsqk after delete as rollback transaction print 不能删除xsqk表中的信息! go delete xsqk where 学号= '020101',二、创建和激活触发器 3.update触发器,实例3：阅读教材P184例7-11 。在xsqk表上创建一个触发器。当更新了某位学生的学号信息时，就激活触发器级联更新xs_kc表中相关成绩记录中的学号信息，并使用print语句返回一个提示信息。 【题意分析】：用下列语句修改了xsqk表中的学号020101 ，那么xs_kc表中的该学号也应同时修改，否则，将引起数据不一致。解决的方法有： 创建外键约束(不允许修改，或者允许级联更新) 用触发器实现自动级联修改 update xsqk set 学号=020111 where 学号=020101,二、创建和激活触发器 3.update触发器,请思考：该题中参考了哪些表？为什么能从临时表中提取更新前后的学号值？ create trigger tr_update_xsqk1 on xsqk after update as declare 原学号 char(6), 新学号 char(6) select 原学号=deleted.学号, 新学号= inserted.学号 from deleted, inserted where deleted.姓名 = inserted.姓名 print 准备级联更新xs_kc表中的学号信息. update xs_kc set 学号=新学号 where 学号=原学号 print 已经级联更新了xs_kc表中原学号为+ 原学号 +的成绩信息。,可 省 去,二、创建和激活触发器 3.update触发器,课堂练习2：在xsqk表上创建名为tr_update_xsqk2的触发器，当对该表的“姓名”列修改时激发该触发器，使用户不能修改“姓名”列。 create trigger tr_update_xsqk2 on xsqk after update as if update(姓名) begin rollback transaction - 撤消修改操作 raiserror(不能修改学生姓名！ , 16 ,1) end go update xsqk set 姓名=小花 where 姓名=杨颖,三、更新、删除触发器,更新触发器的语法格式： alter trigger 触发器名 删除触发器的语法格式： drop trigger 触发器名,n,本课小结,触发器的含义 触发器的类型按触发的时机不同： 后触发器、替代触发器 触发器执行期间的临时表 inserted 表 、deleted表 创建触发器 insert触发器、delete触发器、update触发器 触发(激活)触发器(三种触发事件) 触发器的更新、删除,课余作业布置,课外练习： 教材P197： 6、7、8、9。 上机准备：调试作业。,