1、SQL Server 2000 触发器一、简介触发器是一种特殊的存储过程，类似于其它编程语言中的事件函数，SQL Serve2000 允许为 INSERT、UPDATE、DELETE 创建触发器，当在表（视图）中插入、更新、删除记录时，触发一个或一系列 T-SQL 语句。两大类：DML 触发器和 DLL 触发器。1、优缺点触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：强化约束（Enforce restriction）触发器能够实现比 CHECK 语句更为复杂的约束。跟踪变化 Auditing changes触发器可
2、以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化。级联运行（Cascaded operation） 。触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器中包含有对另外一个表的数据操作（如删除，更新，插入）而该操作又导致该表上触发器被触发。存储过程的调用（Stored procedure invocation） 。为了响应数据库更新触，发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在 DBMS（ 数据库管理系统）本身之外进行操作。由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题。例如，触
3、发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的处理。此外一个表的同一类型（INSERT、 UPDATE、 DELETE）的多个触发器能够对同一种数据操作采取多种不同的处理。总体而言，触发器性能通常比较低。当运行触发器时，系统处理的大部分时间花费在参照其它表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。2、注意事项在视图上创建普通触发器可能会出现“对象无效”的错误，实际上，我们不能在视图上创建 FOR 触发器，而应该创建 INSTEAD OF 触发器。在 SQL Serv
4、er 2000联机丛书中，是没有说触发器不能在视图上创建的， 并且在语法解释中表明：在CREATE TRIGGER 的 ON 之后可以是视图。 然而，事实似乎并不是如此，很多专家也说触发器不能在视图上创建。 我也专门作了测试，的确如此，不管是 普通视图还是索引视图，都无法在上面创建触发器. 但是无可厚非的是：当在临时表或系统表上创建触发器时会遭到拒绝。FOR CREATE TRIGGER 语句FOR 关键字之后可以跟 INSERT、UPDATE、DELETE 中的一个或多个，也就是说在其它情况下是不会触发触发器的， 包括SELECT、TRUNCATE 、 WRITETEXT、UPDATETEX
5、T。Truncate 与 delete 的区别：TRUNCATE TABLE 和不带 WHERE 的DELETE 功能是一样的，都是删除表中的所有数据，不过 TRUNCATE TABLE 速度更快，占用的日志更少，这是因为 TRUNCATE TABLE 直接释放数据页并且在事务日志中也只记录数据页的释放，而 DELETE 是一行一行地删除，在事务日志中要记录每一条记录的删除。 那么可不可以用 TRUNCATE TABLE 代替不带 WHERE 的 DELETE 呢？在以下情况是不行的： 1、要保留标识的情况下，不能用 TRUNCATE TABLE，因为 TRUNCATE TABLE会重置标识。
6、 2、需要使用触发器的情况下，不能使用 TRUNCATE TABLE ，它不会激发触发器。3、对于由 FOREIGN KEY 约束引用的表（即主键所在的表，不是外键所在的表）不能使用 TRUNCATE TABLE。 4、对于参与了索引视图的表不能使用 TRUNCATE TABLE ，注意指索引视图，并非普通视图。 二、触发器常用功能操作一个有趣的触发器应用：触发器回滚触发器内部语句出错时，前面对数据更改操作将会无效。 举个例子，在表中插入数据时触发触发器，而触发器内部此时发生了运行时错误，那么将返回一个错误值，并且拒绝刚才的数据插入。 我们看到许多注册系统在注册后都不能更改用户名，但这多半是由
7、应用程序决定的，如果直接打开数据库表进行更改，同样可以更改其用户名，在触发器中利用回滚就可以巧妙地实现无法更改用户名。 CREATE TRIGGER trigger_nameON 表名 FOR update ASif update(userName) rollback transaction关键在最后两句，其解释为：如果更新了 userName 列，就回滚事务。 不能在触发器中使用的语句 :触发器中可以使用大多数 T-SQL 语句，但如下一些语句是不能在触发器中使用的。 CREATE 语句，如：CREATE DATABASE、CREATE TABLE、CREATE INDEX 等。 ALTER
8、 语句，如：ALTER DATABASE、ALTER TABLE、ALTER INDEX 等。 DROP 语句，如：DROP DATABASE、DROP TABLE、DROP INDEX 等。 DISK 语句，如：DISK INIT（磁盘初始化） 、DISK RESIZE（磁盘重写） 。 LOAD 语句，如：LOAD DATABASE（导入数据库） 、LOAD LOG（导入日志） 。 RESTORE 语句，如：RESTORE DATABASE（还原数据库） 、RESTORE LOG(还原日志)。 RECONFIGURE:(更新配置选项 ) DBCC：DBCC Dbreindex（重新生成索引）
9、说明：有人说不能用 TRUNCATE TABLE 语句，其实是可以的。 查看某个触发器的内容 :方式一：exec sp_helptext 触发器名称 将会以表的样式显示触发器内容。除了触发器外，sp_helptext 还可以显示规则、默认值、未加密的存储过程、用户定义函数、视图的文本 .方式二：用企业管理器查看 在表上点右键-“所有任务”-“管理触发器”，选择所要查看的触发器 ,查看当前数据库中有哪些触发器 方式三：在查询分析器中运行：select * from sysobjects where xtype=TR sysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为
10、触发器对象。在name 一列，我们可以看到触发器名称。 删除触发器 方式一：用查询分析器删除 ：DROP TRIGGER 触发器名称 1,触发器名称 2. 注意 1：触发器名称是不加引号的。注意 2：在删除触发器之前可以先看一下触发器是否存在： if Exists(select name from sysobjects where name=触发器名称 and xtype=TR) 方式二：用企业管理器删除 在企业管理器中，在表上点右键-“所有任务”-“管理触发器”，选中所要删除的触发器，然后点击“删除”。 重命名触发器 方式一：用查询分析器重命名 ：exec sp_rename 原名称, 新名
11、称 sp_rename 是 SQL Server2000 自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。 方式二：用企业管理器重命名 在表上点右键-“所有任务”-“管理触发器”，选中所要重命名的触发器，修改触发器语句中的触发器名称，点击“确定”。 查看触发器的属性存储过程 sp_helptrigger 用于查看触发器的属性。sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6) 类型，可以是INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。 exec sp_helptrigg
12、er table_name 三、触发器更多语法 INSTEAD OF 类型触发器执行触发器语句，但不执行触发触发器的 SQL 语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete 语句。例： create trigger trigger_nameon table_nameinstead of delete as insert into Logs. IF UPDATE(列名) 检查是否更新了某一列，用于 insert 或 update，不能用于 delete。例： create trigger trigger_name on table_name for update
13、 as if update(status) or update(title) sql_statement -更新了 status 或 title 列 inserted、deleted 这是两个虚拟表,每个触发器将用到的两个临时表。inserted 保存的是 insert 或 update 之后所影响的记录形成的表；deleted 保存的是 delete 或 update (先 insert 后 delete)之前所影响的记录形成的表。deleted 表和 inserted 表的特征： 这两个表的表结构与该触发器作用的表相同； 这两个表是逻辑表，并且由系统管理； 这两个表是动态驻留在内存中的(
14、不是存储在数据库中)，当触发器工作完成后，它们也被删除； 这两个表是只读的，即只能运用 select 语句查看( 用户不能直接更改)；例： create trigger trigger_name on table_namefor delete as declare title varchar(200) select title=title from deleted insert into Logs(logContent) values(删除了 title 为： + title + 的记录) 说明：如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值
15、时，所取得的值将会是 null。 四、SQL Server 2000 触发器的两种类型SQL Server 2000 支持两种类型的触发器：AFTER 触发器和 INSTEAD OF 触发器。FOR 触发器不是 before，SQL Server 只有 INSTEAD OF 和 AFTER 两种触发器。默认识 AFTER 的，所以说 FOR 触发器就是 AFTER 触发器。AFTER 触发器AFTER 触发器即为 SQL Server 2000 版本以前所介绍的触发器。该类型触发器要求只有执行某一操作（INSERT UPDATE DELETE） 之后，触发器才被触发，且只能在表上定义。可以为针
16、对表的同一操作定义多个触发器。对于 AFTER 触发器，可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程 sp_settriggerorder 来完成此任务。INSTEAD OF 触发器INSTEAD OF 触发器表示并不执行其所定义的操作（INSERT、 UPDATE、 DELETE） ，而仅是执行触发器本身。既可在表上定义 INSTEAD OF 触发器，也可以在视图上定义 INSTEAD OF 触发器，但对同一操作只能定义一个 INSTEAD OF 触发器。异同点After 触发器只能用于数据表中， Instead Of 触发器可以用于数据表和视图上，但两种触发器都不可以
17、建立在临时表上。一个数据表可以有多个触发器，但是一个触发器只能对应一个表。在同一个数据表中，对每个操作（如 Insert、Update、Delete）而言可以建立许多个 After 触发器，但 Instead Of 触发器针对每个操作只有建立一个。如果针对某个操作即设置了 After 触发器又设置了 Instead Of 触发器，那么 Instead of 触发器一定会激活，而 After 触发器就不一定会激活了。5、禁用和启用触发器禁用和启用指定表中的触发器#alter table table_name disable trigger trigger_name#alter table tab
18、le_name enable trigger trigger_name禁用和启用指定的触发器#disable trigger trigger_name on database -禁用触发器#enable trigger trigger_name on database -开启触发器禁用和启用所有的触发器#exec sp_msforeachtable ALTER TABLE ? disable TRIGGER all #exec sp_msforeachtable ALTER TABLE ? enable TRIGGER all 六、谨慎使用触发器触发器功能强大，轻松可靠地实现许多复杂的功能，为
19、什么又要慎用呢。 触发器本身没有过错，但由于我们的滥用会造成数据库及应用程序的维护困难。 在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作，比如删除 T1 表记录时期望删除 T2 表相关的记录，此时可以建立级联删除的关系，也可以为 T1 表建立触发器使同时删除 T2 表相关记录，也可以自定义存储过程删除T1 和 T2 表的记录，也可以在应用程序中使用两个 SQL 语句来删除到底用哪一种好呢，应该说我们建立通过建立关系来实现级联删除是最好的，除非更有高的需求。 触发器还有一个用途可以用来保障数据的完整性，但同时规则、约束、默认值也可以保障数据完整性，到底哪一个好呢，一般说来，较为简单的完整性要求我们不应该使用触发器。两者在运行机制上也是有区别:像规则、约束、默认值这些是在数据更改之前进行数据验证; 而触发器是在数据更改之后进行验证（如果事务回滚，该表将不会产生变化） 。 总之，如果我们对触发器过分的依赖，就会造成遍地是程序的情况，因为触发器本身就需要别的程序给它一个触发条件，也就是说至少在两个地方存在着程序，同时我们抛弃了约束、默认值等而选用触发器，势必影响数据库的结构。
