触发器的概念和作用.doc

1、触发器的概念和作用 2010-08-30 21:11:38| 分类： sql2000 | 标签： |字号大中小 订阅 在 SQL Server 2005 里，可以用两种方法来保证数据的有效性和完整性：约束（check ）和触发器（Trigger）。约束是直接设置于数据表内，只能现实一些比较简单的功能操作，如：实现字段有效性和唯一性的检查、自动填入默认值、确保字段数据不重复（即主键）、确保数据表对应的完整性（即外键）等功能。触发器是针对数据表（库）的特殊的存储过程，当这个表发生了 Insert、Update 或 Delete 操作时，会自动激活执行的，可以处理各种复杂的操作。在 SQL Serv

2、er 2005 中，触发器有了更进一步的功能，在数据表（库）发生 Create、Alter 和 Drop 操作时，也会自动激活执行。触发器常用的一些功能如下：l 完成比约束更复杂的数据约束：触发器可以实现比约束更为复杂的数据约束l 检查所做的 SQL 是否允许：触发器可以检查 SQL 所做的操作是否被允许。例如：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消该删除操作。l 修改其它数据表里的数据：当一个 SQL 语句对数据表进行操作的时候，触发器可以根据该 SQL语句的操作情况来对另一个数据表进行操作。例如：一个订单取消的时候，那么

3、触发器可以自动修改产品库存表，在订购量的字段上减去被取消订单的订购数量。l 调用更多的存储过程：约束的本身是不能调用存储过程的，但是触发器本身就是一种存储过程，而存储过程是可以嵌套使用的，所以触发器也可以调用一个或多过存储过程。l 发送 SQL Mail：在 SQL 语句执行完之后，触发器可以判断更改过的记录是否达到一定条件，如果达到这个条件的话，触发器可以自动调用 SQL Mail 来发送邮件。例如：当一个订单交费之后，可以物流人员发送 Email，通知他尽快发货。l 返回自定义的错误信息：约束是不能返回信息的，而触发器可以。例如插入一条重复记录时，可以返回一个具体的友好的错误信息给前台应用

4、程序。l 更改原本要操作的 SQL 语句：触发器可以修改原本要操作的 SQL 语句，例如原本的 SQL 语句是要删除数据表里的记录，但该数据表里的记录是最要记录，不允许删除的，那么触发器可以不执行该语句。l 防止数据表构结更改或数据表被删除：为了保护已经建好的数据表，触发器可以在接收到 Drop和 Alter 开头的 SQL 语句里，不进行对数据表的操作。11.1.2 触发器的种类在 SQL Server 2005 中，触发器可以分为两大类：DML 触发器和 DDL 触发器l DML 触发器：DML 触发器是当数据库服务器中发生数据操作语言（Data Manipulation Language

5、）事件时执行的存储过程。DML 触发器又分为两类：After 触发器和 Instead Of 触发器l DDL 触发器：DDL 触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。DDL 触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。11.2 DML 触发器的分类SQL Server 2005 的 DML 触发器分为两类：l After 触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用 Rollback Transaction

6、 语句来回滚本次的操作。l Instead Of 触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来 SQL 语句里的操作（Insert、Update、Delete ），而去执行触发器本身所定义的操作。11.3 DML 触发器的工作原理在 SQL Server 2005 里，为每个 DML 触发器都定义了两个特殊的表，一个是插入表，一个是删除表。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。这两个表的结构与触发器所在数据表的结构是完全一致的，当触发器的工作完成之后，

7、这两个表也将会从内存中删除。插入表里存放的是更新前的记录：对于插入记录操作来说，插入表里存放的是要插入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。删除表里存放的是更新后的记录：对于更新记录操作来说，删除表里存放的是更新前的记录（更新完后即被删除）；对于删除记录操作来说，删除表里存入的是被删除的旧记录。下面看一下触发器的工作原理。11.3.1 After 触发器的工作原理After 触发器是在记录变更完之后才被激活执行的。以删除记录为例：当 SQL Server 接收到一个要执行删除操作的 SQL 语句时，SQL Server 先将要删除的记录存放在删除表里，然后把数据表里的记录

