《触发器原理及使用.ppt》由会员分享，可在线阅读，更多相关《触发器原理及使用.ppt（46页珍藏版）》请在三一办公上搜索。
1、1,浙江财经学院,第13章 触发器原理及使用,13.1 触发器基本概念13.2 触发器原理13.3 触发器的创建和管理13.4 使用触发器实现强制业务规则13.5 使用触发器的T-SQL限制13.6 触发器应用实例分析,2,浙江财经学院,13.1 触发器基本概念,13.1.1 触发器的概念及作用 13.1.2 触发器的种类,3,浙江财经学院,13.1.1 触发器的概念及作用,触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。存储过程和触发器同是提高数据库服务器性能的有力工具。
2、触发器不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。,4,浙江财经学院,13.1.1 触发器的概念及作用,触发器的主要作用:实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。（1）强化约束（EnFORce restriction）触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的更新和变化。（2）级联运行（Cascaded
3、Operation）触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。（3）存储过程的调用（Stored Procedure Invocation）为了响应数据库更新，触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS之外进行操作。,5,浙江财经学院,13.1.2 触发器的种类,SQL Server 支持两种类型的触发器：（1）AFTER触发器 即为SQL Server 2000版本以前所介绍的触发器。该类型触发器要求只有执行完某一操作（INSERT、UPDATE、DELETE），并处理过所有约束后，触发器才被触发，且只能在表上定义。如果操作违反约束条件
4、，将导致事务回滚，这时就不会执行后触发器。（2）INSTEAD OF触发器 该类触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。可在表上定义INSTEAD OF触发器，也可以在视图上定义INSTEAD OF触发器，但对同一操作只能定义一个INSTEAD OF触发器。,6,浙江财经学院,13.2 触发器原理,触发器具有强大的功能，那么MS SQL Server 是如何使得触发器能够感知数据库数据的变化、维护数据库参照完整性及比CHECK约束更复杂的约束呢？下面我们将对其工作原理及实现做较为详细的介绍，以便大家学习创建、理解和使用各种类型的触发器
5、，完成各种任务。,7,浙江财经学院,13.2.1 插入表的功能,对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入（INSERT）操作，系统就会生成一个特殊表-插入表（inserted），这个表驻留在内存中，不是存储在数据库中，因此不允许用户直接对其修改。对于用户插入的所有行来说，都有一个相应的副本拷贝存放到插入表（inserted）中，即插入表就是用来存储原表插入的新数据行。,8,浙江财经学院,13.2.2 删除表的功能,对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除（DELETE）操作，则将所有的被删除的行存放至删除表（deleted表）中。这样做的目的是，一旦触发器遇到了
6、强迫它中止的语句被执行时，删除的那些行可以从删除表（deleted表）中得以还原。需要强调的是，更新（UPDATE）操作包括两个部分，即先将旧的内容删除，然后将新值插入。因此，对一个定义了更新类型触发器的表来讲，当执行更新操作时，在删除表中存放了修改之前的旧值，然后在插入表中存放的是修改之后的新值。,9,浙江财经学院,13.2.3 插入视图和删除视图,当在定义了触发器的表上发生修改操作时会自动派生出两个视图，一个是插入视图，一个是删除视图。当在表上发生插入操作时，新插入的行将出现在inserted表中形成插入视图；当在表上发生删除操作时，被删除的旧行将出现deleted表中，形成删除视图。而更
7、新的实现过程是先删除旧行，然后再插入新行。,10,浙江财经学院,13.3 触发器的创建和管理,13.3.1 创建触发器 13.3.2 管理触发器 13.3.3 修改、删除触发器,11,浙江财经学院,13.3.1 创建触发器,在创建触发器以前必须考虑到以下几个方面：CREATE TRIGGER语句必须是批处理的第一个语句；创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户；触发器是数据库对象，所以其命名必须符合命名规则；尽管在触发器的SQL语句中可以引用其它数据库中的对象，但是，触发器只能创建在当前数据库中；一个触发器只能对应一个表，这是由触发器的机制决定的；在含有用DELETE或
8、UPDATE操作定义的外键的表中，不能定义INSTEAD OF和INSTEAD OF UPDATE触发器。在触发器定义中，所有建立和更改数据库以及数据库对象的语句、所有的drop语句都不允许在触发器中使用。,12,浙江财经学院,1用对象资源管理器创建触发器 启动对象资源管理器，登录到要使用的服务器。在对象资源管理器的左窗格中，展开要创建触发器的数据库文件夹，点击“表”文件夹前面的“+”号，此时在右窗格中显示该数据库的所有表。选择创建触发器的表，点击要创建触发器的数据表前面的“+”号，右击触发器选项，在出现的下一级子菜单中选择“新建触发器”菜单项。,13,浙江财经学院,2用CREATE TRIG
9、GER命令创建触发器,语法格式：CREATE TRIGGER trigger_name ON table|view WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE NOT FOR REPLICATION AS IF UPDATE(column)AND|OR UPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operator updated_bitmask)comparison_operator column_bitmask.n Sql 语句.n,14,浙江财经学院,重要生疏选项注释,（3
10、）WITH ENCRYPTION表示对包含有CREATE TRIGGER文本的syscomments 表进行加密。（8）NOT FOR REPLICATION表明当复制处理修改与触发器相关联的表时，触发器不能被执行。（11）IF UPDATE（column）测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作。（12）IF（COLUMNS_UPDATED()）仅在INSERT和UPDATE类型的触发器中使用，用其来检查所涉及的列是被更新还是被插入。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。,15,浙江财经学院,举例,【例】创建一个触发器，当向学生表
11、表中插入一条学生记录时，自动显示该表中的记录。可以用UPDATE(column)测试在指定的列上进行的INSERT或UPDATE操作。在INSERT操作中IF UPDATE将返回TRUE值，因为这些列插入了显式值或隐性（NULL）值。也可以用COLUMNS_UPDATED()来测试是否更新了指定的列。COLUMNS_UPDATED函数返回varbinary位模式，表示插入或更新了表中的哪些列。COLUMNS_UPDATED函数以从左到右的顺序返回位，最右边的位表示表中的第一列；向左的下一位表示第二列，依此类推。学号是学生表中的第一列，测试COLUMNS_UPDATED是否返回1（二进制0000
12、1）,16,浙江财经学院,举例,USE 教学管理GOCREATE TRIGGER T_学生表改变显示On 学生表 FOR INSERTASBEGINIF(COLUMNS_UPDATED()&1=1)SELECT*FROM 学生表END-验证 BEGIN TRANSACTIONINSERT INTO 学生表VALUES(S090103,*19971021*,李飞,男,130*12,温州,计算机,信电学院,160)ROLLBACK TRANSACTION,17,浙江财经学院,13.3.2 管理触发器,1使用对象资源管理器显示触发器信息（1）启动对象资源管理器，登录到要使用的服务器。（2）在对象资源
13、管理器的左窗格中，展开要创建触发器的数据库文件夹，单击“表”文件夹前面的“+”号，此时在下面显示该数据库的所有表。（3）点击要修改触发器的数据表前面的“+”号，出现触发器选项，再点击触发器前面的“+”号，下面显示该表上建立的所有触发器，将鼠标指向要修改的触发器，点击右键，在出现的下一级子菜单中选择“修改”菜单项。,18,浙江财经学院,13.3.2 管理触发器,2使用系统存储过程查看触发器 系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。（1）sp_help【例】查看我们已经建立的T_表改变显示触发器。sp_help T_表改变显示【例】查看
14、我们已经建立的T_表改变显示触发器的命令文本。sp_helptext T_表改变显示【例】查看我们已经建立的T_表改变显示触发器所涉及的表。sp_depends T_表改变显示,19,浙江财经学院,13.3.3 修改、删除触发器,1修改触发器（1）使用sp_rename命令，修改触发器的名字 sp_rename命令的语法格式为：sp_rename oldname,newname,20,浙江财经学院,（2）使用Alert trigger，修改触发器的正文 语法格式：ALTER TRIGGER trigger_name ON table|view WITH ENCRYPTION FOR|AFTER
15、|INSTEAD OF INSERT,UPDATE NOT FOR REPLICATION AS IF UPDATE(column)AND|OR UPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operator updated_bitmask)comparison_operator column_bitmask.n Sql 语句.n,21,浙江财经学院,USE 教学管理GOIF EXISTS(SELECT name FROM sysobjects WHERE name=T_表改变显示 AND type=TR)DROP TRIGGER T_表改变显示GO
16、,删除触发器,22,浙江财经学院,13.4 使用触发器实现强制业务规则,13.4.1 INSERT触发器 13.4.2 UPDATE触发器 13.4.3 DELETE 触发器 13.4.4 INSTEAD OF 触发器,23,浙江财经学院,13.4.1 INSERT触发器,【例】在大学数据库中，当新的学生选课注册信息增加到选课表表中的时候，要对开课表表中学生选课人数进行更新，且当人数超过最多能容纳的人数时，要提示选课人数已满的信息。USE 教学管理GOCREATE TRIGGER T_选课表插入触发ON 选课表FOR INSERTAS,24,浙江财经学院,BEGINDECLARE 已选人数 I
17、NT,限选人数 INTSELECT 已选人数=已选人数+1,限选人数=限选人数FROM 开课表 O,inserted iWHERE O.开课号=i.开课号IF(已选人数 限选人数)BEGIN PRINT 选修人数已满！ROLLBACK TRANSACTION ENDUPDATE 开课表SET 已选人数=已选人数 FROM 开课表 O,inserted i WHERE O.开课号=i.开课号END,25,浙江财经学院,-验证：-查看已选人数SELECT 开课号,限选人数,已选人数 FROM 开课表 WHERE 开课号=020102-在选课表里增加一条记录。下面用了错误捕捉方法，见例7-2。BEG
18、IN TRY BEGIN TRANSACTIONINSERT INTO 选课表VALUES(S060306,020102,NULL)COMMIT TRANSACTIONEND TRYBEGIN CATCH ROLLBACK TRANSACTIONEND CATCH-再查看已选人数 SELECT 开课号,限选人数,已选人数 FROM 开课表 WHERE 开课号=020102,26,浙江财经学院,13.4.2 UPDATE触发器,【例】教师表里的工号和负责人具有外键关系，当负责人工号修改了，负责人内容也要跟着改变。USE 教学管理GOIF EXISTS(SELECT name FROM sysob
19、jects WHERE name=T_负责人工号变化 AND type=TR)DROP TRIGGER T_负责人工号变化GOCREATE TRIGGER T_负责人工号变化ON 教师表FOR UPDATEAS,27,浙江财经学院,BEGINDECLARE old_工号CHAR(6),new_工号CHAR(6)SELECT old_工号=工号FROM deletedSELECT new_工号=i.工号FROM inserted i UPDATE 教师表 SET 负责人=new_工号 WHERE 负责人=old_工号END-此时用到inserted和deleted表，在inserted表中，存放
20、的是执行UPDATE操作的表中被修改的那些记录修改之后的新值，而在delelted表中，存放的是执行UPDATE操作的表中被修改的那些记录修改之前的旧值。,28,浙江财经学院,-验证BEGIN TRAN-查询教师表SELECT*FROM 教师表-修改教师表中的工号，将T01001变为T01003UPDATE 教师表 SET 工号=T01003 WHERE 工号=T01001-再查询教师表SELECT*FROM 教师表ROLLBACK,29,浙江财经学院,13.4.3 DELETE 触发器,【例】当某个学生退学时，须删除该学生的基本数据，并级联删除该学生的选课记录（需暂时去掉选课表上相对学生表的
21、外键约束）。CREATE TRIGGER T_学生数据删除ON 学生表FOR DELETEASBEGINDELETE FROM 选课表FROM 选课表 E,deleted dWHERE E.学号=d.学号END,30,浙江财经学院,-验证BEGIN TRAN-查看删除前的记录SELECT*FROM 学生表 S,选课表 E WHERE S.学号=E.学号-删除学生表，同时触发删除选课表中对应的数据DELETE FROM 学生表 WHERE 学号=S060101-查看删除后的记录SELECT*FROM 学生表S,选课表E WHERE S.学号=E.学号 ROLLBACK,31,浙江财经学院,13.
22、4.4 INSTEAD OF 触发器,前面三类触发器统称为AFTER 触发器（也叫“FOR”触发器），只能用在表上，而INSTEAD OF触发器既可以用在表上，也可以使用在视图上。用INSTEAD OF可以指定执行触发器而不是执行触发语句本身，从而屏蔽原来的SQL语句，而转向执行触发器内部的SQL语句。对同一操作只能定义一个INSTEAD OF触发器。,32,浙江财经学院,13.4.4 INSTEAD OF 触发器,【例】当删除教师表某教师信息时，需先查看开课表有没有该教师的代课情况，如果有，则不能删除。如果没有，就执行触发器中的删除语句完成删除。USE 教学管理GOIF EXISTS(SEL
23、ECT name FROM sysobjects WHERE name=T_教师表信息删除 AND type=TR)DROP TRIGGER T_教师表信息删除GOCREATE TRIGGER T_教师表信息删除ON 教师表INSTEAD OF DELETEAS,33,浙江财经学院,BEGINDECLARE 姓名 CHAR(20)SELECT 姓名=姓名 FROM deletedIF EXISTS(SELECT*FROM 开课表 O,deleted d WHERE O.工号=d.工号)PRINT 姓名+教师有开课计划，不能删除ELSE BEGIN DELETE FROM 教师表 FROM 教师
24、表T,deleted d WHERE T.工号=d.工号 PRINT 姓名+教师没有开课计划，已经删除 ENDEND,34,浙江财经学院,-验证BEGIN TRAN-查看删除前的信息SELECT*FROM 教师表 SELECT*FROM 开课表-删除教师信息DELETE FROM 教师表 WHERE 姓名=曲宏伟-查看删除后的信息SELECT*FROM 教师表 SELECT*FROM 开课表ROLLBACK,35,浙江财经学院,13.4.4 INSTEAD OF 触发器,【例】例9-11视图“V_信电学生成绩”引用了多个表，对视图不能直接执行更新删除，但可以使用INSTEAD OF触发器完成以
25、上功能。USE 教学管理GOIF EXISTS(SELECT name FROM sysobjects WHERE name=T_视图信息删除1 AND type=TR)DROP TRIGGER T_视图信息删除1GO,36,浙江财经学院,CREATE TRIGGER T_视图信息删除1ON V_信电学生成绩INSTEAD OF DELETEASBEGINDECLARE 学号 CHAR(7),开课号 CHAR(6)SELECT 学号=学号,开课号=开课号 FROM deleted-在INSTEAD OF触发器里实际是对表的操作DELETE FROM 选课表 WHERE 学号=学号AND 开课号
26、=开课号END,37,浙江财经学院,-验证BEGIN TRANSELECT*FROM V_信电学生成绩 WHERE 学号=S060101-对视图进行删除操作DELETE FROM V_信电学生成绩 WHERE 学号=S060101 AND 开课号=010201SELECT*FROM V_信电学生成绩 WHERE 学号=S060101ROLLBACK,38,浙江财经学院,13.6 触发器应用实例分析,分析1：由于学生选课管理的实际情况，学生在期初或前一学期结束之前就进行选课，而成绩是在学期末考试后输入，所以录入成绩实际上是对选课表的数据的修改。故我们可以创建该表的修改触发器，实现学分的自动累计。
27、由于成绩修改UPDATE语句可能涉及多个学生，故我们要在触发器中使用游标对每个学生进行判断修改。,39,浙江财经学院,CREATE TRIGGER T_选课学分修改ON 选课表FOR UPDATEASBEGINIF(ROWCOUNT0)BEGIN DECLARE old_成绩FLOAT,new_成绩FLOAT DECLARE 学号_d CHAR(7),开课号_d CHAR(6),学号_i CHAR(7),开课号_i CHAR(6)DECLARE 学分INT DECLARE CUR_选课新信息CURSOR FOR SELECT 学号,开课号,成绩 FROM inserted,【例13-10】创建
28、选课表的UPDATE触发器，实现学分的级联修改。,40,浙江财经学院,DECLARE CUR_选课旧信息CURSOR FOR SELECT 学号,开课号,成绩 FROM deleted OPEN CUR_选课新信息 OPEN CUR_选课旧信息 FETCH NEXT FROM CUR_选课新信息 INTO 学号_i,开课号_i,new_成绩 FETCH NEXT FROM CUR_选课旧信息 INTO 学号_d,开课号_d,old_成绩 SELECT 学分=学分 FROM 开课表O,课程表C WHERE O.课号=C.课号AND 开课号=开课号_i,41,浙江财经学院,WHILE fetch_
29、status=0 BEGIN IF(old_成绩is NULL)AND(new_成绩=60)UPDATE 学生表 SET 累计学分=累计学分+学分 WHERE 学号=学号_i IF(old_成绩=60)UPDATE 学生表 SET 累计学分=累计学分+学分 WHERE 学号=学号_i IF(old_成绩=60)AND(new_成绩 60 or new_成绩is NULL)UPDATE 学生表 SET 累计学分=累计学分-学分 WHERE 学号=学号_i,42,浙江财经学院,FETCH NEXT FROM CUR_选课新信息 INTO 学号_i,开课号_i,new_成绩 FETCH NEXT F
30、ROM CUR_选课旧信息 INTO 学号_d,开课号_d,old_成绩 SELECT 学分=学分 FROM 开课表O,课程表C WHERE O.课号=C.课号AND 开课号=开课号_i END CLOSE CUR_选课新信息 CLOSE CUR_选课旧信息 DEALLOCATE CUR_选课新信息 DEALLOCATE CUR_选课旧信息 END END,43,浙江财经学院,-验证BEGIN TRAN-查看修改前的信息SELECT*FROM 选课表WHERE 学号=S060101 SELECT*FROM 学生表WHERE 学号=S060101-进行修改update 选课表set 成绩=85
31、WHERE 学号=S060101 and 开课号=010201-查看修改后的信息SELECT*FROM 选课表WHERE 学号=S060101 SELECT*FROM 学生表WHERE 学号=S060101 ROLLBACK,44,浙江财经学院,分析2：对于成绩取消的情况，相当于删除了该课程的选课记录和成绩，因此我们可以设计选课表的删除出发器来实现学分的取消。同学自己试试。,45,浙江财经学院,小 结,触发器是一种特殊类型的存储过程，与其他类型存储过程不同的是：它是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server就会自动执行触发器所定义的SQL语句。本章我们主要介绍了触发器的概念、触发器的种类、触发器的工作原理以及对如何使用触发器实现复杂强制约束，包括触发器的创建、修改、删除以及对触发器的管理，并通过实例介绍对触发器的应用。,46,浙江财经学院,Thank you very much!,谢谢您的光临！,下一章,
