触发器的概念和作用
2010-08-30 21:11:38| 分类： sql2000 | 标签： |字号大中小 订阅
在SQL Server 2005里，可以用两种方法来保证数据的有效性和完整性：约束（check）和触发器（Trigger）。约束是直接设置于数据表内，只能现实一些比较简单的功能操作，如：实现字段有效性和唯一性的检查、自动填入默认值、确保字段数据不重复（即主键）、确保数据表对应的完整性（即外键）等功能。
触发器是针对数据表（库）的特殊的存储过程，当这个表发生了 Insert、Update或Delete操作时，会自动激活执行的，可以处理各种复杂的操作。在SQL Server 2005中，触发器有了更进一步的功能，在数据表（库）发生Create、Alter和Drop操作时，也会自动激活执行。
触发器常用的一些功能如下：
l 完成比约束更复杂的数据约束：触发器可以实现比约束更为复杂的数据约束
l检查所做的SQL是否允许：触发器可以检查SQL所做的操作是否被允许。例如：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消该删除操作。
l 修改其它数据表里的数据：当一个SQL语句对数据表进行操作的时候，触发器可以根据该SQL语句的操作情况来对另一个数据表进行操作。例如：一个订单取消的时候，那么触发器可以自动修改产品库存表，在订购量的字段上减去被取消订单的订购数量。
l 调用更多的存储过程：约束的本身是不能调用存储过程的，但是触发器本身就是一种存储过程，而存储过程是可以嵌套使用的，所以触发器也可以调用一个或多过存储过程。
l 发送SQL Mail：在SQL语句执行完之后，触发器可以判断更改过的记录是否达到一定条件，如果达到这个条件的话，触发器可以自动调用SQL Mail来发送邮件。例如：当一个订单交费之后，可以物流人员发送Email，通知他尽快发货。
l 返回自定义的错误信息：约束是不能返回信息的，而触发器可以。例如插入一条重复记录
时，可以返回一个具体的友好的错误信息给前台应用程序。
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触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。
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触发器都定义了两个特殊的表，一个是插入表，一个是
删除表。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。
这两个表的结构与触发器所在数据表的结构是完全一致的，当触发器的工作完成之后，这两个表也将会从内存中删除。
插入表里存放的是更新前的记录：对于插入记录操作来说，插入表里存放的是要插入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。
删除表里存放的是更新后的记录：对于更新记录操作来说，删除表里存放的是更新前的记录（更新完后即被删除）；对于删除记录操作来说，删除表里存入的是被删除的旧记录。
下面看一下触发器的工作原理。
11.3.1 After触发器的工作原理
After触发器是在记录变更完之后才被激活执行的。以删除记录为例：当SQL Server接收到一个要执行删除操作的SQL语句时，SQL Server先将要删除的记录存放在删除表里，然后把数
据表里的记录删除，再激活After触发器，执行After触发器里的SQL语句。执行完毕之后， 删除内存中的删除表，退出整个操作。
还是举上面的例子：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消删除操作。看一下数据库是怎么操作的：（1）接收SQL语句，将要从产品库存表里删除的产品记录取出来，放在删除表里。（2）从产品库存表里删除该产品记录。（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 Database | 导入数据库 |
Load Log | 导入日志 |
| sql触发器的使用 Reconfigure | 更新配置选项 |
Restore Database | 还原数据库 |
Restore Log | 还原数据库日志 |
另外，在对作为触发操作的目标的表或视图使用了下面的SQL语句时，不允许在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 | 存放要插入的记录 | |
Update | 存放要更新的记录 | 存放更新前的旧记录 |
Delete | 存放要删除的旧记录 | |
以上面删除库存产品记录为例，在删除时触发器要判断库存数量是否为零，那么判断就应该这么写：
f (Select 库存数量 From Deleted)>0
Begin
Print ‘库存数量大于零时不能删除此记录’
Rollback Transaction
End
11.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语句可以删除记录，但是它不能激活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预写的触发器代码
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论