第09讲chp5_(补充)触发器_new

1、触发器触发器（参见第（参见第5 5版教材版教材P168P168第五章第五章5.75.7注意区别）注意区别）SQL Server中常用的触发器分为两种：中常用的触发器分为两种： DML触发器触发器 DDL触发器触发器DML触发器概述触发器概述 当对某一表进行如当对某一表进行如UPDATE，INSERT，DELETE这些操作时，这些操作时，SQL Server可以自动执行相关触发器可以自动执行相关触发器所定义的所定义的SQL语句。从而确保对数据的处理必须符语句。从而确保对数据的处理必须符合这些合这些SQL语句定义的规则。语句定义的规则。 触发器的触发器的主要作用主要作用就是：实现由主键和外键所就是

2、：实现由主键和外键所不能保证的参照完整性和数据的一致性。不能保证的参照完整性和数据的一致性。SQL Server 常用的常用的DML触发器有触发器有两种两种：AFTER 触发器触发器 和和 INSTEAD OF 触发器。触发器。（指定（指定 FOR 与与 指定指定 AFTER相同相同 ） AFTER触发器触发器就是在某一语句（就是在某一语句（INSERT、UPDATE、DELETE）执行执行之后之后激活触发器，且只激活触发器，且只能在能在表表上定义。上定义。 INSTEAD OF 触发器触发器就是在某一语句（就是在某一语句（INSERT、UPDATE、DELETE）执行执行之前之前激活触发器，

3、但激活触发器，但并并不执行不执行该语句（该语句（INSERT、UPDATE、DELETE），），而而仅执行仅执行触发器本身。触发器本身。 INSTEAD OF 触发器既可以在触发器既可以在表表上定义，也可以上定义，也可以在在视图视图上定义。上定义。 使用使用Transac_SQL来创建触发器来创建触发器 触发器可以在特定的表上修改数据时触发器可以在特定的表上修改数据时自动自动执行。执行。 SQL Server允许允许 对给定的对给定的INSERT、UPDATE 或或 DELETE 语句语句 创建多个触发器。创建多个触发器。 创建触发器的语法简单表示如下：创建触发器的语法简单表示如下：CREAT

4、E TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n sql_statement .n 其中各个参数的意义为：其中各个参数的意义为：trigger_name触发器的名称。触发器的名称。触发器名称必须符合标识符的规则。触发器名称必须符合标识符的规则。触发器在当前数据库中创建。触发器在当前数据库中创建。只有表的所有者才可以在该表上创建一个

5、触发器。只有表的所有者才可以在该表上创建一个触发器。Table | view指定激活触发器的表或视图，它有时也称为触发表指定激活触发器的表或视图，它有时也称为触发表或触发视图。或触发视图。WITH ENCRYPTION加密加密syscomments中包含中包含CREATE TRIGGER语句的语句的相应文本。相应文本。AFTER指定只有当触发器定义中的所有操作都成功执行后，指定只有当触发器定义中的所有操作都成功执行后，触发器才会执行。触发器才会执行。INSTEAD OF创建一个创建一个INSTEAD OF触发器。触发器。可以为表或视图中的每一个可以为表或视图中的每一个INSERT、UPDATE

6、或或DELETE语句都指定一个语句都指定一个INSTEAD OF触发器。触发器。 如果一个可更新的如果一个可更新的视图视图定义时使用了定义时使用了WITH CHECK OPTION 选项，则选项，则 INSTEAD OF 触发器触发器不允许不允许在这个视图上定义，否则产生错误。在这个视图上定义，否则产生错误。 DELETE , INSERT , UPDATE 指定触发表上的哪些数据修改指定触发表上的哪些数据修改语句语句激活该触发器。激活该触发器。 这其中必须这其中必须至少至少指定指定一个一个选项。选项。 如果指定了如果指定了多个选项多个选项，必须用逗号隔开。，必须用逗号隔开。sql_state

