触发器 整理的文档

1、SQLSQL ServerServer 20052005 学习笔记之触发器简介学习笔记之触发器简介11. .1 触发器简介触发器实际上就是一种特殊类型的存储过程，它是在执行某些特定的 T-SQL 语句时自动执行的一种存储过程。在 SQL Server 2005 中，根据 SQL 语句的不 同，把触发器分为两类：一类是 DML 触发器，一类是 DLL 触发器。11. .1. .1 触发器的概念和作用在 SQL Server 2005 里，可以用两种方法来保证数据的有效性和完整性： 约束（check）和触发器（Trigger）。约束是直接设置于数据表内，只能现实 一些比较简 单的功能操作，如：实现字段有效性和唯一性的检查、自动填入默 认值、确保字段数据不重复（即主键）、确保数据表对应的完整性（即外键） 等功能。触发器是针对数据表（库）的特殊的存储过程，当这个表发生了 Insert、Update 或 Delete 操作时，会自动激活执行的，可以处理各种复杂的 操作。在 SQL Server 2005 中，触发器有了更进一步的功能，在数据表（库） 发生 Create、Alter 和 Drop

2、操作时，也会自动激活执行。触发器常用的一些功能如下：l 完成比约束更复杂的数据约束：触发器可以实现比约束更为复杂的 数据约束l 检查所做的 SQL 是否允许：触发器可以检查 SQL 所做的操作是否被 允许。例如：在产品库存表里，如果要删除一条产品记录，在删除记录 时，触发器可以检查该产品库存数量是否为零，如果不为零则取消该删 除操作。l 修改其它数据表里的数据：当一个 SQL 语句对数据表进行操作的时 候，触发器可以根据该 SQL 语句的操作情况来对另一个数据表进行操作。 例如：一个订单取消的时候，那么触发器可以自动修改产品库存表，在 订购量的字段上减去被取消订单的订购数量。l 调用更多的存储过程：约束的本身是不能调用存储过程的，但是触 发器本身就是一种存储过程，而存储过程是可以嵌套使用的，所以触发 器也可以调用一个或多过存储过程。l 发送 SQL Mail：在 SQL 语句执行完之后，触发器可以判断更改过的 记录是否达到一定条件，如果达到这个条件的话，触发器可以自动调用 SQL Mail 来发送邮件。例如：当一个订单交费之后，可以物流人员发 送 Email，通知他尽快发货。l 返回自

3、定义的错误信息：约束是不能返回信息的，而触发器可以。 例如插入一条重复记录时，可以返回一个具体的友好的错误信息给前台 应用程序。l 更改原本要操作的 SQL 语句：触发器可以修改原本要操作的 SQL 语 句，例如原本的 SQL 语句是要删除数据表里的记录，但该数据表里的记 录是最要记录，不允许删除的，那么触发器可以不执行该语句。l 防止数据表构结更改或数据表被删除：为了保护已经建好的数据表， 触发器可以在接收到 Drop 和 Alter 开头的 SQL 语句里，不进行对数据 表的操作。11. .1. .2 触发器的种类在 SQL Server 2005 中，触发器可以分为两大类：DML 触发器和 DDL 触发器l DML 触发器：DML 触发器是当数据库服务器中发生数据操作语言 （Data Manipulation Language）事件时执行的存储过程。DML 触发器 又分为两类：After 触发器和 Instead Of 触发器l DDL 触发器：DDL 触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。DDL 触发器一般用于执行数

4、据库中 管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。11. .2 DML 触发器的分类SQL Server 2005 的 DML 触发器分为两类： l After 触发器：这类触发器是在记录已经改变完之后（after），才 会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误， 也可以用 Rollback Transaction 语句来回滚本次的操作。l Instead Of 触发器：这类触发器一般是用来取代原本的操作，在记 录变更之前发生的，它并不去执行原来 SQL 语句里的操作 （Insert、Update、Delete），而去执行触发器本身所定义的操作。11. .3 DML 触发器的工作原理在 SQL Server 2005 里，为每个 DML 触发器都定义了两个特殊的表，一个 是插入表，一个是删除表。这两个表是建在数据库服务器的内存中的，是由系 统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户 只有读取的权限，没有修改的权限。这两个表的结构与触发器所在数据表的结构是完全一致的，当触发器的工 作完成之后，这两个表也将会从内存中删除。插

5、入表里存放的是更新前的记录：对于插入记录操作来说，插入表里存放 的是要插入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。删除表里存放的是更新后的记录：对于更新记录操作来说，删除表里存放 的是更新前的记录（更新完后即被删除）；对于删除记录操作来说，删除表里 存入的是被删除的旧记录。下面看一下触发器的工作原理。11. .3. .1 After 触发器的工作原理After 触发器是在记录更变完之后才被激活执行的。以删除记录为 例：当 SQL Server 接收到一个要执行删除操作的 SQL 语句时，SQL Server 先将要删除 的记录存放在删除表里，然后把数据表里的记录删除，再激活 After 触发器， 执行 After 触发器里的 SQL 语句。执行完毕之后， 删除内存中的删除表，退出 整个操作。还是举上面的例子：在产品库存表里，如果要删除一条产品记录，在删除 记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消删除操 作。看一下数据库是怎么操作的：（1）接收 SQL 语句，将要从产品库存表里删除的产品记录取出来，放在删 除表里。（2）从产品库存表里删除该产品记

