触发器原理及使用.ppt

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 触发器的概念及作用,触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。 触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。存储过程和触发器同是提高数据库服务器性能的有力工具。 触发器不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。,4,浙江财经学院,13.1.1 触发器的概念及作用,触发器的主要作用: 实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。 （1）强化约束（EnFORce restriction） 触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的更新和变化。 （2）级联运行（Cascaded Operation） 触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。 （3）存储过程的调用（Stored Procedure Invocation） 为了响应数据库更新，触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS之外进行操作。,5,浙江财经学院,13.1.2 触发器的种类,SQL Server 支持两种类型的触发器： （1）AFTER触发器 即为SQL Server 2000版本以前所介绍的触发器。该类型触发器要求只有执行完某一操作（INSERT、UPDATE、DELETE），并处理过所有约束后，触发器才被触发，且只能在表上定义。如果操作违反约束条件，将导致事务回滚，这时就不会执行后触发器。 （2）INSTEAD OF触发器 该类触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。可在表上定义INSTEAD OF触发器，也可以在视图上定义INSTEAD OF触发器，但对同一操作只能定义一个INSTEAD OF触发器。,6,浙江财经学院,13.2 触发器原理,触发器具有强大的功能，那么MS SQL Server 是如何使得触发器能够感知数据库数据的变化、维护数据库参照完整性及比CHECK约束更复杂的约束呢？下面我们将对其工作原理及实现做较为详细的介绍，以便大家学习创建、理解和使用各种类型的触发器，完成各种任务。,7,浙江财经学院,13.2.1 插入表的功能,对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入（INSERT）操作，系统就会生成一个特殊表-插入表（inserted），这个表驻留在内存中，不是存储在数据库中，因此不允许用户直接对其修改。 对于用户插入的所有行来说，都有一个相应的副本拷贝存放到插入表（inserted）中，即插入表就是用来存储原表插入的新数据行。,8,浙江财经学院,13.2.2 删除表的功能,对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除（DELETE）操作，则将所有的被删除的行存放至删除表（deleted表）中。这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从删除表（deleted表）中得以还原。 需要强调的是，更新（UPDATE）操作包括两个部分，即先将旧的内容删除，然后将新值插入。因此，对一个定义了更新类型触发器的表来讲，当执行更新操作时，在删除表中存放了修改之前的旧值，然后在插入表中存放的是修改之后的新值。,9,浙江财经学院,13.2.3 插入视图和删除视图,当在定义了触发器的表上发生修改操作时会自 动派生出两个视图，一个是插入视图，一个是删除视 图。当在表上发生插入操作时，新插入的行将出现在 inserted表中形成插入视图；当在表上发生删除操作 时，被删除的旧行将出现deleted表中，形成删除视图。 而更新的实现过程是先删除旧行，然后再插入新行。,10,浙江财经学院,13.3 触发器的创建和管理,13.3.1 创建触发器 13.3.2 管理触发器 13.3.3 修改、删除触发器,11,浙江财经学院,13.3.1 创建触发器,在创建触发器以前必须考虑到以下几个方面： CREATE TRIGGER语句必须是批处理的第一个语句； 创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户； 触发器是数据库对象，所以其命名必须符合命名规则； 尽管在触发器的SQL语句中可以引用其它数据库中的对象，但是，触发器只能创建在当前数据库中； 一个触发器只能对应一个表，这是由触发器的机制决定的； 在含有用DELETE或UPDATE操作定义的外键的表中，不能定义INSTEAD OF和INSTEAD OF UPDATE触发器。 在触发器定义中，所有建立和更改数据库以及数据库对象的语句、所有的drop语句都不允许在触发器中使用。,12,浙江财经学院,1用对象资源管理器创建触发器 启动对象资源管理器，登录到要使用的服务器。 在对象资源管理器的左窗格中，展开要创建触发器的数据库文件夹，点击“表”文件夹前面的“+”号，此时在右窗格中显示该数据库的所有表。 选择创建触发器的表，点击要创建触发器的数据表前面的“+”号，右击触发器选项，在出现的下一级子菜单中选择“新建触发器”菜单项。,13,浙江财经学院,2用CREATE TRIGGER命令创建触发器,语法格式： 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）WITH ENCRYPTION表示对包含有CREATE TRIGGER文本的syscomments 表进行加密。 （8）NOT FOR REPLICATION表明当复制处理修改与触发器相关联的表时，触发器不能被执行。 （11）IF UPDATE（column）测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作。 （12）IF（COLUMNS_UPDATED()）仅在INSERT和UPDATE类型的触发器中使用，用其来检查所涉及的列是被更新还是被插入。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。,15,浙江财经学院,举例,【例】创建一个触发器，当向学生表表中插入一条学生记录时，自动显示该表中的记录。 可以用UPDATE (column)测试在指定的列上进行的INSERT或UPDATE操作。在INSERT操作中IF UPDATE将返回TRUE值，因为这些列插入了显式值或隐性（NULL）值。也可以用COLUMNS_UPDATED()来测试是否更新了指定的列。COLUMNS_UPDATED函数返回varbinary位模式，表示插入或更新了表中的哪些列。COLUMNS_UPDATED函数以从左到右的顺序返回位，最右边的位表示表中的第一列；向左的下一位表示第二列，依此类推。 学号是学生表中的第一列，测试COLUMNS_UPDATED是否返回1（二进制00001）,16,浙江财经学院,举例,USE 教学管理 GO CREATE TRIGGER T_学生表改变显示 On 学生表 FOR INSERT AS BEGIN IF (COLUMNS_UPDATED()&1=1) SELECT * FROM 学生表 END -验证 BEGIN TRANSACTION INSERT INTO 学生表VALUES(S090103, *19971021*, 李飞, 男, 130*12, 温州, 计算机, 信电学院, 160) ROLLBACK TRANSACTION,17,浙江财经学院,13.3.2 管理触发器,1使用对象资源管理器显示触发器信息 （1）启动对象资源管理器，登录到要使用的服务器。 （2）在对象资源管理器的左窗格中，展开要创建触发器的数据库文件夹，单击“表”文件夹前面的“+”号，此时在下面显示该数据库的所有表。 （3）点击要修改触发器的数据表前面的“+”号，出现触发器选项，再点击触发器前面的“+”号，下面显示该表上建立的所有触发器，将鼠标指向要修改的触发器，点击右键，在出现的下一级子菜单中选择“修改”菜单项。,18,浙江财经学院,13.3.2 管理触发器,2使用系统存储过程查看触发器 系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。（1）sp_help 【例】查看我们已经建立的T_表改变显示触发器。 sp_help T_表改变显示 【例】查看我们已经建立的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 | 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 教学管理 GO IF EXISTS (SELECT name FROM sysobjects WHERE name =T_表改变显示 AND type = TR) DROP TRIGGER T_表改变显示 GO,删除触发器,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 教学管理 GO CREATE TRIGGER T_选课表插入触发 ON 选课表 FOR INSERT AS,24,浙江财经学院,BEGIN DECLARE 已选人数 INT,限选人数 INT SELECT 已选人数=已选人数+1, 限选人数=限选人数 FROM 开课表 O,inserted i WHERE O.开课号=i.开课号 IF (已选人数 限选人数) BEGIN PRINT 选修人数已满！ ROLLBACK TRANSACTION END UPDATE 开课表 SET 已选人数=已选人数 FROM 开课表 O, inserted i WHERE O.开课号=i.开课号 END,25,浙江财经学院,-验证： -查看已选人数 SELECT 开课号,限选人数,已选人数 FROM 开课表 WHERE 开课号=020102 -在选课表里增加一条记录。下面用了错误捕捉方法，见例7-2。 BEGIN TRY BEGIN TRANSACTION INSERT INTO 选课表VALUES(S060306, 020102,NULL) COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH -再查看已选人数 SELECT 开课号,限选人数,已选人数 FROM 开课表 WHERE 开课号=020102,26,浙江财经学院,13.4.2 UPDATE触发器,【例】教师表里的工号和负责人具有外键关系，当负责人工号修改了，负责人内容也要跟着改变。 USE 教学管理 GO IF EXISTS (SELECT name FROM sysobjects WHERE name =T_负责人工号变化 AND type = TR) DROP TRIGGER T_负责人工号变化 GO CREATE TRIGGER T_负责人工号变化 ON 教师表 FOR UPDATE AS,27,浙江财经学院,BEGIN DECLARE old_工号CHAR(6),new_工号CHAR(6) SELECT old_工号=工号 FROM deleted SELECT new_工号=i.工号 FROM inserted i UPDATE 教师表 SET 负责人=new_工号 WHERE 负责人=old_工号 END -此时用到inserted和deleted表，在inserted表中，存放的是执行UPDATE操作的表中被修改的那些记录修改之后的新值，而在delelted表中，存放的是执行UPDATE操作的表中被修改的那些记录修改之前的旧值。,28,浙江财经学院,-验证 BEGIN TRAN -查询教师表 SELECT * FROM 教师表 -修改教师表中的工号，将T01001变为T01003 UPDATE 教师表 SET 工号=T01003 WHERE 工号=T01001 -再查询教师表 SELECT * FROM 教师表 ROLLBACK,29,浙江财经学院,13.4.3 DELETE 触发器,【例】当某个学生退学时，须删除该学生的基本数据，并级联删除该学生的选课记录（需暂时去掉选课表上相对学生表的外键约束）。 CREATE TRIGGER T_学生数据删除 ON 学生表 FOR DELETE AS BEGIN DELETE FROM 选课表 FROM 选课表 E,deleted d WHERE 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.4.4 INSTEAD OF 触发器,前面三类触发器统称为AFTER 触发器（也叫“FOR”触发器），只能用在表上，而INSTEAD OF触发器既可以用在表上，也可以使用在视图上。用INSTEAD OF可以指定执行触发器而不是执行触发语句本身，从而屏蔽原来的SQL语句，而转向执行触发器内部的SQL语句。 对同一操作只能定义一个INSTEAD OF触发器。,32,浙江财经学院,13.4.4 INSTEAD OF 触发器,【例】当删除教师表某教师信息时，需先查看开课表有没有该教师的代课情况，如果有，则不能删除。如果没有，就执行触发器中的删除语句完成删除。 USE 教学管理 GO IF EXISTS (SELECT name FROM sysobjects WHERE name =T_教师表信息删除 AND type = TR) DROP TRIGGER T_教师表信息删除 GO CREATE TRIGGER T_教师表信息删除 ON 教师表 INSTEAD OF DELETE AS,33,浙江财经学院,BEGIN DECLARE 姓名 CHAR(20) SELECT 姓名=姓名 FROM deleted IF EXISTS(SELECT * FROM 开课表 O,deleted d WHERE O.工号=d.工号) PRINT 姓名+教师有开课计划，不能删除 ELSE BEGIN DELETE FROM 教师表 FROM 教师表T,deleted d WHERE T.工号=d.工号 PRINT 姓名+教师没有开课计划，已经删除 END END,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触发器完成以上功能。 USE 教学管理 GO IF EXISTS (SELECT name FROM sysobjects WHERE name =T_视图信息删除1 AND type = TR) DROP TRIGGER T_视图信息删除1 GO,36,浙江财经学院,CREATE TRIGGER T_视图信息删除1 ON V_信电学生成绩 INSTEAD OF DELETE AS BEGIN DECLARE 学号 CHAR(7),开课号 CHAR(6) SELECT 学号=学号,开课号=开课号 FROM deleted -在INSTEAD OF触发器里实际是对表的操作 DELETE FROM 选课表 WHERE 学号=学号AND 开课号=开课号 END,37,浙江财经学院,-验证 BEGIN TRAN SELECT * FROM V_信电学生成绩 WHERE 学号=S060101 -对视图进行删除操作 DELETE FROM V_信电学生成绩 WHERE 学号=S060101 AND 开课号=010201 SELECT * FROM V_信电学生成绩 WHERE 学号=S060101 ROLLBACK,38,浙江财经学院,13.6 触发器应用实例分析,分析1：由于学生选课管理的实际情况，学生在期初或前一学期结束之前就进行选课，而成绩是在学期末考试后输入，所以录入成绩实际上是对选课表的数据的修改。故我们可以创建该表的修改触发器，实现学分的自动累计。由于成绩修改UPDATE语句可能涉及多个学生，故我们要在触发器中使用游标对每个学生进行判断修改。,39,浙江财经学院,CREATE TRIGGER T_选课学分修改 ON 选课表 FOR UPDATE AS BEGIN IF (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】创建选课表的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_status=0 BEGIN IF (old_成绩is NULL) AND (new_成绩=60) UPDATE 学生表 SET 累计学分=累计学分+学分 WHERE 学号=学号_i IF (old_成绩= 60) UPDATE 学生表