[其它]ch8-3触发器.ppt

第8章 数据库编程（续）触发器,国脉信息学院20120517,本节知识点,触发器概述 触发器类型 存储过程执行过程 创建存储过程 修改及删除存储过程 调用存储过程,触发器概述,场 景,学生情况表XSQK,课程表KC,成绩表XS_KC,银行的取款机系统,触发器概述,帐户信息表bank,交易信息表transInfo,张三取钱200 问题： 有没有自动修改张三的余额,张三开户1000元,李四开户1元,赵二,插入,触发器 概述,删除,赵二退休,赵二,员工表,退休员工表,问题：如何实现将赵二的信息自动保存到退休员工表中,触发器概述,问题分析 存在的问题 参照完整性 级联操作 复杂的约束 数据修改前后的差别 强制的业务规则,触发器概述,解决问题 解决方案 触发器（Trigger ） 触发器是一种特殊的存储过程，它在特定语言事件发生时自动执行，通常用于实现强制执行一定的业务规则，以保持数据完整性、检查数据有效性，实现数据管理任务和一些附加的功能。 触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。除此之外，触发器还有以下作用。 （1）触发器可以对数据库进行级联修改。 （2）实现比CHECK约束更为复杂的限制。 （3）比较数据修改前后的差别。 （4）强制表的修改要合乎业务规则。,触发器的类型,按照触发事件的不同，可以把Microsoft SQL Server 2005系统提供的触发器分成两大类型，即DML触发器和DDL触发器。,触发器的类型,DML触发器可以在数据库中数据修改时被执行。 DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。 DML触发器可以查询其他表，还可以包含复杂的T-SQL语句。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误（例如，磁盘空间不足），则整个事务自动回滚。,DDL触发器是Microsoft SQL Server 2005的新增功能。当服务器或数据库中发生数据定义语言（DDL）事件时将调用这些触发器。 DDL触发器与DML触发器的相同之处在于都需要触发事件进行触发，但是，它与DML触发器不同的是，它不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而触发，相反，它会为响应多种数据定义语言（DDL）语句（如：CREATE、ALTER）而触发。规范数据库操作，防止数据库表结构被修改等。,触发器的类型,DML触发器的创建和应用,在Microsoft SQL Server 2005系统中，按照触发器事件类型的不同，可将DML触发器分成3种类型：INSERT类型、UPDATE类型和DELETE类型 如果该表有INSERT类型的DML触发器,则当向一个表中插入数据时，则该INSERT类型的触发器触发执行； 如果该表有UPDATE类型的DML触发器，则当对该触发器表中的数据执行更新操作时，该触发器就执行； 如果该表有DELETE类型的DML触发器，当对该触发器表中的数据执行删除操作时，该DELETE类型的DML触发器就触发执行。 也可以将这三种触发器组合起来使用。,DML触发器的创建和应用,按照触发器和触发事件的操作时间划分，可以把DML触发器分为AFTER触发器和INSTEAD OF触发器。 当在INSERT、UPDATE、DELETE语句执行之后才执行DML触发器的操作时，这种触发器的类型就是AFTER触发器。AFTER触发器只能在表上定义。,DML触发器的创建和应用,按照触发器和触发事件的操作时间划分，可以把DML触发器分为AFTER触发器和INSTEAD OF触发器。 如果希望使用触发器操作代替触发事件操作，可以使用INSTEAD OF类型的触发器。也就是说，INSTEAD OF触发器可以替代INSERT、UPDATE和DELETE触发事件的操作。INSTEAD OF触发器既可以建在表上，也可以建在视图上。通过在视图上建立触发器，可以大大增强通过视图修改表中数据的功能。,DML触发器的创建和应用,DML触发器的主要优点如下： DML触发器可以防止恶意或错误的插入、修改及删除操作，并强行比较检查约束定义的限制更为复杂的其他限制。与检查约束不同，DML触发器可以引用其他表中的列。例如，触发器可以使用另一个表中的SELECT比较插入或更新的数据，以及执行其他操作，如修改数据或显示用户定义错误信息。 DML触发器可以评估数据修改前后表的状态，并根据该差异采取措施。 一个表中的多个同类DML触发器（INSERT、UPDATE或DELETE）允许采取多个不同的操作来响应同一个修改语句。,DML触发器的创建和应用,创建DML触发器应该考虑以下几个问题： CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用于一个表。 触发器只能在当前的数据库中创建，但是可以引用当前数据库的外部对象。 创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。 触发器是一种数据库对象，其名称必须遵循标识符的命名规则。 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。,DML触发器的创建和应用, 如果一个表的外键包含对定义的 DELETE或UPDATE 操作的级联，则不能定义INSTEAD OF 和INSTEAD OF UPDATE触发器 虽然TRUNCATE TABLE 语句类似于没有WHERE子句（用于删除行）的DELETE语句，但它并不会引发DELETE触发器，因为TRUNCATE TABLE 语句没有记录。 如果指定了触发器架构名称来限定触发器，则将以相同的方式限定表名称。 在触发器内可以指定任意的 SET 语句。选择的 SET 选项在触发器执行期间保持有效，然后恢复为原来的设置。 在 DML 触发器中不允许使用下列 Transact-SQL 语句：ALTER DATABASE、CREATE DATABASE、DROP DATABASE、RECONFIGURE、LOAD LOG、LOAD DATABASE、RESTORE LOG、RESTORE DATABASE,DML触发器的创建和应用,1. DML 触发器的创建 使用SQL Server管理控制台创建DML触发器 在SQL Server管理控制台中，展开指定的服务器和数据库，单击要创建触发器的数据表，右击其中的“触发器”文件夹。从弹出的快捷菜单中选择“新建触发器”选项，会出现新建触发器模板窗口。,使用Transact-SQL语句创建DML触发器,CREATE TRIGGER 触发器名 ON 表|视图 WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE NOT FOR REPLICATION AS IF UPDATE(列名)AND|OR UPDATE(列名)n SQL语句,（1）FOR|AFTER。FOR与AFTER同义，指定触发器只有在触发器SQL语句中指定的所有操作都已成功后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器，即为后触发。只能在表上定义 （2）INSTEAD OF。指定执行触发器而不执行造成触发的SQL语句，从而替代造成触发的语句。在表或视图上，每个INSERT、UPDATE或DELETE语句只能定义一个INSTEAD OF触发器，即替代触发。 （3）INSERT,UPDATE,DELETE是指定在表上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用任意顺序组合的这些关键字。当进行触发条件的操作时（INSERT、UPDATE或DELETE），将执行SQL语句中指定的触发器操作。,使用Transact-SQL语句创建DML触发器,（4）NOT FOR REPLICATION。表示当复制进程更改触发器所涉及的表时，不要执行该触发器。 （5）IF UPDATE(列名)。测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作，可以指定多列。因为已经在ON子句中指定了表名，所以在IF UPDATE子句中的列名前不要包含表名。若要测试在多个列上进行的INSERT或UPDATE操作，要分别单独地指定UPDATE(列名)子句。在INSERT操作中IF UPDATE将返回TRUE值, 因为这些列插入了显式值或隐性 (NULL) 值。,使用Transact-SQL语句创建DML触发器,（6）注意：在使用WITH ENCRYPTION选项时需要注意两点：一是原始触发器的文件丢失，将不能从syscomments表中重新保存加密文本；二是文本加密后，在数据库升级为新版本时不能修改，也不能重新存入新版本中。也就是说，如果触发器文本有可能需要修改，就不要随便将其加密。,使用Transact-SQL语句创建DML触发器,DML 触发器使用 deleted 和 inserted 逻辑表。它们在结构上和触发器所在的表的结构相同，SQL Server会自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件。 Deleted表用于存储delete，update语句所影响的行的副本。在执行delete或update语句时，行从触发器表中删除，并传输到deleted表中。 Inserted表用于存储Insert或update语句所影响的行的副本，在一个插入或更新事务处理中，新建的行被同时添加到Inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。,DML触发器的创建和应用,注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。 一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：,DML触发器的创建和应用,DML触发器的应用,1、使用INSERT触发器 INSERT触发器常被用来更新时间标记字段，或者验证被触发器监控的字段中数据满足要求的标准，以确保数据的完整性。 当向数据库中插入数据时，INSERT触发器将被触发执行。INSERT触发器被触发时，新的记录增加到触发器的对应表中，并且同时也添加到inserted表中。,INSERT触发器,插入记录行,触发insert触发器。向inserted表中插入新行的副本,触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作,INSERT触发器的工作原理：,INSERT 触发器示例,问题： 解决上述的银行取款问题：当向交易信息表(transInfo)中插入一条交易信息时，我们应自动更新对应帐户的余额。,分析： 在交易信息表上创建INSERT触发器 从inserted临时表中获取插入的数据行 根据交易类型(transType)字段的值是存入/支取， 增加/减少对应帐户的余额。,-关键代码- CREATE TRIGGER trig_transInfo ON transInfo FOR INSERT AS DECLARE type char(4),outMoney float DECLARE myCardID varchar(10 SELECT type=transType,outMoney=transMoney， myCardID=cardID FROM inserted IF (type='支取') UPDATE cardinfo SET currentMoney=currentMoney-outMoney WHERE cardID=myCardID ELSE UPDATE cardinfo SET currentMoney=currentMoney+outMoney WHERE cardID=myCardID GO,INSERT 触发器示例,从inserted表中获取交易类型、教员金额等,根据交易类型，减少或增加对应卡号的余额,-测试代码- insert into transinfo values('1000000001','支取',200) insert into transinfo values('1000000002','存入',800) select * from cardinfo,INSERT 触发器示例,2、使用DELETE触发器 DELETE触发器通常用于两种情况： 第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。例如在学生表中删除记录时，同时要删除和某个学生相关的其他信息表中的信息。通常见于用作其他表的外部键的记录； 第二种情况是执行可删除主记录的级联删除操作。,DML触发器的应用,DELETE触发器,删除记录行,触发delete触发器向deleted表中插入被删除的副本,触发器检查deleted表中被删除的数据，决定是否需要回滚或执行其他操作,DELETE触发器的工作原理：,问题： 当删除交易信息表时，要求自动备份被删除的数据到表backupTable中 。,分析： 在交易信息表上创建DELETE触发器 被删除的数据可以从deleted表中获取,DELETE触发器示例,-关键代码- CREATE TRIGGER trig_delete_transInfo ON transInfo FOR DELETE AS print '开始备份数据，请稍后' IF NOT EXISTS(SELECT * FROM sysobjects WHERE name='backupTable') SELECT * INTO backupTable FROM deleted ELSE INSERT INTO backupTable SELECT * FROM deleted print '备份数据成功，备份表中的数据为:' SELECT * FROM backupTable GO,从deleted表中获取被删除的交易记录,DELETE触发器示例,3、使用UPDATE触发器 UPDATE触发器和INSERT触发器的工作过程基本一致，修改一条记录等于插入了一条新的记录并且删除一条旧的记录，同时使用inserted表和deleted表。 理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。 一个Update 的过程可以看作为：复制旧的记录到Deleted表，生成新的记录到Inserted表，然后删除Student记录并写入新纪录。,UPDATE触发器,删除记录行,向deleted表中插入被删除的副本,检查deleted和inserted表中的数据，确定是否需要回滚或执行其他操作,UPDATE触发器的工作原理：,向inserted表中插入被添加的副本,插入记录行,问题： 跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。,分析： 在bank表上创建UPDATE触发器 修改前的数据可以从deleted表中获取 修改后的数据可以从inserted表中获取,UPDATE触发器示例,-关键代码- CREATE TRIGGER trig_update_bank ON bank FOR UPDATE AS DECLARE beforeMoney MONEY,afterMoney MONEY SELECT beforeMoney=currentMoney FROM deleted SELECT afterMoney=currentMoney FROM inserted IF ABS(afterMoney-beforeMoney)20000 BEGIN print '交易金额:'+convert(varchar(8), ABS(afterMoney-beforeMoney) RAISERROR ('每笔交易不能超过2万元，交易失败',16,1) ROLLBACK TRANSACTION END GO,从deleted表中获取交易前的余额，从inserted表中获取交易后的余额,UPDATE触发器,交易金额是否2万,回滚事务，撤销交易,4、列级 UPDATE 触发器,UPDATE触发器除了跟踪数据的变化（修改）外，还可以检查是否修改了某列的数据 使用UPDATE（列）函数检测是否修改了某列,问题： 交易日期一般由系统自动产生，默认为当前日期。为了安全 起见，一般禁止修改，以防舞弊。,分析： UPDATE（列名）函数可以检测是否修改了某列,-关键代码- CREATE TRIGGER trig_update_transInfo ON transInfo FOR UPDATE AS IF UPDATE(transDate) BEGIN print '交易失败.' RAISERROR (安全警告：交易日期不能修改， 由系统自动产生',16,1) ROLLBACK TRANSACTION END GO,检查是否修改了交易日期列transDate,回滚事务，撤销交易,列级 UPDATE 触发器,列级 UPDATE 触发器,【例】创建一个修改触发器，该触发器防止用户修改xs表中的学号。 create trigger update_xh on xs for update as if update (学号) begin print 不能修改学号 rollback end go,分析： create trigger notallowdelete on cj instead of delete as print 'not allowed delete' go,5、使用INSTEAD OF触发器,如果视图的数据来自于多个基表，则必须使用INSTAED OF触发器支持引用表中的数据的插入、更新和删除操作。 如果视图的列为以下几种情况之一： 基表中的计算列 基表中的标识列 具有timestamp数据类型的基表列 该视图的INSERT语句必须为这些列指定值，INSTEAD OF触发器在构成将值插入基表的INSERT语句时，会忽略指定的值。 下面通过一个例子说明。,5、使用INSTEAD OF触发器,（1）简单的INSTEAD OF触发器 【例】创建一个INSTEAD OF触发器，要求实现以下功能：在kc表上创建一个删除类型的触发器TR_NotAllowDelete，当在kc表中删除记录时，触发该触发器，显示“不允许删除表中数据！”的提示信息。,If exists(select name from sysobjects where name=' TR_NotAllowDelete ' and type='tr') drop trigger TR_NotAllowDelete Go create trigger TR_NotAllowDelete on kc instead of delete as print 'INSTEAD OF 触发器开始执行' print '本表中的数据不允许被删除！不能执行删除操作！' go,5、使用INSTEAD OF触发器,（2）在视图上创建INSTEAD OF触发器： 在“软件技术专业学生成绩视图”上创建触发器instead_ins，要求向该视图插入数据时，实际只向xs表的“学号”、“姓名”字段插入数据，创建成功后向视图中插入数据体会触发器的作用,if exists(select * from sysobjects where name='instead_ins' and type='tr') drop trigger instead_ins go create trigger instead_ins on 软件技术专业学生成绩视图 instead of insert as begin insert into xs(学号,姓名) select 学号,姓名 from inserted end go,insert into 软件技术专业学生成绩视图 values ('0001','王小凡','2004','体育','98') insert into 软件技术专业学生成绩视图 values ('0002','王凡',null,null,null) insert into 软件技术专业学生成绩视图 values ('0002',' 王凡'),6、使用嵌套的触发器。如果一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器，这些触发器就是嵌套触发器。例如，在执行过程中，如果一个触发器修改某个表，而这个表已经有其他触发器，这时就要使用嵌套触发器。 嵌套触发器最深可以嵌套至32层。,使用系统存储过程sp_config设置是否使用嵌套触发器 Exec sp_config inested_trigger,0|1 当设置为1时，表示允许使用嵌套触发器，否则禁止使用。,DDL触发器的创建与应用 （1/3),DDL 触发器一般用于执行以下操作： 防止对数据库架构进行某些更改。 希望数据库中发生某种情况以响应数据库架构中的更改。 要记录数据库架构中的更改或事件。 仅在运行触发 DDL 触发器的 DDL 语句后，DDL 触发器才会激发。DDL 触发器无法作为 INSTEAD OF 触发器使用。仅在要响应由 Transact-SQL DDL 语法指定的 DDL 事件时，DDL 触发器才会激发。不支持执行类似 DDL 操作的系统存储过程。,DDL触发器的创建与应用 （2/3),1. 创建DDL触发器 使用CREATE TRIGGER命令创建DDL触发器的语法形式如下： CREATE TRIGGER trigger_name ON ALL SERVER | DATABASE WITH ,.n FOR | AFTER event_type | event_group ,.n AS sql_statement ; .n | EXTERNAL NAME ; := ENCRYPTION EXECUTE AS Clause := assembly_name.class_name.method_name,DDL触发器的创建与应用 （3/3),2. DDL触发器的应用 在响应当前数据库或服务器中处理的 Transact-SQL 事件时，可以激发 DDL 触发器。触发器的作用域取决于事件。 例如，每当数据库中发生 CREATE TABLE 事件时，都会触发为响应 CREATE TABLE 事件创建的 DDL 触发器。每当服务器中发生 CREATE LOGIN 事件时，都会触发为响应 CREATE LOGIN 事件创建的 DDL 触发器。,【例】 使用 DDL 触发器来防止数据库中的任一表被修改或删除。 CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger “safety“ to drop or alter tables!' ROLLBACK,查看并修改触发器,1使用SQL Server管理控制台查看并修改触发器 （1）查看并修改触发器定义信息 在SQL Server管理控制台中，展开指定的服务器和数据库，选择指定的数据库和表，单击要查看的表，单击其中的“触发器”文件夹，此时会在右侧的“摘要”窗口中看到此表中的所有触发器，右击某个触发器名称，从弹出的快捷菜单中选择“修改”选项，在打开的窗口中可以查看到定义触发器的语句，在窗口中也可以直接修改触发器的定义。,查看并修改触发器,（2）查看与触发器有依赖关系的其他数据库对象 右击某个触发器名称，从弹出的快捷菜单中选择“查看依赖关系”选项，在出现的“对象依赖关系”对话框中可以查看到依赖于此触发器的对象和此触发器依赖的对象。,查看并修改触发器,2使用系统存储过程查看触发器 可以使用系统存储过程sp_help、sp_helptext和sp_depends分别查看触发器的不同信息。它们的具体用途和语法形式如下： sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。 语法格式：sp_help 触发器名称 sp_helptext：用于查看触发器的正文信息。 语法格式： sp_helptext 触发器名称,查看并修改触发器,sp_depends：用于查看指定触发器所引用的表 语法格式：sp_depends 触发器名称 【例】使用系统存储过程查看tr_cj_insert触发器的一般信息。 use xsgl go sp_help tr_cj_insert,【例】使用系统存储过程查看tr_cj_insert触发器所引用的表 use xsgl go sp_depends tr_cj_insert 查询出对应数据库中的触发器 USE xsgl go SELECT * FROM sys.triggers,查看并修改触发器,2. 修改触发器 可以通过SQL Server Management Studio、存储过程和T-SQL语句来修改触发器的正文和名称。 （1）使用SQL Server Management Studio修改已创建的触发器的信息。,查看并修改触发器,（2）使用ALTER TRIGGER修改DML触发器的语法形式如下 ALTER TRIGGER schema_name.trigger_name ON ( table | view ) WITH ,.n ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE NOT FOR REPLICATION AS sql_statement ; .n | EXTERNAL NAME ; := ENCRYPTION := assembly_name.class_name.method_name,删除触发器,删除已创建的触发器有三种方法： （1）使用系统命令DROP TRIGGER删除指定的触发器。其语法形式如下： DROP TRIGGER trigger ,n （2）删除触发器所在的表。删除表时，SQL Server将会自动删除与该表相关的触发器。 （3）在SQL Server Management Studio中，展开指定的服务器和数据库，找到想要删除的触发器，右击要删除的触发器，从弹出的快捷菜单中选择“删除”选项，,课堂练习：,准备工作：在xsgl数据库中创建三张表 xb（系部代码,系部,系主任） zy（专业代码,专业,系部代码） bj（班级代码,班级,专业代码,系部代码,备注） 1、创建一个INSTER触发器：在xsgl数据库中建立一个名为“inster_xibu”的INSTER触发器,存储在zy表中。向zy表中插入记录时，如果插入了“系部”表中没有的代码，则提示“系部代码不存在系部表中，不能插入记录，插入将终止！”，否则提示“插入成功”,课堂练习：,准备工作：在xsgl数据库中创建三张表 xb（系部代码,系部,系主任） zy（专业代码,专业,系部代码） bj（班级代码,班级,专业代码,系部代码,备注） 2、创建一个DELETE触发器：在xsgl数据库中建立一个名为“delete_zy”的DELETE触发器,存储在zy表中。删除zy表中记录时，如果bj表引用了此记录的专业代码，则提示用户“不能删除记录”，否则提示“记录已删除”,课堂练习：,准备工作：在xsgl数据库中创建三张表 xb（系部代码,系部,系主任） zy（专业代码,专业,系部代码） bj（班级代码,班级,专业代码,系部代码,备注） 3、创建一个UPDATE触发器：在xsgl数据库中建立一个名为“update_zy”的UPDATE触发器,存储在zy表中。更新zy表中的“专业”字段时，提示用户“不能修改专业”,