7、ment指定指定触发条件触发条件和和触发程序动作触发程序动作。 触发条件的触发条件的IF子句中，如果有多个触发动作，子句中，如果有多个触发动作，应放在应放在BEGIN和和END之间。之间。 触 发 器 可 以 包 含触 发 器 可 以 包 含 任 何 数 量任 何 数 量 和和 任 何 种 类任 何 种 类 的的Transact-SQL语句，触发器是用来基于数据修改语语句，触发器是用来基于数据修改语句检查或改变数据的，它句检查或改变数据的，它不应该返回不应该返回数据给用户。数据给用户。 使用使用UPDATE触发器触发器修改触发器在使用修改触发器在使用update命令修改表时被触发。命令修改表时

8、被触发。例例7.8 对对student表创建表创建UPDATE触发器。触发器。CREATE TRIGGER stu_upt ON studentAFTER updateAS print update has been done successfully当执行以下的更新语句时，当执行以下的更新语句时，UPDATE student SET sage=17 WHERE sname=张三张三触发器被触发，输出如下信息：触发器被触发，输出如下信息：update has been done successfully使用使用DELETE触发器触发器 DELETE触发器通常用于两种情况：触发器通常用于两种情况

9、：1、会引起数据一致性问题的记录的删除。、会引起数据一致性问题的记录的删除。2、执行可删除主记录的子记录的级联删除操作。、执行可删除主记录的子记录的级联删除操作。 例例7.9 在在student表上定义删除型触发器，首先检查表上定义删除型触发器，首先检查要删除几行，若删除多行则返回错误信息。要删除几行，若删除多行则返回错误信息。其中，函数其中，函数 raiserror( ) 返回用户定义的错误信息。返回用户定义的错误信息。格式为：格式为： Raiserror(错误信息错误信息,严重级别严重级别,调用状态调用状态 ,替代参数替代参数) 字符串，字符串， 0-18, 1-127Raiserror(

10、错误信息错误信息,严重级别严重级别,调用状态调用状态 ,替代参数替代参数) 字符串，字符串， 0-18, 1-127l 任何用户都可以指定任何用户都可以指定 0 到到 18 之间的严重级别。之间的严重级别。只有只有 sysadmin 固定服务器角色成员或具有固定服务器角色成员或具有 ALTER TRACE 权权限的用户才能指定限的用户才能指定 19 到到 25 之间的严重级别，必须选择之间的严重级别，必须选择 WITH LOG 选项。选项。20 到到 25 之间的严重级别被认为是致命之间的严重级别被认为是致命的。的。 l 小于小于 0 的严重级别被解释为级别为的严重级别被解释为级别为 0。大于

11、。大于 25 的严重的严重级别被解释为级别为级别被解释为级别为 25。 l 如果在多个位置引发相同的用户定义错误，则针对每如果在多个位置引发相同的用户定义错误，则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。个位置使用唯一的状态号有助于找到引发错误的代码段。CREATE TRIGGER stu_infoON student AFTER deleteASIF (rowcount 1) beginrollback transactionraiserror (you can only delete one information at one time,16,1)end 当删除多行时，触发

12、器被触发执行。当删除多行时，触发器被触发执行。DELETE FROM student WHERE Sdept=CS当删除一行时当删除一行时:DELETE FROM student WHERE sno=200915130该记录被成功删除。该记录被成功删除。触发器原理触发器原理 每个触发器有两个特殊的表：每个触发器有两个特殊的表： inserted 表和表和deleted 表。表。 这两个表是由系统管理的，动态驻留在内存中，不这两个表是由系统管理的，动态驻留在内存中，不是存储在数据库中。是存储在数据库中。 inserted 表和表和deleted 表表只能只能被创建它们的触发器引被创建它们的触发器

