SQL-Server-2000触发器.doc

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