db-chapter05之触发器.ppt

1、数据库系统概论第5章 触发器的应用触发器的概述触发器的概述触发器的创建触发器的创建触发器的维护触发器的维护触发器的应用触发器的应用数据库系统概论触发器概述 Microsoft SQL Server 2000 提供了两种主要机制来强制提供了两种主要机制来强制业务规则和数据完整性：业务规则和数据完整性：约束和触发器。约束和触发器。约束是实现完整性要求约束是实现完整性要求高效且实用高效且实用的重要方法，但（除了外的重要方法，但（除了外键约束）只能涉及一个表，当根据需要涉及多表键约束）只能涉及一个表，当根据需要涉及多表Check约束时，约束时，就无法用就无法用Check约束来解决。约束来解决。触发器是

2、一种特殊类型的存储过程，它在指定表中的数据发触发器是一种特殊类型的存储过程，它在指定表中的数据发生变化时自动生效，唤醒调用触发器以响应生变化时自动生效，唤醒调用触发器以响应 INSERT、UPDATE 或或 DELETE 语句。触发器可以操作其它表，并可以包含复杂的语句。触发器可以操作其它表，并可以包含复杂的 Transact-SQL 语句。所以触发器可以用来对表实施更加复杂的完语句。所以触发器可以用来对表实施更加复杂的完整性约束，当触发器表所保存的数据发生改变时，触发器会自动整性约束，当触发器表所保存的数据发生改变时，触发器会自动激活，从而防止对数据的不正确更新。激活，从而防止对数据的不正确

3、更新。数据库系统概论触发器的概述1 触发器概述触发器概述2 触发器的作用触发器的作用3 触发器与约束的区别触发器与约束的区别4 触发器的类型触发器的类型5 Inserted表和表和Deleted表表数据库系统概论 触发器的概述1 触发器概述触发器概述 在在SQL Server中一张表可以有多个触发器，用户可以针中一张表可以有多个触发器，用户可以针对更新表操作（对更新表操作（INSERT、UPDATE、DELETE）分别设置）分别设置触发器，也可以针对一张表上的特定操作设置多个触发器。触发器，也可以针对一张表上的特定操作设置多个触发器。尽管触发器可以容纳非常复杂的尽管触发器可以容纳非常复杂的T-

4、SQL语句，但是不语句，但是不管触发器所进行的操作有多复杂，管触发器所进行的操作有多复杂，SQL Server将触发器和将触发器和触发它的语句作为可在触发器内回滚的单个事务对待触发它的语句作为可在触发器内回滚的单个事务对待。数据库系统概论 触发器的概述2 触发器的作用触发器的作用 触发器可通过数据库中的相关表实现级联更改；触发器可通过数据库中的相关表实现级联更改；不过，通过级联参照完整性约束可以更有效地执行不过，通过级联参照完整性约束可以更有效地执行这些更改。但是在某些应用环境，必须使用触发器这些更改。但是在某些应用环境，必须使用触发器来操作。来操作。触发器可以实现比用触发器可以实现比用 CH

5、ECK 约束定义的约束约束定义的约束更为复杂的约束，触发器可以引用其它表中的列，更为复杂的约束，触发器可以引用其它表中的列，即多表约束规则。即多表约束规则。数据库系统概论 触发器的概述v 触发器也可以评估数据更新前后的表状态，并触发器也可以评估数据更新前后的表状态，并根据其差异采取对策。根据其差异采取对策。v 在一个表同一类型的操作（在一个表同一类型的操作（INSERT、UPDATE 或或 DELETE）上设置多个触发器，从而可以针对）上设置多个触发器，从而可以针对不同的更新语句执行多种不同的操作不同的更新语句执行多种不同的操作。数据库系统概论 触发器的概述3 触发器与约束的区别触发器与约束的

