最新Sqlserver技术知识点之二十..doc

《最新Sqlserver技术知识点之二十..doc》由会员分享，可在线阅读，更多相关《最新Sqlserver技术知识点之二十..doc（26页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-dateSqlserver技术知识点之二十.Sqlserver技术知识点之二十.Sqlserver 技术知识点之二十系统存储过程就是系统创建的存储过程，目的在于能够方便地从系统表中查询信息或完成与更新数据库表相关的管理任务或其它的系统管理任务。系统过程以“sp_ 为开头,在Master 数据库中创建并保存在该数据库中，为数据库管理者所有。一些系统过程只能由系统管理员使用，而有

2、些系统过程通过授权可以被其它用户所使用。系统存储过程主要包括以下几类：（这里主要给出每类系统过程中经常使用的系统过程）在上面几节我们介绍了一般意义的存储过程，即用户自定义的存储过程和系统存储过程。本节将介绍一种特殊的存储过程，即触发器。在余下各节中我们将对触发器的概念、作用以及对其的使用方法作详尽介绍，使读者了解如何定义触发器，创建和使用各种不同复杂程度的触发器。12.5.1 触发器的概念及作用触发器是一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、 INSERT、 D

3、ELETE 这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：（1） 强化约束（Enforce restriction）触发器能够实现比CHECK 语句更为复杂的约束。 （2） 跟踪变化Auditing changes触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化。（3） 级联运行（Cascaded operation）。触发器可以侦测数据库内的操作，并自动地级

4、联影响整个数据库的各项内容。例如，某个表上的触发器中包含有对另外一个表的数据操作（如删除，更新，插入）而该操作又导致该表上触发器被触发。 （4） 存储过程的调用（Stored procedure invocation）。为了响应数据库更新触，发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS（ 数据库管理系统）本身之外进行操作。由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的处理。此外一个表的同一类型（INSERT、 UPDATE、 DELETE）的多个

5、触发器能够对同一种数据操作采取多种不同的处理。总体而言，触发器性能通常比较低。当运行触发器时，系统处理的大部分时间花费在参照其它表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。12.5.2 触发器的种类SQL Server 2000 支持两种类型的触发器：AFTER 触发器和INSTEAD OF 触发器。其中AFTER 触发器即为SQL Server 2000 版本以前所介绍的触发器。该类型触发器要求只有执行某一操作（INSERT UPDATE DELETE） 之后，触发器才被触发，且只能在表上

6、定义。可以为针对表的同一操作定义多个触发器。对于AFTER 触发器，可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程sp_settriggerorder 来完成此任务。INSTEAD OF 触发器表示并不执行其所定义的操作（INSERT、 UPDATE、 DELETE），而仅是执行触发器本身。既可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF 触发器，但对同一操作只能定义一个INSTEAD OF 触发器。上面介绍了有关触发器的概念、作用和一些基本问题，下面我们将分别介绍在MS SQLServer 中如何用SQL Server 管理工具Enter

7、prise Manager 和Transaction_SQL 来创建触发器。在创建触发器以前必须考虑到以下几个方面： CREATE TRIGGER 语句必须是批处理的第一个语句； 表的所有者具有创建触发器的缺省权限，表的所有者不能把该权限传给其它用户； 触发器是数据库对象，所以其命名必须符合命名规则； 尽管在触发器的SQL 语句中可以参照其它数据库中的对象，但是，触发器只能创建在当前数据库中； 虽然触发器可以参照视图或临时表，但不能在视图或临时表上创建触发器，而只能在基表或在创建视图的表上创建触发器； 一个触发器只能对应一个表，这是由触发器的机制决定的； 尽管TRUNCATE TABLE 语句

8、如同没有WHERE 从句的DELETE 语句，但是由于TRUNCATE TABLE 语句没有被记入日志，所以该语句不能触发DELETE 型触发器； WRITETEXT 语句不能触发INSERT 或UPDATE 型的触发器。 当创建一个触发器时，必须指定触发器的名字，在哪一个表上定义触发器，激活触发 器的修改语句，如INSERT、 DELETE、 UPDATE。 当然两个或三个不同的修改语句也可 以都触发同一个触发器，如INSERT 和UPDATE 语句都能激活同一个触发器。12.6.1 用管理工具Enterprise Manger 创建触发器其操作步骤如下：1. 启动Enterprise Ma

