SQL_server_2005触发器操作

1、 SQL Server 2005 实用教程 电子工业出版社出版第第9章章触发器操作触发器操作3触发器综述触发器综述1、触发器概念、触发器概念触发器是触发器是一种特殊类型的存储过程一种特殊类型的存储过程，不由用户直接调用，而且可以包，不由用户直接调用，而且可以包含复杂的含复杂的 SQL语句。它们主要用于强制复杂的业务规则或要求。触发语句。它们主要用于强制复杂的业务规则或要求。触发器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。可以完成存储过程能完成的功能，但是它具留表之间已定义的关系。可以完成存储过程能

2、完成的功能，但是它具有自己显著的的特点：有自己显著的的特点： 1）它与表紧密相连，可以看作表定义的一部分；）它与表紧密相连，可以看作表定义的一部分； 2）它不能通过名称被直接调用，更不允许带参数，而是当用户对）它不能通过名称被直接调用，更不允许带参数，而是当用户对表中的数据进行修改时，自动执行；表中的数据进行修改时，自动执行； 3）它可以用于）它可以用于SQL Server约束、默认值和规则的完整性检查，实约束、默认值和规则的完整性检查，实施更为复杂的数据完整性约束。施更为复杂的数据完整性约束。411.7 触发器综述触发器综述2、触发器的优点 触发器包含复杂的处理逻辑，能够实现复杂的数据完整性

3、约束。同其他约束相比，它主要有以下优点： 1）触发器自动执行 在对表的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。 2）触发器能够对数据库中的相关表实现级联更改 触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。例如，在学生数据库中，可以在产品表的产品编号字段上建立一个插入触发器，当对产品表增加记录时，在产品销售表的产品编号上自动插入编号值。 511.7 触发器综述触发器综述2、触发器的优点 3）触发器可以实现比CHECK约束更为复杂的数据完整性约束在数据库中为了实现数据完整性约束，可以使用CHECK约束或触发器。CHECK约束不允许

4、引用其它表中的列来完成检查工作，而触发器可以引用其它表中的列。例如，在STUDENT数据库中，向学生表中插入记录时，当输入系部代码时，必须先检查系部表中是否存在该系。这只能通过触发器实现，而不能通过CHECK约束完成。 4）触发器可以评估数据修改前后的表状态，并根据其差异采取对策。 5）一个表中可以存在多个同类触发器（INSERT、UPDATE或DELETE），对于同一个修改语句可以有多个不同的对策以响应。611.7 触发器综述触发器综述3、触发器的种类 SQL Server 2005按触发被被激活的时机可以分为两种类型：AFTER 触发器和 INSTEAD OF触发器。 AFTER触发器又称

5、为后触发器，该类触发器是在引起触发器执行的修改语句成功完成之后执行。如果修改语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。此类触发器只能定义在表上，不能创建在视图上。可以为每个触发操作（INSERT、UPDATE或DELETE）创建多个AFTER触发器。 INSTEAD OF触发器又称为替代触发器，当引起触发器执行的修改语句停止执行时，该类触发器代替触发操作执行。该类触发器既可在表上定义，也可在视图上定义。对于每个触发操作（INSERT、UPDATE和DELETE）只能定义一个INSTEAD OF触发器。 711.8触发器的创建执行综述综述在进行触发器的基本操作之前，介绍两张特殊

6、的临时表，分别是inserted表和deleted表。这两张表都存在于高速缓存中。用户可以使用这两张临时表来检测某些修改操作所产生的效果。例如，可以使用SELECT 语句来检查INSERT和UPDATE语句执行的插入操作是否成功，触发器是否被这些语句触发等。但是不允许用户直接修改inserted表和deleted表中数据。811.8触发器的创建执行 deleted表中存储着被DELETE和UPDATE语句影响的旧数据行。在执行DELETE和UPDATE语句过程中，指定的数据行被用户从基本表中删除，然后转移到了delete表中。一般来说，在基本表中delete表中不会存在有相同的数据行。 ins

7、erted表中存储着被INSERT 和UPDATE语句影响的新的数据行。当用户执行INSERT 和UPDATE语句时，新的数据行被添加到insert表中，同时这些数据行的备份被复制到inserted临时表中。 一个典型的UPDATE事务实际上是由两个操作组成。首先，旧的数据行从基本表中转移到delete表中，前提是这个过程没有出错；紧接着将新的数据行同时插入基本表和insert表。911.8触发器的创建执行1.触发器的基本操作_创建在创建触发器前，必须注意以下几点：lCREATE TRIGGER 语句必须是批处理中的第一条语句。l只能在当前数据库中创建触发器，名称必须遵循标识符的命名规则。l表