8、删除，再激活 After 触发器，执行 After 触发器里的 SQL 语句。执行完毕之后， 删除内存中的删除表，退出整个操作。还是举上面的例子：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消删除操作。看一下数据库是怎么操作的：（1）接收 SQL 语句，将要从产品库存表里删除的产品记录取出来，放在删除表里。（2）从产品库存表里删除该产品记录。（3）从删除表里读出该产品的库存数量字段，判断是不是为零，如果为零的话，完成操作，从内存里清除删除表；如果不为零的话，用 Rollback Transaction 语句来回滚操作。11.3.2

9、Instead Of 触发器的工作原理Instead Of 触发器与 After 触发器不同。After 触发器是在 Insert、Update 和 Delete 操作完成后才激活的，而 Instead Of 触发器，是在这些操作进行之前就激活了，并且不再去执行原来的 SQL 操作，而去运行触发器本身的 SQL 语句。11.4 设计 DML 触发器的注意事项及技巧在了解触发器的种类和工作理由之后，现在可以开始动手来设计触发器了，不过在动手之前，还有一些注意事项必须先了解一下：11.4.1 设计触发器的限制在触发器中，有一些 SQL 语句是不能使用的，这些语句包括：表 11.1 在 DML 触发

10、器中不能使用的语句不能使用的语句 语句功能Alter Database 修改数据库Create Database 新建数据库Drop Database 删除数据库Load Database 导入数据库Load Log 导入日志Reconfigure 更新配置选项Restore Database 还原数据库Restore Log 还原数据库日志另外，在对作为触发操作的目标的表或视图使用了下面的 SQL 语句时，不允许在 DML 触发器里再使用这些语句：不能使用的语句 语句功能Create Index 建立索引Alter Index 修改索引Drop Index 删除索引DBCC Dbreinde

11、x 重新生成索引Alter Partition Function 通过拆分或合并边界值更改分区Drop Table 删除数据表Alter Table 修改数据表结构11.4.2 如何在触发器取得字段修改前和修改后的数据上面介绍过，SQL Server 2005 在为每个触发器都定义了两个虚拟表，一个是插入表（inserted），一个是删除表（deleted），现在把这两个表存放的数据列表说明一下：表 11.3 插入/删除表的功能激活触发器的动作 Inserted 表 Deleted 表Insert 存放要插入的记录 Update 存放要更新的记录 存放更新前的旧记录Delete 存放要删除的旧

12、记录以上面删除库存产品记录为例，在删除时触发器要判断库存数量是否为零，那么判断就应该这么写：f (Select 库存数量 From Deleted)0BeginPrint 库存数量大于零时不能删除此记录Rollback TransactionEnd11.4.3 其他注意事项l After 触发器只能用于数据表中，Instead Of 触发器可以用于数据表和视图上，但两种触发器都不可以建立在临时表上。l 一个数据表可以有多个触发器，但是一个触发器只能对应一个表。l 在同一个数据表中，对每个操作（如 Insert、Update、Delete）而言可以建立许多个 After 触发器，但 Instea

13、d Of 触发器针对每个操作只有建立一个。l 如果针对某个操作即设置了 After 触发器又设置了 Instead Of 触发器，那么 Instead of 触发器一定会激活，而 After 触发器就不一定会激活了。l Truncate Table 语句虽然类似于 Delete 语句可以删除记录，但是它不能激活 Delete 类型的触发器。因为 Truncate Table 语句是不记入日志的。l WRITETEXT 语句不能触发 Insert 和 Update 型的触发器。l 不同的 SQL 语句，可以触发同一个触发器，如 Insert 和 Update 语句都可以激活同一个触发器。11.5

14、 设计 After 触发器在了解触发器及其种类、作用、工作原理之后，下面详细讲述一下要怎么去设计及建立触发器。11.5.1 设计简单的 After 触发器下面用实例设计一个简单的 After Insert 触发器，这个触发器的作用是：在插入一条记录的时候，发出“又添加了一种产品”的友好提示。（1）启动 Management Studio，登录到指定的服务器上。（2）在如图 11.1 所示界面的【对象资源管理器】下选择【数据库】，定位到【Northwind】数据库 【表 】 【dbo.产品】，并找到 【触发器】项。图 11.1 定位到触发器（3）右击【触发器 】，在弹出的快捷菜单中选择【新建触发