6、录。（3）从删除表里读出该产品的库存数量字段，判断是不是为零，如果为零 的话，完成操作，从内存里清除删除表；如果不为零的话，用 Rollback Transaction 语句来回滚操作。11. .3. .2 Instead Of 触发器的工作原理Instead Of 触发器与 After 触发器不同。After 触发器是在 Insert、Update 和 Delete 操作完成后才激活的，而 Instead Of 触发器，是在 这些操作进行之前就激活了，并且不再去执行原来的 SQL 操作，而去运行触发 器本身的 SQL 语句。11. .4 设计 DML 触发器的注意事项及技巧在了解触发器的种类和工作理由之后，现在可以开始动手来设计触发器了， 不过在动手之前，还有一些注意事项必须先了解一下： 11. .4. .1 设计触发器的限制在触发器中，有一些 SQL 语句是不能使用的，这些语句包括：表 11. .1 在 DML 触发器中不能使用的语句不能使用的语句语句功能Alter Database修改数据库Create Database新建数据库Drop Database删除数据库Load Da

7、tabase导入数据库Load Log导入日志Reconfigure更新配置选项Restore Database还原数据库Restore Log还原数据库日志另外，在对作为触发操作的目标的表或视图使用了下面的 SQL 语句时，不 允许在 DML 触发器里再使用这些语句：表 11. .2 在目标表中使用过的，DML 触发器不能再使用的语句不能使用的语句语句功能Create Index建立索引Alter Index修改索引Drop Index删除索引DBCC Dbreindex重新生成索引Alter Partition Function通过拆分或合并边界值更改分区Drop Table删除数据表Alter Table修改数据表结构11. .4. .2 如何在触发器取得字段修改前和修改后的数据上面介绍过，SQL Server 2005 在为每个触发器都定义了两个虚拟表，一个 是插入表（inserted），一个是删除表（deleted），现在把这两个表存放的数 据列表说明一下：表 11. .3 插入/删除表的功能激活触发器的动作Inserted 表Deleted 表Insert存放要插入的记录U

8、pdate存放要更新的记录存放更新前的旧记录Delete存放要删除的旧记录以上面删除库存产品记录为例，在删除时触发器要判断库存数量是否为零， 那么判断就应该这么写：If (Select 库存数量 From Deleted)0BeginPrint 库存数量大于零时不能删除此记录Rollback TransactionEnd11. .4. .3 其他注意事项l After 触发器只能用于数据表中，Instead Of 触发器可以用于数据 表和视图上，但两种触发器都不可以建立在临时表上。l 一个数据表可以有多个触发器，但是一个触发器只能对应一个表。l 在同一个数据表中，对每个操作（如 Insert、Update、Delete）而 言可以建立许多个 After 触发器，但 Instead Of 触发器针对每个操作 只有建立一个。l 如果针对某个操作即设置了 After 触发器又设置了 Instead Of 触发 器，那么 Instead of 触发器一定会激活，而 After 触发器就不一定会 激活了。l Truncate Table 语句虽然类似于 Delete 语句可以删除记录，但是 它不

9、能激活 Delete 类型的触发器。因为 Truncate Table 语句是不记入 日志的。l WRITETEXT 语句不能触发 Insert 和 Update 型的触发器。l 不同的 SQL 语句，可以触发同一个触发器，如 Insert 和 Update 语 句都可以激活同一个触发器。11. .5 设计 After 触发器在了解触发器及其种类、作用、工作原理之后，下面详细讲述一下要怎么 去设计及建立触发器。11. .5. .1 设计简单的 After 触发器下面用实例设计一个简单的 After Insert 触发器，这个触发器的作用是： 在插入一条记录的时候，发出“又添加了一种产品”的友好提示。（1）启动 Management Studio，登录到指定的服务器上。（2）在如图 11. .1 所示界面的【对象资源管理器】下选择【数据库】，定 位到【Northwind】数据库 【表】【dbo. .产品】，并找到【触发器】项。图 11. .1 定位到触发器（3）右击【触发器】，在弹出的快捷菜单中选择【新建触发器】选项，此 时会自动弹出【查询编辑器】对话框，在【查询编辑器】的编辑区里 SQL Server 已经预写入了一些建立触发器相关的 SQL 语句，如图 11. .2 所示。图 11. .2 SQL Server 2005 预写的触发器代码（4）修改【查询编辑器】里的代码，将从“CREATE”开始到“GO”结束的 代码改为以下代码：CREATE TRIGGER 产品_Insert ON 产品AFTER INSERTAS BEGINprint 又添加了一种产品ENDGO如果有兴趣的话，也可以去修改一下如图 11. .2 中绿色部分的版权信息。（5）单击工具栏中的【分析】按钮 ，检查一下是否语法有错，如图 11. .3 所示，如果在下面的【结果】对话框中出现“命令已成功完成”，则表示语法 没有错误。图 11. .3 检查语法（6）语法检查无误后，单击【执行】按钮，生成触发器。（7）关掉查询编辑器对话框，刷新一下触发器对话框，可以看到刚才建立 的【产品_Insert】触发器，如图 11. .4 所示。图 11

《触发器 整理的文档》由会员wt****50分享，可在线阅读，更多相关《触发器 整理的文档》请在金锄头文库上搜索。