13、引用。用。 这两个表的结构与这两个表的结构与被该触发器作用的表被该触发器作用的表结构相同。结构相同。 这两个表主要保存因用户操作而被影响到的这两个表主要保存因用户操作而被影响到的原原数据数据值或值或新新数据值。数据值。 这两个表是只读的。这两个表是只读的。1. inserted表表 当一个记录添加到表中时，相应的触发器创建一个当一个记录添加到表中时，相应的触发器创建一个inserted表，它与该触发器相连接的表的结构相同。表，它与该触发器相连接的表的结构相同。inserted表中保存因用户操作而被影响到表中保存因用户操作而被影响到新新数据值。数据值。 使用使用INSERT触发器触发器 INSE

14、RT触发器通常被用来触发器通常被用来监控监控字段中的字段中的数据数据满满足要求的标准，以确保数据完整性。足要求的标准，以确保数据完整性。例例7.7 对表对表student创建创建insert触发器，检查新添加的触发器，检查新添加的数据。如果年龄小于数据。如果年龄小于10岁，则不允许添加，并显示岁，则不允许添加，并显示错误提示。错误提示。CREATE TRIGGER stu_ins ON studentAFTER insertAS if exists(select * from inserted where sage10)beginrollback transactionprint 注意：学生年

15、龄不能小于十岁。注意：学生年龄不能小于十岁。end 当执行下列语句时：当执行下列语句时：insert into student(sno,sage)values(200915132,9)系统系统返回消息：返回消息：注意：学生年龄不能小于十岁。注意：学生年龄不能小于十岁。事务在触发器中结束。批处理已中止。事务在触发器中结束。批处理已中止。2 . deleted表表 Deleted表也映射与该触发器相连接的表结构。当表也映射与该触发器相连接的表结构。当执行一条执行一条DELETE语句时，相应的触发器创建一个，语句时，相应的触发器创建一个，从表中删除的每一行都包含在删除触发器内的从表中删除的每一行都包

16、含在删除触发器内的deleted表中。表中。 Deleted表保存因用户操作而被影响到的表保存因用户操作而被影响到的原原数据值。数据值。 先创建一个新表，与先创建一个新表，与student结构相同：结构相同：Select top 0 * into schange from student在在student表上创建一个表上创建一个delete触发器：触发器：create TRIGGER stu_ins2 ON studentAFTER deleteASinsert into schangeselect * from deletedgo然后执行语句：然后执行语句：Delete from stude

17、nt where sname=李勇李勇 被被UPDATE 语句触发的触发器创建两个表：语句触发的触发器创建两个表：inserted表和表和deleted表。这两个表和它们相连接的表表。这两个表和它们相连接的表有相同的列结构。有相同的列结构。 deleted表和表和inserted表分别包含相连接表中数据表分别包含相连接表中数据的的“前后前后”快照。快照。 例如，在例如，在student表上创建一个表上创建一个update触发器：触发器：create TRIGGER stu_upt2 ON studentAFTER updateASselect * from deletedselect * fr

18、om insertedgo 然后，用户执行语句：然后，用户执行语句：UPDATE student SET sage=18 WHERE sage=19 当语句执行时，当语句执行时，student表中的表中的Update触发器被触触发器被触发。发。 Deleted表中的数据是执行表中的数据是执行UPDATE语句之前表中语句之前表中的数据值（原值的数据值（原值19）；）； inserte表中的数据则是执行表中的数据则是执行UPDATE语句后表中的语句后表中的数据值（新值数据值（新值18）。）。 根据语句的根据语句的操作类型操作类型的不同，执行触发器时会的不同，执行触发器时会创建创建一个或者两个一个或

19、者两个临时表临时表(inserted表和表和deleted表表) 。下表说明了在进行何种查询时，触发器创建哪些表。下表说明了在进行何种查询时，触发器创建哪些表。触发器类型触发器类型 创建创建inserted 表表 创建创建deleted 表表INSERT 是是 否否UPDATE 是是 是是DELETE 否否 是是创建触发器应该考虑以下几个问题：创建触发器应该考虑以下几个问题：CREATE TRIGGER 语句必须是批处理中的第语句必须是批处理中的第一个语句。一个语句。创建触发器的创建触发器的权限权限默认分配给表的默认分配给表的所有者所有者，且，且不能将该权限转给其他用户。不能将该权限转给其他用