15、器】选项，此时会自动弹出【查询编辑器】对话框，在【查询编辑器】的编辑区里 SQL Server 已经预写入了一些建立触发器相关的 SQL 语句，如图 11.2 所示。 图 11.2 SQL Server 2005 预写的触发器代码（4）修改【查询编辑器 】里的代码，将从“CREATE”开始到“GO”结束的代码改为以下代码：CREATE TRIGGER 产品_InsertON 产品AFTER INSERTASBEGINprint 又添加了一种产品ENDGO如果有兴趣的话，也可以去修改一下如图 11.2 中绿色部分的版权信息。（5）单击工具栏中的【分析】按钮 ，检查一下是否语法有错，如果在下面的【

16、结果】对话框中出现“命令已成功完成”，则表示语法没有错误。（6）语法检查无误后，单击【执行】按钮，生成触发器。（7）关掉查询编辑器对话框，刷新一下触发器对话框，可以看到刚才建立的【产品_Insert】触发器。建立 After Update 触发器、After Delete 触发器和建立 After Insert 触发器的步骤一致，不同的地方是把上面的 SQL 语句中的 AFTER INSERT 分别改为 AFTER UPDATE 和 AFTER DELETE 即可，如下所示，有兴趣的读者可以自行测试。CREATE TRIGGER 产品_UpdateON 产品AFTER UPDATEASBEGI

17、Nprint 有一种产品更改了ENDGOCREATE TRIGGER 产品_DeleteON 产品AFTER DELETEASBEGINprint 又删除了一种产品ENDGO11.5.2 测试触发器功能建好 After Insert 触发器之后，现在来测试一下触发器是怎么样被激活的。（1）在 Management Studio 里新建一个查询，在弹出的【查询编辑器】对话框里输入以下代码：INSERT INTO 产品(产品名称) VALUES (大苹果)（2）单击【执行 】按钮，可以看到【消息】对话框里显示出一句提示：“又添加了一种产品” ，这说明，After Insert 触发器被激活，并运行

18、成功了。而如果在【查询编辑器】里执行的不是一个 Insert 语句，而是一个 Delete 语句的话，After Insert触发器将不会被激活。如在【查询编辑器】输入以下语句：DELETE FROM 产品 WHERE (产品名称= 大苹果)单击【执行】按钮，在【消息】对话框里只显示了一句“(1 行受影 响)” 的提示，而没有“ 又添加了一种产品”的提示。这是因为 Delete 语句是不能激活 After Insert 触发器，所以 After Insert 触发器里的“print 又添加了一种产品”语句并没有执行。11.5.3 建立触发器的 SQL 语句回顾一下，在 Management S

19、tudio 新建一个触发器的时候，它在查询分析对话框给预设了一些SQL 代码，这些代码其实上就是建立触发器的语法提示。现在来看一下完整的触发器语法代码：CREATE TRIGGER .ON .AFTER ASBEGIN- SET NOCOUNT ON added to prevent extra result sets from- interfering with SELECT statements.SET NOCOUNT ON;- Insert statements for trigger hereENDGO用中文改了一下，以上代码就一目了然了：CREATE TRIGGER 触发器名ON 数

20、据表名或视图名AFTER INSERT 或 DELETE 或 UPDATEASBEGIN-这里是要运行的 SQL 语句ENDGO现在再对上面的代码进行进一步的说明：l CREATE TRIGGER 触发器名：这一句声明 SQL 语句是用来建立一个触发器。其中触发器名在所在的数据库里必须是唯一的。由于触发器是建立中数据表或视图中的，所以有很多人都以为只要是在不同的数据表中，触发器的名称就可以相同，其实触发器的全名（Server.Database.Owner.TriggerName ）是必须 唯一的，这与触发器在哪个数据表或视图无关。l ON 数据表名或视图名：这是指定触发器所在的数据表或视图，但

21、是请注意，只有 Instead Of 触发器才能建立在视图上。并且，有设置为 With Check Option 的视图也不允许建立 Instead Of 触发器。l AFTER INSERT 或 DELETE UPDATE：这是指定触发器的类型，是 After Insert 触发器，还是After Delete 触发器，或者是 After Update 触发器。其中 After 可以用 For 来代取，它们的意思都是一样的，代表只有在数据表的操作都已正确完成后才会激活的触发器。INSERT、 DELETE 和 UPDATE 至少要指定一个，当然也可以指定多个，若指定多个时，必须用逗号来分开。