8、的所有者具有创建触发器的默认权限，不能将该权限转给其他用户。l不能在临时表或系统表上创建触发器，但是触发器可以引用临时表，但是不能引用系统表。l尽管TRUNCATE TABLE 语句类似于没有WHERE子句（用于删除行）的DELETE语句，但由于该语句不被记入日志，所以它不会引发DELETE触发器。lWRITETEXT语句不会引发INSERT或UPDATE触发器。l在创建触发器时，必须指明在哪一个表上定义触发器以及触发器的名称、激发时机、激活触发器的修改语句（INSERT、UPDATE或DELETE）。1011.8触发器的创建执行1.触发器的基本操作_创建使用SQL语句创建触发器语法格式为：

9、CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , DELETE, 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_statement .n

10、1111.8触发器的创建执行1.触发器的基本操作_创建其中： trigger_name：触发器名称，其必须符合命名标识规则，并且在当前数据库中唯一。 table | view：被定义触发器的表或视图。 WITH ENCRYPTION：对syscomments 表中含 CREATE TRIGGER 语句文本进行加密。 AFTER ：默认的触发器类型，后触发器。此类型触发器不能在视图上定义。 INSTEAD OF：表示建立替代类型的触发器。 NOT FOR REPLICATION：表示当复制进程更改触发器所涉及的表时，不应执行该触发器。 IF UPDATE：指定对表中字段进行增加或修改内容时起作用

11、，不能用于删除操作。 sql_statement：定义触发器被触发后，将执行的SQL语句。1211.8触发器的创建执行1.触发器的基本操作_创建例：在student数据库中，为“班级”表建立一个名为del_banji的DELETE触发器，其作用是当删除“班级”表中的记录时，检查“学生”表中是否存在该班级的学生，如果存在则提示不允许删除该班级的信息。 1311.8触发器的创建执行USE studentGOIF EXISTS (SELECT name FROM sysobjects WHERE name = del_banji AND type = TR) DROP TRIGGER del_ban

12、jiGO CREATE TRIGGER del_banji ON 班级FOR DELETEAS DECLARE banjidaima char(9)SELECT banjidaima=班级代码 FROM DeletedIF EXISTS (SELECT * FROM 学生 WHERE 班级代码=banjidaima)BEGINPRINT 班级正在使用，不能被删除！ROLLBACK TRANSACTION ENDGO1411.8触发器的创建执行2.触发器的基本操作_执行案例：删除班级表中的班级代码为“060101001”的“06级软件工程001班”，观察触发器del_banji的作用。代码如下：

13、USE studentGODELETE 班级 WHERE 班级代码=060101001GO 15在SQL Server Management Studio中创建触发器的操作步骤为：（1）启动SQL Server Management Studio，在“对象资源管理器”窗口中，依次展开数据库、student、表节点。（2）在表节点中，展开需要建立触发器的表（如班级），右键单击触发器，在弹出的快捷菜单中选择“新建触发器”命令。（3）单击“新建触发器”命令，打开“创建触发器模板”，在模板中输入触发器创建文本。（4）单击工具栏上的“执行”按钮，完成触发器的创建。11.8触发器的创建执行1611.8触发

14、器的创建执行3.查看触发器信息 1)使用系统存储过程查看触发器信息 使用系统存储过程sp_helptrigger查看班级表上存在的触发器的信息。代码如下：USE STUDENTGOEXEC sp_helptrigger 班级GO 2)使用SQL Server Management Studio查看触发器信息 1711.9修改和删除触发器1.修改1）使用系统存储过程修改触发器名称对触发器进行重命名，可以使用系统存储过程sp_rename来完成，其语法格式如下：EXECUTE sp_rename 触发器原名,触发器新名1811.9修改和删除触发器2）使用T-SQL语句修改触发器定义修改触发器的定义