20、户。 触发器为数据库对象，其名称必须遵循标识符触发器为数据库对象，其名称必须遵循标识符的的命名规则命名规则。 虽然触发器可以引用当前数据库以外的对象，虽然触发器可以引用当前数据库以外的对象，但只能在但只能在当前数据库当前数据库中创建触发器。中创建触发器。 虽然不能在临时表或系统表上创建触发器，但虽然不能在临时表或系统表上创建触发器，但是触发器可以是触发器可以引用临时表引用临时表。在含有用在含有用DELETE或或UPDATE操作定义的外键操作定义的外键的表中，不能定义的表中，不能定义INSTEAD OF DELETE和和 INSTEAD OF UPDATE触发器。触发器。虽然虽然TRUNCATE

21、 TABLE语句类似于没有语句类似于没有WHERE子句（用于删除行）的子句（用于删除行）的 DELETE 语句，语句，但它但它并不会并不会引发引发 DELETE 触发器。触发器。当创建一个触发器时必须指定触发器当创建一个触发器时必须指定触发器名称名称，在，在哪一个哪一个表表上定义触发器，触发器将上定义触发器，触发器将何时何时激发，激发，激活触发器的数据修改激活触发器的数据修改语句语句。触发器的管理触发器的管理1. 查看触发器查看触发器使用系统存储过程查看触发器。使用系统存储过程查看触发器。 sp_help：用于查看触发器的一般信息，如触发器用于查看触发器的一般信息，如触发器的名称、属性、类型和

22、创建时间。的名称、属性、类型和创建时间。 例如例如: sp_help stu_ins sp_helptext：用于查看触发器的正文信息。用于查看触发器的正文信息。 例如例如: sp_helptext stu_ins2. 修改触发器修改触发器 （语法格式与（语法格式与create trigger 相似）相似）ALTER TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS IF UPDATE ( column ) AND | OR UPDA

23、TE ( column ) .n sql_statement .n 例如，修改触发器，使用加密：例如，修改触发器，使用加密：ALTER TRIGGER stu_ins ON studentWITH ENCRYPTION AFTER insertAS if exists(select * from inserted where sage10)beginrollback transactionprint 注意：学生年龄不能小于十岁。注意：学生年龄不能小于十岁。end 然后查看内容：然后查看内容：sp_helptext stu_ins单击此处编辑母版标题样式单击此处编辑母版标题样式 单击此处编辑母版

24、副标题样式单击此处编辑母版副标题样式禁用禁用/启用触发器启用触发器 当用户想暂停触发器的使用，但又不想删除它，当用户想暂停触发器的使用，但又不想删除它，可以禁用触发器，当需要时可以再次启用。可以禁用触发器，当需要时可以再次启用。1. 使用使用DISABLE TRIGGER命令禁用触发器命令禁用触发器语法格式：语法格式：DISABLE TRIGGER schema_name trigger_name ,.n | ALL ON object_name 例例: 禁用禁用student表上的表上的 stu_ins 触发器触发器DISABLE TRIGGER stu_ins ON student单击此处编辑母版标题样式单击此处编辑母版标题样式 单击此处编辑母版副标题样式单击此处编辑母版副标题样式2. 使用使用ENABLE TRIGGER命令启用触发器命令启用触发器语法格式语法格式 ：ENABLE TRIGGER schema_name trigger_name ,.n | ALL ON object_name 例例: 启用启用student表上的表上的 stu_ins 触发器触发器ENABLE TRIGGER stu_ins ON student禁用禁用/启用触发器启用触发器删除触发器删除触发器可以使用管理器可以使