9、nger， 登录到指定的服务器上。 2. 展开数据库，然后展开要在其上创建触发器的表所在的数据库，然后单击该表。 3. 右击鼠标，在弹出菜单中选择ALL Tasks, 然后单击Manage Triggers。 4. 在名字框中选择new， 在文本框中输入触发器文本，如图12-5 所示。 5. 单击Check Syntax 检查语句是否正确。 6. 单击Apply， 在Name 下拉列表中会有新创建的触发器名字。 7. 单击OK， 关闭窗口创建成功。12.6.2 用CREATE TRIGGER 命令创建触发器各参数的说明如下： trigger_name是用户要创建的触发器的名字触发器的名字，必须

10、符合MS SQL Server 的命名规则，且其名字在当前数据库中必须是惟一的。 Table是与用户创建的触发器相关联的表的名字，并且该表已经存在。 WITH ENCRYPTION表示对包含有CREATE TRIGGER 文本的syscomments 表进行加密。 AFTER表示只有在执行了指定的操作（INSERT、 DELETE、 UPDATE）之后触发器才被激活，执行触发器中的SQL 语句。若使用关键字FOR， 则表示为AFTER 触发器，且该类型触发器仅能在表上创建。 INSTEAD OF请参看“12.8 INSTEAD OF 触发器” DELETE , INSERT , UPDATE关

11、键字用来指明哪种数据操作将激活触发器。至少要指明一个选项，在触发器的定义中三者的顺序不受限制，且各选项要用逗号隔开。 WITH APPEND表明增加另外一个已存在某一类型触发器。只有在兼容性水平（指某一数据库行为与以前版本的MS SQL Server 兼容程度）不大于65 时才使用该选项。 NOT FOR REPLICATION表明当复制处理修改与触发器相关联的表时，触发器不能被执行。 AS是触发器将要执行的动作。 Sql_statement是包含在触发器中的条件语句或处理语句。触发器的条件语句定义了另外的标准来决定将被执行的INSERT、 DELETE、 UPDATE 语句是否激活触发器。

12、IF UPDATE （column）用来测定对某一确定列是插入操作还是更新操作，但不与删除操作用在一起。 IF （COLUMNS_UPDATED（）仅在INSERT 和UPDATE 类型的触发器中使用，用其来检查所涉及的列是被更新还是被插入。 Bitwise_operatorj是在比较中使用的位逻辑运算符。 Pdated_bitmask是那些被更新或插入的列的整形位掩码。例如，如果表T 包括C1， C2， C3， C4， C5五列。为了确定是否只有C2 列被修改，可用2 来做位掩码，如果想确定是否C1， C2， C3，C4 都被修改，可用14 来做位掩码。 Comparison_operato

13、r是一比较操作符用“= ”表示检查在updated_bitmask 中定义的所有列是否都被更新，用“” 表示检查是否在updated_bitmask 中定义的某些列被更新。 Column_bitmask指那些被检查是否被更新的列的位掩码。从以上的介绍中我们可以看出触发器具有强大的功能，那么MS SQL Server 是如何管理触发器来完成这些任务呢？下面我们将对其工作原理及实现做较为详细的介绍。每个触发器有两个特殊的表：插入表和删除表。这两个表是逻辑表，并且这两个表是由系统管理的，存储在内存中，不是存储在数据库中，因此不允许用户直接对其修改。这两个表的结构总是与被该触发器作用的表有相同的表结构

14、。这两个表是动态驻留在内存中的，当触发器工作完成，这两个表也被删除。这两个表主要保存因用户操作而被影响到的原数据值或新数据值。另外，这两个表是只读的，即用户不能向这两个表写入内容，但可以引用表中的数据。例如可用如下语句查看DELETED 表中的信息：select * from deleted下面详细介绍这两个表的功能：12.7.1 插入表的功能对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入操作，那么对向该表插入的所有行来说，都有一个相应的副本存放到插入表中。即插入表就是用来存储向原表插入的内容。12.7.2 删除表的功能对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除操作，则

15、将所有的删除行存放至删除表中。这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从删除表中得以恢复。需要强调的是，更新操作包括两个部分，即先将更新的内容去掉，然后将新值插入。因此对一个定义了更新类型触发器的表来讲，当报告会更新操作时，在删除表中存放了旧值，然后在插入表中存放新值。由于触发器仅当被定义的操作被执行时才被激活，即仅当在执行插入、删除、和更新操作时，触发器将执行。每条SQL 语句仅能激活触发器一次，可能存在一条语句影响多条记录的情况。在这种情况下就需要变量rowcount 的值，该变量存储了一条SQL 语句执行后所影响的记录数，可以使用该值对触发器的SQL 语句执行后所影响的记录求合计值。一般来说，首先要用IF 语句测试rowcount 的值以确定后面的语句是否执行。-