6、区别 约束和触发器在特殊情况下各有优势。触发器的主要好约束和触发器在特殊情况下各有优势。触发器的主要好处在于它们可以包含使用处在于它们可以包含使用 Transact-SQL 代码的复杂处理逻代码的复杂处理逻辑。因此，辑。因此，触发器可以支持约束的所有功能；但它在所给出触发器可以支持约束的所有功能；但它在所给出的功能上并不总是最好的方法。的功能上并不总是最好的方法。SQL Server帮助中建议：帮助中建议：假设功能可以满足应用程序的假设功能可以满足应用程序的功能需求，实体完整性要求应通过功能需求，实体完整性要求应通过Primary Key约束、约束、Unique约束等进行强制，用户定义完整性应

7、通过约束等进行强制，用户定义完整性应通过 CHECK 约束进行强制，参照完整性约束进行强制，参照完整性(RI)则应通过则应通过 FOREIGN KEY 约束进行强制。约束进行强制。数据库系统概论触发器的概述 在约束所支持的功能无法满足应用程序的功能要求在约束所支持的功能无法满足应用程序的功能要求时，触发器就极为有用。主要表现在以下几个方时，触发器就极为有用。主要表现在以下几个方面：面：CHECK 约束中逻辑表达式只能涉及表内约约束中逻辑表达式只能涉及表内约束。如果应用程序要求需要多表之间列值约束，束。如果应用程序要求需要多表之间列值约束，则必须使用触发器。则必须使用触发器。约束只能通过标准的系

8、统错误信息传递错约束只能通过标准的系统错误信息传递错误信息。如果应用程序要求使用（或能从中获益）误信息。如果应用程序要求使用（或能从中获益）自定义信息和较为复杂的错误处理，则必须使用自定义信息和较为复杂的错误处理，则必须使用触发器。触发器。数据库系统概论 触发器的概述 触发器可以禁止或回滚违反参照完整性的更触发器可以禁止或回滚违反参照完整性的更改，从而取消所尝试的数据更新。改，从而取消所尝试的数据更新。如果触发器表上存在约束，则在如果触发器表上存在约束，则在 INSTEAD OF 触发器执行后但在触发器执行后但在 AFTER 触发器执行前检查触发器执行前检查这些约束。如果约束破坏，则回滚这些约

9、束。如果约束破坏，则回滚 INSTEAD OF 触发器操作并且不执行触发器操作并且不执行 AFTER 触发器。触发器。数据库系统概论 触发器的概述4 触发器的类型触发器的类型 SQL2000提供两种类型的触发器：提供两种类型的触发器：Instead Of触发器和触发器和After触发器。这两种触发器的区别在于它们触发的时机以触发器。这两种触发器的区别在于它们触发的时机以及进行的操作不同。及进行的操作不同。AFTER 触发器：在触发器：在触发操作执行后和处理完任何约束触发操作执行后和处理完任何约束后后激发。可通过指定激发。可通过指定 AFTER 或或 FOR 关键字来请求关键字来请求 AFTER

10、 触发器。因为触发器。因为 FOR 关键字与关键字与 AFTER 的效果相同，所以具的效果相同，所以具有有 FOR 关键字的触发器也归类为关键字的触发器也归类为 AFTER 触发器。触发器。AFTER触发器只能作用于基本表。触发器只能作用于基本表。数据库系统概论 触发器的概述 INSTEAD OF 触发器：触发器：代替触发动作进行激发，代替触发动作进行激发，并在处理约束之前激发并在处理约束之前激发。除了基本表以外，除了基本表以外，INSTEAD OF触发器也可以作用于视图，主要用触发器也可以作用于视图，主要用来扩展视图支持的更新操作。来扩展视图支持的更新操作。对于每个触发操作（对于每个触发操作

11、（UPDATE、DELETE 和和 INSERT），每个表或视图只能有一个），每个表或视图只能有一个 INSTEAD OF 触发器，而一个表对于每个触发操作可以有多触发器，而一个表对于每个触发操作可以有多个个 AFTER 触发器。触发器。注意：注意：在含有级联删除或级联更新操作定义的外在含有级联删除或级联更新操作定义的外键所在的表上，不能定义键所在的表上，不能定义 INSTEAD OF DELETE 触发器和触发器和 INSTEAD OF UPDATE 触发器。触发器。数据库系统概论 触发器的概述5 Inserted表和表和Deleted表表 SQL Server为每个触发器语句都自动创建了两