15、，可以使用ALTER TRIGGER语句。ALTER TRIGGER语句与CREATE TRIGGER语句的语法相似，只是语句的第一个关键字不同 .1911.9修改和删除触发器2.触发器的基本操作_禁止和启动 针对某个表创建的触发器，可以根据需要，禁止或启用其执行。禁止触发器或启用触发器执行只能在查询分析器中进行，其语法格式为： ALTER TABLE 表名 ENABLE |DISABLE 触发器名称其中： ENABLE：该选项为启用触发器 DISABLE：该选项为禁用触发器2011.9修改和删除触发器3.触发器的基本操作_删除 当不再需要某个触发器时，可以将其删除。只有触发器的所有者才有权删

16、除触发器。可以使用下面的方法将触发器删除： 1）可以使用SQL Server Management Studio 2）使用SQL语句删除触发器 删除一个或多个触发器，可以使用DROP TRIGGER语句，语法如下： DROP TRIGGER 触发器名称 ,.n 3）删除表同时删除触发器 当某个表被删除后，该表上的所有触发器将同时被删除，但是删除触发器不会对表中数据有影响。2111.10嵌套触发器嵌套触发器在触发器中可以包含影响另外一个表的INSERT、UPDATE或者DELETE语句。这就是嵌套触发器，具体来说就是，如果表A上的触发器在执行时引发了表B上的触发器，而表B上的触发器又激活了表C上

17、的触发器，表C上的触发器又激活了表D上的触发器，所有触发器依次触发。这些触发器不会形成无限循环，SQL Server规定触发器最多可嵌套至 32 层。如果允许使用嵌套触发器，且链中的一个触发器开始一个无限循环，如果超出嵌套级，触发器将被终止执行。正确的使用嵌套触发器，可以执行一些有用的日常工作，但是嵌套触发器比较复杂，使用时要注意技巧，比如，由于触发器在事务中执行，如果在一系列嵌套触发器的任意层中发生错误，则整个事务都将取消，且所有的数据修改都将回滚。一般情况下，在触发器中包含PRINT语句，用以确定错误发生的位置。2211.10嵌套触发器嵌套触发器设置是否嵌套的方法：使用系统存储过程改变嵌套

18、使用sp_config系统存储过程设置是否允许嵌套的语法格式如下：EXEC sp_configure nested trigger,0|1其中，如果设置为0，则允许嵌套，设置为1，禁止嵌套 2311.11触发器案例触发器案例1.在在student数据库中建立一个名为数据库中建立一个名为insert_xibu的的INSERT触发器，存触发器，存储在储在“专业专业”表中。当用户向表中。当用户向“专业专业”表中插入记录时，如果插入了表中插入记录时，如果插入了在在“系部系部”表中没有的系部代码，则提示用户不能插入记录，否则提表中没有的系部代码，则提示用户不能插入记录，否则提示记录插入成功。示记录插入成

19、功。USE studentGOIF EXISTS (SELECT name FROM sysobjects WHERE name = insert_xibu AND type = TR) DROP TRIGGER insert_xibu GO2411.11触发器案例触发器案例CREATE TRIGGER insert_xibu ON dbo.专业 FOR INSERTASDECLARE XIBU CHAR(2) SELECT XIBU=系部.系部代码 FROM 系部, inserted WHERE 系部.系部代码 =inserted.系部代码IF XIBU PRINT(记录插入成功)ELSEB

20、EGIN PRINT (系部代码不存在系部表中，不能插入记录，插入将终止！) ROLLBACK TRANSACTIONENDGO2511.11触发器案例触发器案例在student数据库中建立一个名为delete_zhye的DELETE触发器，存储在“专业”表中。当用户删除“专业”表中的记录时，如果“班级”表引用了此记录的专业代码，则提示用户不能删除记录，否则提示记录已删除。USE studentGOIF EXISTS (SELECT name FROM sysobjects WHERE name = delete_zhye AND type = TR) DROP TRIGGER delete_

21、zhye GO2611.11触发器案例触发器案例CREATE TRIGGER delete_zhyeON 专业 FOR DELETEAS IF(SELECT COUNT(*) FROM 班级 INNER JOIN DELETED ON 班级.专业代码=DELETED.专业代码)0BEGIN PRINT (该专业被班级表所引用，你不可以删除此条记录，删除将终止) ROLLBACK TRANSACTIONENDELSE PRINT 记录已删除GO2711.11触发器案例触发器案例在student数据库中建立一个名为update_zymc的UPDATE触发器，存储在“专业”表中。当用户更新“专业”表中的专业名称时，提示用户不能修改专业名称。USE studentGOIF EXISTS (SELECT name FROM sysobjects WHERE name = update_zymc AND type =