触发器在 SQL Server 数据库开发中的应用

触发器在 SQL Server 数据库开发中的应用

　　在 SQL Server 数据库中数据的完整性是极其重要的 , 数据的完整性可以通过各种约束来保证 , 但对于一些较为复杂的数据库 , 由于设计的需要有一些冗余数据 ( 如 : 当年销量、标志列等 ) , 就需要比较复杂的完整性算法。在同一个数据库中简单参照完整性一般用 PR IMARY KEY 和 FORE IGN KEY 约束 , 而 CHECK 约束虽然可以完成较复杂的约束 , 但 CHECK 约束只能引用当前表中的列 , 所以它的使用受到了不小的限制 , SQL Server 中的触发器则可以保证实现复杂的完整性 , 并且可以引用其他表中的列 , 甚至其他数据库中的列。数据库是面向实际应用的 , 而在实际应用中往往需要强制实现一定的商业规则 , 用其他的数据库对象很难做到 , 但很容易通过触发器来完成。利用触发器还可以实现用户自定义的错误提示 , 用户对数据的操作不都是可行的 , 通过使用触发器 , 你可以在触发器执行过程中满足一定条件时发生存储在数据库中用户定义好的提示或错误信息。触发器实际上是一类特殊的存储过程 , 但它和一般的存储过程有本质的区别 , 存储过程用户可以调用 , 而触发器不能被调用 , 只有当对表或视图进行一定的触发操作 ( IN SERT 、 U PDA TE 和 DEL ETE) 时由系统自动调用。另外触发器不允许带参数和特定的表相关联。

1 　后触发和替代触发

　　在 SQL Server 中触发器有两种 : 后触发和替代触发。它们有各自的使用特点 , 合理的使用可以给数据库的管理和设计带来事半功倍的效果。触发操作有三种 : IN SERT 、 U PDA TE 和 DEL ETE 。触发器的信息和定义文本被插入到 sysob 2 ject s 和 syscomm en t s 系统表中 , 如果创建的触发器和一个现有的触发器同名 , 则原有的触发器将被覆盖。

1 . 1 　两个重要的临时表

　　SQL Server 触发器在执行时 , 系统会产生两个重要的临时表 : in serted 和 deleted, 它们保存在高速缓存中 , 从内存中分配空间 , 这两个表的结构与触发表或视图有着相同的结构 , 触发器工作完成后 , 与该触发器相关的这两个表也会被删除。在编写触发器时可以不另使用变量来保存修改前的数据行 , 而直接引用这两个临时表来比较、验证数据修改前后的变化、设置触发器的操作条件以便采取下一步行动。虽然所有的数据修改也都被记录在事务日志中 , 但事务日志中的数据是不可读的 , 只能用于保证事务的原子性以及用于数据库的日志备份和恢复。

　　触发操作中 , 在通过 IN SERT 语句向触发表中插入数据时 , 所插入的数据行同时也被添加到 in serted 临时表中 ; 在通过 DEL ETE 语句删除触发表中数据时 , 所删除数据行同时被添加到 deleted 临时表中 ; 在通过 U PDA TE 语句更新触发表中的数据时 , 先执行删除操作 , 删除的行 ( 前映像 ) 被添加到 deleted 临时表中 , 然后再执行插入操作 , 插入的行 ( 后映像 ) 被添加到 in serted 临时表中。值得注意的是 DEL ETE 类型的触发器不能通过 TRUNCA TE TABL E 语句触发 , 因为 TRUNCA TETABL E 语句不记录在事务日志中。

1 . 2 　后触发 ( AFTER 触发器 )

　　后触发型触发器是当所发生的触发操作完成 , 并处理完所有的其他约束后激活触发器。后触发器只能是针对表 , 而不能对视图定义触发器。

1 . 3 　替代触发 ( INSTEAD OF 触发器 )

　　替代触发实际上是取消了原有的触发动作 , 在数据库引擎创建了 in serted 和 deleted 临时表后执行相应的替代操作。 IN STEAD O F 触发器的触发对象既可以是表也可以是视图 ; 表和视图的每个触发操作 ( IN 2 SERT 、 DEL ETE 、 U PDA TE) 只能有一个 IN STEADO F 触发器 ; 但每个表的触发操作可以有多个 A FTER 触发器。我们知道视图是保护数据库敏感数据和进行复杂设计的重要手段 , 但通过视图可以 ( 只能 ) 修改一个基表中的数据 , 而通过在视图上创建的 IN STEAD O F 触发器则可以同时对多个基表进行修改。实例 : 在商品销售系统中 , 把客户信息分成两个客户表 : 国内客户表 (cu stom ers hom e) 和国外客户表 (cu stom ers fo reign ) , 创建视图 cu stom ers view( 略 ) 。

2 　触发器的应用

2 . 1 　强制数据完整性

　　在商品销售系统中 , 可以通过创建 U PDA TE 触发器强制数据完整性 : 当用户修改 o rders ( 定单 ) 表中的 qty ( 数量 ) 列时 , 自动更新产品表 (p roduct s) 中的 ytd sales ( 当年销量 ) 的值。具体代码略 , 需者来函。

2 . 2 　强制商业规则

　　在商品销售系统中 , 当删除 p roduct s 表中的产品时 , 通过 DEL ETE 触发器检查是否在 o rders 表中存在订购的历史 , 如果有就取消删除操作 ( 回滚事务 ) 。代码略 , 需者来函。

2 . 3 　用户自定义错误信息

　　在数据库设计中 , 定义用户自定义的错误信息可以在设计和实际应用中给出更加明确的错误和提示信息。约束、规则和缺省虽然也可以给出错误提示 , 但只能给出系统标准的错误提示信息 , 可读性差。但我们可以使用触发器来给出预先自定义的错误信息和复杂的错误处理。上例中就有简单的错误提示。

3 　使用触发器注意事项

①触发器和事务回滚

　　SQL Server 中的事务管理机制主要是为了保证数据库数据的完整性 , 事务内的一批操作要么全部执行 , 要么全部取消 , 即事务的原子性。触发器和激活触发器的语句是隐含的作为同一个事务来处理的 , 触发器中的任何错误和 ROLLBACK 命令都会使事务回滚。如果触发语句在一个用户定义的事务中 , 那么触发器中的任何错误和 ROLLBACKTRAN SACT ION 语句也将导致整个事务回滚。因为回滚会取消事务中已经完成的所有数据操作 , 所以应该尽量减少在触发器中使用 ROLLBACKTRAN SACT ION 语句。对数据的检查要尽量放在外部完成。

②一个触发器可以被触发表中的多个触发操作所触发 , 可以使用 IN SERT 、 DEL ETE 、 U PDA TE 的组合表示。如 :

CREA TE TR IGGER tril

ON o rders

FOR IN SERT,U PDA TE

A S. . .

③一个表的每个触发操作可以有多个触发器 , 可以使用 sp set t riggero rder 系统存储过程来指定哪个触发器最先或最后被激活 , 其他触发器顺序则不能被指定。如 :

Sp set t riggero rder ‘ delete t rigger ' , ‘ f irst ' , ‘ DEL ETE '

④触发器允许嵌套和递归 , 但在递归触发器中要包含递归结束检查。