12、种特殊的为每个触发器语句都自动创建了两种特殊的表：表：DELETED 表和表和 INSERTED 表表。这两个逻辑表的结构。这两个逻辑表的结构总是与触发器所作用的表的结构相同总是与触发器所作用的表的结构相同，但是它们存放于内存，但是它们存放于内存而不是数据库中。我们可以使用这两个临时的驻留内存的表而不是数据库中。我们可以使用这两个临时的驻留内存的表测试某些数据更新的效果及设置触发器操作的条件，然而测试某些数据更新的效果及设置触发器操作的条件，然而不不能直接对表中的数据进行更改能直接对表中的数据进行更改。触发器执行完成后，。触发器执行完成后，SQL Server自动删除与该触发器相应的自动删除与

13、该触发器相应的DELETED 表和表和 INSERTED表。表。数据库系统概论 触发器的概述 inserted 和和 deleted 表主要用于触发器中：表主要用于触发器中：扩展表间参照完整性。扩展表间参照完整性。在以视图为基础的基表中插入或更新数据。在以视图为基础的基表中插入或更新数据。检查错误并基于错误采取行动。检查错误并基于错误采取行动。找到数据更新前后表状态的差异，并基于此找到数据更新前后表状态的差异，并基于此差异采取行动。差异采取行动。数据库系统概论 触发器的概述 Deleted 表用于存储表用于存储 DELETE 和和 UPDATE 语句所语句所影响的行的影响的行的副副本。在执行本

14、。在执行 DELETE 或或 UPDATE 语句语句时，行从触发器表中删除，并传输到时，行从触发器表中删除，并传输到 deleted 表中。表中。Deleted 表和触发器表通常没有相同的行（但表和触发器表通常没有相同的行（但Instead Of触发器可能出现两个表中有相同的行的情况）。触发器可能出现两个表中有相同的行的情况）。数据库系统概论触发器的概述 Inserted 表用于存储表用于存储 INSERT 和和 UPDATE 语句所语句所影响的行的副本。在一个插入或更新事务处理中，影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到新建行被同时添加到 inserted 表和触发器表

15、中。表和触发器表中。Inserted 表中的行是触发器表中新行的副本。表中的行是触发器表中新行的副本。修改事务类似于在修改事务类似于在删除之后执行插入删除之后执行插入；首先旧；首先旧行被复制到行被复制到 deleted 表中，然后新行被复制到触发表中，然后新行被复制到触发器表和器表和 inserted 表中。表中。数据库系统概论触发器的概述SnoSnameSdept2010001HUA CS触发器表触发器表2010003 QIANG MA新元组新元组触发动作：插入触发动作：插入SnoSnameSdeptInserted表表2010003 QIANG MA数据库系统概论触发器的概述SnoSnam

16、eSdept 2010003QIANGMASnoSnameSdept DELETED表表触发器表触发器表2010001 HUA CS触发动作：删除触发动作：删除数据库系统概论触发器的概述SnoSnameSdept 2010003 QIANG MASnoSnameSdept SnoSnameSdept DELETED表表触发器表触发器表INSERTED表表2010001 HUA CS触发动作：修改触发动作：修改2010001 HUA CS2010002 HUA CS2010002 HUA CS数据库系统概论 触发器的创建1 创建之前应考虑的问题创建之前应考虑的问题2 创建触发器时需要确定的对象创

17、建触发器时需要确定的对象3 Create Trigger命令的语法以及参数意义说明命令的语法以及参数意义说明数据库系统概论 触发器的创建1 创建之前应考虑的问题创建之前应考虑的问题u 创建触发器的权限默认分配给表的所有者，且不能将该创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。权限转给其他用户。u 触发器为数据库对象，其名称必须遵循标识符的命名规触发器为数据库对象，其名称必须遵循标识符的命名规则。则。u 只能在当前数据库中创建触发器。只能在当前数据库中创建触发器。u 触发器可以引用临时表，但不能引用系统表。触发器可以引用临时表，但不能引用系统表。数据库系统概论 触发器的创建