22、其顺序可以任意摆放。l With Encryption：With Encryption 是用来加密触发器的，放在“On 数据表名或视图名” 的后面，“For”的前面。如果使用了这句话，该触发器将会被加密，任何人都看不到触发器的内容了。例一：以下是一个包含提醒电子邮件的触发器例子，如果订单表里记录有改动的的话（无论增加订单还是修改、删除订单），则给物流人员张三发送电子邮件：CREATE TRIGGER 订单_InsertON 订单AFTER INSERT, UPDATE, DELETEASEXEC master.xp_sendmail 张三,订单有更改，请查询确定GO例二：在订单明细表里，折扣字

23、段不能大于 0.6，如果插入记录时，折扣大于 0.6 的话，回滚操作。CREATE TRIGGER 订单明细_InsertON 订单明细AFTER INSERTASBEGINif (Select 折扣 from inserted)0.6beginprint 折扣不能大于 0.6Rollback TransactionendENDGO在示例二中运用了两个方法，一个是前面说过的，在 Inserted 表里查询某个字段，还有一个是用Rollback Transaction 来回滚操作。如果用下面的 SQL 语句来进行 Insert 操作的话，插入记录将会不成功。INSERT INTO 订单明细(订单

24、 ID,产品 ID,单价,数量,折扣) VALUES (11077,1,18,1,0.7)运行结果如图 11.3 所示：图 11.3 插入记录不符合触发器里的约束，则回滚操作11.6 设置 After 触发器的激活顺序对于同一个操作，如 Insert、Update 或 Delete 来说，可以建立多个 After Insert 触发器，在 11.5.1节中，已经建立了一个名为“产品 _Insert”的触发器，现在再建立一个 After Insert 触发器，作用也是输出一句有好提示，提示内容为：“再一次告诉你，你又添加了一种产品 ”。CREATE TRIGGER 产品_Insert1ON 产品

25、AFTER INSERTASBEGINprint 再一次告诉你，你又添加了一种产品ENDGO重新运行一下插入产品的 SQL 语句：INSERT INTO 产品(产品名称)VALUES (大苹果 )如图 11.4 所示，运行一个 Insert 语句，在【消息】可以看到一共输出了两句话，说明激活两个不同的触发器。图 11.4 一个语句激活两个触发器当同一个操作定义的触发器越来越多的时候，触发器被激活的次序就会变得越来越重要了。在 SQL Server 2005 里，用存储过程【sp_settriggerorder】可以为每一个操作各指定一个最先执行的 After 触发器和最后执行的 After 触

26、发器。 sp_settriggerorder 语法如下：sp_settriggerorder triggername = triggerschema. triggername, order = value, stmttype = statement_type , namespace = DATABASE | SERVER | NULL 翻译成中文就是sp_settriggerorder 触发器名,激活次序,激活触发器的动作解释如下：l 触发器名，要用单引号括起来，因为它是一个字符串。l 激活次序可以为 First、Last 和 None：First 是指第一个要激活的触发器；Last 是指它最

27、后一个要激活的触发器；None 是不指激活序，由程序任意触发。l 激活触发器的动作可以是：Insert、Update 和 Delete。上面的例子里，先激活的是【产品_Insert】触发器，后激活的是【产品_Insert1】触发器。如果把【产品_Insert1】 触发器设为 First 触发器，把【产品_Insert 】触发器设为 Last 触发器，那么结果将会完全不一样。设置语句如下：Exec sp_settriggerorder产品 _Insert1,First,InsertgoExec sp_settriggerorder产品 _Insert,Last,InsertGo重新运行一下插入产

28、品的 SQL 语句：INSERT INTO 产品(产品名称)VALUES (大苹果 )运行结果如图 11.5，与图 11.4 比较一下，是不是激活次序已经发生变化了？图 11.5 按次序激活的激活器在设置 After 触发器激活顺序时，还有几点是需要注意的：l 每个操作最多只能设一个 First 触发器和一个 Last 触发器。l 如果要取消已经设好的 First 触发器或 Last 触发器，只要把它们设为 None 触发器即可。l 如果用 Alter 命令修改过触发器内容后，该触发器会自动变成 None 触发器。所以用 Alter 命令也可以用来取消已经设好的 First 触发器或 Last 触发器。l 只有 After 触发器可以设置激活次序，Instead Of 触发器不可以设置激活次序。l 激活触发器的动作必须和触发器内部的激活动作一致。举例说明：After Insert 触发器，只能为Insert 操作设置激活次序，不能为 Delete 操作设置激活次序。以下的设置是错误的：Exec sp_settriggerorder