18、2 创建触发器时需要确定的对象创建触发器时需要确定的对象u 触发器的名称。触发器的名称。u 定义触发器的表：即在那个表上创建触发器。定义触发器的表：即在那个表上创建触发器。u 触发器将何时激发：创建什么类型的触发器。触发器将何时激发：创建什么类型的触发器。u 激活触发器的数据更新语句。有效选项为激活触发器的数据更新语句。有效选项为 INSERT、UPDATE 或或 DELETE。多个数据更新语句可激活同一个触。多个数据更新语句可激活同一个触发器。例如，触发器可由发器。例如，触发器可由 INSERT 或或 UPDATE 语句激活。语句激活。u 执行触发操作的编程语句：触发器触发后要进行的操作执行

19、触发操作的编程语句：触发器触发后要进行的操作语句或要实现的功能语句。语句或要实现的功能语句。数据库系统概论 触发器的创建3 Create Trigger命令的语法以及参数意义说明命令的语法以及参数意义说明CREATE TRIGGER trigger_name ON table|view FOR|AFTER|INSTEAD OF INSERT ,UPDATE ,DELETE AS sql_statement_Block 数据库系统概论 触发器的创建3 Create Trigger命令的语法以及参数意义说明命令的语法以及参数意义说明AFTER：指定触发器只有在触发指定触发器只有在触发 SQL 语句

20、中指定的所有操作语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定须成功完成后，才能执行此触发器。如果仅指定 FOR 关键字，关键字，则则 AFTER 是默认设置。不能在视图上定义是默认设置。不能在视图上定义 AFTER 触发器。触发器。INSTEAD OF：指定执行触发器而不是执行触发：指定执行触发器而不是执行触发 SQL 语句，语句，从而替代触发语句的操作。从而替代触发语句的操作。在表或视图上，每个在表或视图上，每个 INSERT、UPDATE 或或 DELETE 语语句

21、最多可以定义一个句最多可以定义一个 INSTEAD OF 触发器。触发器。数据库系统概论 触发器的创建 DELETE,INSERT,UPDATE：指定在表或视指定在表或视图上执行哪些数据更新语句时将激活触发器。必须至少指定一图上执行哪些数据更新语句时将激活触发器。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。字。如果指定的选项多于一个，需用逗号分隔这些选项。sql_statement：触发器的条件和执行的操作。触发器条件触发器的条件和执行的操作。触发器条件指定其它准则，

22、以确定指定其它准则，以确定 DELETE、INSERT 或或 UPDATE 语句语句是否导致执行触发器操作。当尝试是否导致执行触发器操作。当尝试 DELETE、INSERT 或或 UPDATE 操作时，操作时，Transact-SQL语句中指定的触发器操作将语句中指定的触发器操作将生效。生效。数据库系统概论修改触发器使用使用Alter Trigger命令命令修改触发器修改触发器,语法格式如下：语法格式如下：ALTER TRIGGER trigger_name ON table|view FOR|AFTER|INSTEAD OF INSERT ,UPDATE ,DELETE AS sql_sta

23、tement_Block Alter Trigger命令的使用只是将命令的使用只是将Create Trigger命令中的命令中的Create关键字替换为关键字替换为Alter即可，其他参数含义与即可，其他参数含义与Create Trigger命令完全相同。命令完全相同。数据库系统概论触发器的应用-例子create trigger t_stuon Studentafter deleteasbegin delete from sc where Sno in(select Sno from deleted)end例：创建一个触发器例：创建一个触发器t-stu，实现学生与其选课信息的级，实现学生与其选

24、课信息的级联删除，即如果删除某学生的个人信息，同时也删除他联删除，即如果删除某学生的个人信息，同时也删除他的选课记录。的选课记录。数据库系统概论触发器的应用-例子首先创建优秀学生表首先创建优秀学生表CREATE TABLE Goodstu(Sname char(20),Cname CHAR(40),Grade SMALLINTPRIMARY KEY(Sname,Cname)在在SC表上创建一个触发器，一旦插入选课信息，就将表上创建一个触发器，一旦插入选课信息，就将分数大于分数大于90的加入优秀学生表。的加入优秀学生表。数据库系统概论触发器的应用-例 续CREATE TRIGGER GOOD_S

25、TUON SC AFTER INSERT AS BEGIN INSERT INTO Goodstu SELECT Sname,Cname,Grade FROM Student S,Course C,INSERTED I WHERE S.Sno=I.Sno AND I.Cno=C.Cno AND Grade90 END 数据库系统概论触发器的应用-例 续例题例题（触发器实现）（触发器实现）规定每个学生的选课数量不能超过规定每个学生的选课数量不能超过5门。门。数据库系统概论触发器的应用-例 续 在学生表中增加一列在学生表中增加一列SelCredit，表示该生选修的总，表示该生选修的总学分。学分。要

26、求要求：创建一个触发器：创建一个触发器CntCredit，实现业务规则。，实现业务规则。业务规则业务规则：如果向选课表插入一条选课记录，就在：如果向选课表插入一条选课记录，就在学生表中相应元组的学生表中相应元组的SelCredit属性上统计该生选属性上统计该生选修的总学分。修的总学分。数据库系统概论触发器的应用-例 续 综合思考例题：综合思考例题：假设有一个表假设有一个表SC_Cnt，包含属性，包含属性Cno、Cname、SelCnt，分表表示课程号、课程名、选修该课程人，分表表示课程号、课程名、选修该课程人数。数。要求要求：创建一个触发器创建一个触发器Insert_SC_Cnt,实现业务实现

27、业务规则。规则。业务规则业务规则：如果向选课表（：如果向选课表（SC）当中插入新的选课）当中插入新的选课记录，就将记录，就将SC_Cnt表中相应课程的选修人数加表中相应课程的选修人数加1数据库系统概论删除触发器v 删除触发器的删除触发器的SQL语法：语法：DROP TRIGGER;v 触发器必须是一个已经创建的触发器，并且只能由具有相触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。应权限的用户删除。例例 删除刚才建立的触发器删除刚才建立的触发器GOOD_STU DROP TRIGGER GOOD_STU;数据库系统概论 触发器的创建4 企业管理器创建触发器企业管理器创建触发

28、器 展开服务器组，然后展开服务器。展开服务器组，然后展开服务器。展开展开“数据库数据库”文件夹，展开含触发器的表所属的数据库，然后单击文件夹，展开含触发器的表所属的数据库，然后单击“表表”文件夹。文件夹。在详细信息窗格中，右击将在其上创建触发器的表，指向在详细信息窗格中，右击将在其上创建触发器的表，指向“所有任务所有任务”菜单，然后单击菜单，然后单击“管理触发器管理触发器”命令，打开命令，打开“触发器属性触发器属性”对话框。对话框。在在“名称名称”中，单击中，单击。在在“文本文本”框中输入触发器的文本。用框中输入触发器的文本。用 CTRL-TAB 键来缩进触发器键来缩进触发器的文本。的文本。若

29、要检查语法，单击若要检查语法，单击“检查语法检查语法”命令。命令。数据库系统概论 触发器的操作1 查看触发器查看触发器2 修改触发器操作修改触发器操作3 删除触发器操作删除触发器操作4 嵌套触发器嵌套触发器 参见企业管理器中创建触发器操作，打开参见企业管理器中创建触发器操作，打开“触发器属性触发器属性”对话对话框，然后根据需要选择框，然后根据需要选择“名称名称”组合框中相应触发器并查看其定义组合框中相应触发器并查看其定义等。等。触发器被删除时，触发器所在表中的数据不会因此改变。此外，触发器被删除时，触发器所在表中的数据不会因此改变。此外，如果某个表被删除时，该表上的所有的触发器也自动被删除。如

30、果某个表被删除时，该表上的所有的触发器也自动被删除。具体操作可在企业管理器中删除，也可使用具体操作可在企业管理器中删除，也可使用Drop Trigger命令。命令。数据库系统概论 触发器的操作2 修改触发器操作修改触发器操作如果需要修改触发器的定义和属性，有两种方法：如果需要修改触发器的定义和属性，有两种方法：先删除原有的触发器定义，然后再重新创建与之同名的触发器先删除原有的触发器定义，然后再重新创建与之同名的触发器 直接使用修改命令修改触发器的定义直接使用修改命令修改触发器的定义修改触发器的操作方法：修改触发器的操作方法：（1）企业管理器）企业管理器 参照企业管理器中创建触发器操作，打开参照

31、企业管理器中创建触发器操作，打开“触发器属性触发器属性”对话框，对话框，然后在然后在“名称名称”组合框中选择您要修改的触发器名称，此后直接在组合框中选择您要修改的触发器名称，此后直接在“文文本本”框中直接修改触发器的定义即可。框中直接修改触发器的定义即可。数据库系统概论 触发器的操作4 嵌套触发器嵌套触发器 如果一个触发器在执行操作时，引发了另外一个触发器，而这个触发如果一个触发器在执行操作时，引发了另外一个触发器，而这个触发器接着又引发了下一个触发器，依次类推，这些触发器就是嵌套触发器。器接着又引发了下一个触发器，依次类推，这些触发器就是嵌套触发器。触发器最多可以嵌套至触发器最多可以嵌套至3

32、2层，并且可以控制是否可以通过层，并且可以控制是否可以通过“嵌套触发器嵌套触发器”服务器配置选项进行触发器嵌套。服务器配置选项进行触发器嵌套。如果允许使用嵌套触发器，且链中的一个触发器开始一个无限循环，如果允许使用嵌套触发器，且链中的一个触发器开始一个无限循环，则超出嵌套级时触发器将终止。则超出嵌套级时触发器将终止。我们可以使用嵌套触发器执行一些有用的日常必须的琐碎工作，如保我们可以使用嵌套触发器执行一些有用的日常必须的琐碎工作，如保存前一触发器所影响行的一个备份等。存前一触发器所影响行的一个备份等。说明说明 由于触发器在事务中执行，如果在一系列嵌套触发器的任意层由于触发器在事务中执行，如果在

33、一系列嵌套触发器的任意层中发生错误，则整个事务都将取消，且所有的数据更新都将回滚。中发生错误，则整个事务都将取消，且所有的数据更新都将回滚。在触发在触发器中包含器中包含 PRINT 语句，用以确定错误发生的位置。语句，用以确定错误发生的位置。数据库系统概论第五章 数据库完整性小结小结数据库系统概论小结v 数据库的完整性是为了保证数据库中存储的数据的正确性、数据库的完整性是为了保证数据库中存储的数据的正确性、一致性与相容性。一致性与相容性。v 关系数据库的完整性约束包括实体完整性、参照完整性和关系数据库的完整性约束包括实体完整性、参照完整性和用户定义的完整性。用户定义的完整性。v 为了保证数据的

34、完整性，为了保证数据的完整性，DBMS提供了定义约束、检查约提供了定义约束、检查约束和违约处理机制。束和违约处理机制。v 实体完整性和参照完整性分别是对主码和外码取值的约束。实体完整性和参照完整性分别是对主码和外码取值的约束。违反实体完整性约束的数据更新将被拒绝。然而，对于违违反实体完整性约束的数据更新将被拒绝。然而，对于违反参照完整性约束的更新，存在多种可能的处理方案。反参照完整性约束的更新，存在多种可能的处理方案。v SQL支持实体完整性和参照完整性支持实体完整性和参照完整性，可以在定义基本表时，可以在定义基本表时说明主码和外码，并说明违反参照完整性的处理措施。说明主码和外码，并说明违反参照完整性的处理措施。数据库系统概论小结(续)v对于用户定义的约束，属性约束和元组约束可以对于用户定义的约束，属性约束和元组约束可以在定义基本在定义基本表时说明。表时说明。v触发器定义某个事件发生并且满足相应条件自动触发器定义某个事件发生并且满足相应条件自动执行的动作。触发器有多种用途，如实现商务规执行的动作。触发器有多种用途，如实现商务规则、审计日志，甚至数据库以外的操作。虽然直则、审计日志，甚至数据库以外的操作。虽然直到到SQL-99标准才引入触发器，但是许多标准才引入触发器，但是许多DBMS在在此之前已经提供对触发器的支持。此之前已经提供对触发器的支持。