189.存储过程和触发器

第9章存储过程和触发器

•    9.1 存储过程

 

9.1.1 存储过程的概念

u 存储过程是指封装了可重用代码的、存储在服务器上的程序模块或例程。存储过程是数据库对象之一，它类似于其他高级编程语言中的过程或子程序，编译成可执行代码后保存在服务器上，可多次调用。

 

u 其特点体现在：

Ø 可以接受多个输入参数，能够以多输出参数的格式返回多个值。

Ø 在服务器端运行，使用EXECUTE（简写为EXEC）语句来执行。

Ø 可以调用其他存储过程，也可以被其他语句或存储过程调用，但不能直接在表达式中使用。

Ø 具有返回状态值，表明被调用是成功还是失败。但不返回取代其名称的值，这是它与函数的不同之处。

Ø 存储过程已在服务器注册。

 

 

存储过程的优点主要体现在：

Ø 提高程序的执行效率。存储过程执行在第一次被执行以后，其执行规划就驻留在高速缓冲存储器中。在以后的每次操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行即可，而不必重新编译再执行，从而提高了执行效率。

Ø 具有较高的安全特性。作为一种数据库对象，存储过程要求拥有相应权限的用户才能执行它。同时，它也提供了一种更为灵活的安全性管理机制：用户可以被授予权限来执行存储过程，而不必对存储过程中引用的对象拥有访问权限。

Ø  如果一个存储过程是用于更新某一个数据表的，那么只要用户拥有执行该存储过程的权限，他就可以通过执行该存储过程的方法来实现对指定数据表的更新操作，而不必直接拥有对该数据表操作的权限。

Ø 减少网络通信流量。由于存储过程在服务器端执行，用户每次只需发出一条执行命令，而不必发出存储过程所有的冗长代码，因而减少了网络的数据流量。

Ø 允许模块化程序设计，提高代码的可重用性。存储过程一旦被创建，以后就可以在所有程序中多次调用。这有利于程序的结构化设计，提高程序的可维护性和代码的可重用性。

 

 

9.1.2 存储过程的类型

Ø 在SQL Server 2008中，存储过程可以分为两种类型：SQL存储过程和CLR存储过程。SQL存储过程是指出由SQL语言编写而形成的存储过程，它是SQL语句的集合。CLR(Common Language Runtime)存储过程是指引用Microsoft.NET Framework公共语言运行时(CLR)方法的存储过程，它在.NET Framework程序集中是作为类的公共静态方法实现的。

 

Ø 目前常使用的是SQL存储过程，所以本书要介绍的也就是这类存储过程。

 

Ø 根据来源和应用目的的不同，又可以将存储过程分为用户存储过程、系统存储过程和扩展存储过程。

 

 

1. 系统存储过程

ü 系统存储过程是SQL Server 2008本身定义的、当作命令来执行的一类存储过程。它主要用于管理SQL Server 数据库和显示有关数据库及用户的信息，通常前缀“sp_”。

Ø  sp_addrolemember就是一个用于为数据库角色添加成员的系统存储过程。从逻辑结构看，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。读者最好能够熟悉一些常用的系统存储过程，以免重复开发。

 

2. 用户存储过程

ü 用户存储过程是指由用户通过利用SQL语言编写的、具有特定功能的一类存储过程。由于系统存储过程以“sp_”为前缀，扩展存储过程以“xp_”，所以用户存储过程在定义时最好不要使用“sp_”或“xp_”为前缀。如果需要，用户存储过程应以“up_”为前缀，“u”是单词user的头字母。

ü 本章将主要介绍用户存储过程的定义、修改和删除等基本管理操作。

 

 

3. 扩展存储过程

ü 扩展存储过程是指SQL Server的实例可以动态加载和运行的动态链接库（DLL）。通过扩展存储过程，可以使用其他编程语言（如C语句）创建自己的外部程序，实现了SQL程序与其他语言程序的连接与融合。

ü 扩展存储过程直接在SQL Server的实例地址空间中运行，可以使用SQL Server扩展存储过程API完成编程。但由于后续的SQL Server版本中将不支持扩展存储过程，所以在新的工程开发项目中应尽量少用或不用这种功能。

 

 

9.1.3 存储过程的创建和调用

存储过程是由CREATE PROCEDURE语句来创建，其语法如下：

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
  [ { @parameter [ type_schema_name. ] data_type }
      [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ]
[ FOR REPLICATION ]
AS { <sql_statement> [;][ ...n ] | <method_specifier> }[;]

<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE_AS_Clause ]

<sql_statement> ::= { [ BEGIN ] statements [ END ] }

<method_specifier> ::= EXTERNAL NAME assembly_name.class_name.method_name

 

 

对涉及的参数说明如下：

ü schema_name：设定存储过程所属架构的名称。

ü procedure_name：存储过程的名称。它是一个合法的标识符，在架构中是唯一的。存储过程名一般不能使用前缀“sp_”，此前缀由系统存储过程使用。如果过程名以井号“#”开头，则表示创建的过程将局部临时过程，这种过程名的长度不超116个字符（含#）；如果以双井号“##”开头，则表示是全局临时过程，这种过程名的长度不超128个字符（含##）。

ü number：用于对同名的存储过程进行分组的整数。例如，myPro;1、myPro;2等。

ü @parameter：存储过程带的参数，data_type为参数所属架构的数据类型。参数可以是一个或者多个，最多为2,100个参数。在定义时参数可以设置默认值，而对于没有设置默认值的参数，在调用时必须为其提供值。在默认情况下，参数只能代表常量表达式，而不能用于代表表名、列名或其他数据库对象的名称。如果指定了FOR REPLICATION，则无法声明参数。

ü  OUTPUT（或OUT）：如果指定了OUTPUT（或OUT），则表示该参数为输出参数。输出参数用于将存储过程处理后的某些结果返回给调用它的语句。游标（cursor）数据类型参数必须指定OUTPUT，同时还必须指定关键字VARYING。一般情况下，text、ntext和image类型参数不能用作OUTPUT 参数。

ü  VARYING：指定输出参数支持的结果集。仅适用于游标类型参数。

ü  default：设定参数的默认值。如果定义了default值，则在调用存储过程时无需为此参数指定值，否则必须指定参数值才能调用。默认值必须是常量或NULL。

ü  RECOMPILE：该选项用于指示SQL Server不要将存储过程的执行规划保存在高速缓冲存储器中，因为该过程在执行时要重新编译，然后才运行。如果指定了FOR REPLICATION，则不能使用此选项。

ü  ENCRYPTION：指示SQL Server对CREATE PROCEDURE语句的原始文本进行加密，加密后的代码的输出在SQL Server 2008的任何目录视图中都不能直接显示。

ü  EXECUTE AS：该子句用于指定在其中执行存储过程的安全上下文。

ü  FOR REPLICATION：如果选择该选项，则表示创建的存储过程只能在复制过程中执行。该类过程不能声明参数，忽略RECOMPILE选项。

ü  <sql_statement>：表示包含在过程中的一个或多个SQL语句。

ü  <method_specifier>：CLR存储过程的标识。assembly_name.class_name.method_name用于指定.NET Framework程序集的方法，以便CLR存储过程引用。

 

【例9.1】（简单的存储过程）创建一个存储过程，它可以输出学生的学号、姓名、平均成绩以及所在系别。

该过程名为“myPro1”，所使用的SQL语句如下：

USE MyDatabase;     -- 设置当前数据库

GO

IF OBJECT_ID('myPro1','P') IS NOT NULL -- 判断是否已存在名为“myPro1”存储过程

    DROP PROCEDURE myPro1;    -- 如果存在则删除，否则无法创建（不是必备代码）

GO

CREATE PROCEDURE myPro1   -- 定义存储过程myPro1

AS

    SELECT s_no, s_name, s_avgrade, s_dept

    FROM student;

GO 

 

    在SQL Server Management Studio中编写上述代码，然后运行此代码即可在服务器端生成存储过程myPro1，此后就可以调用此存储过程。

 

 

Ø  调用一个存储过程，一般是用EXECUTE（或EXEC）语句来完成。但也可以直接将过程名当作一个条命令来执行。

【例子】对于上面定义的过程myPro1，以下三种执行方式都是有效且等价的：

 

•    myPro1;             -- 这种没有EXECUTE或EXEC的执行方式必须位于批处理中的第一条语句

•    EXEC myPro1;        -- 这种格式通常用于嵌入到其他语言中

•    EXECUTE myPro1;     -- 这种格式通常用于嵌入到其他语言中

 

 

 

 

 

 

 【例9.2】（带参数的存储过程）对于例9.1，进一步要求能够按照成绩段来查询学生的相关信息。满足本例要求的存储过程需要带参数，用于界定成绩段。该存储过程定义的代码如下：

USE MyDatabase;

GO

CREATE PROCEDURE myPro2    -- 定义带两个参数的存储过程

    @mingrade numeric(3,1) = 60,     -- 参数@mingrade的默认值为60

    @maxgrade numeric(3,1)    -- 参数@maxgrade没有设置默认值

AS

    -- 查询平均成绩在@mingrade到@maxgrade之间的学生信息

    SELECT s_no, s_name, s_avgrade, s_dept 

    FROM student

    WHERE s_avgrade>= @mingrade AND s_avgrade <= @maxgrade; 

GO

 

 

Ø  上述存储过程带有两个参数，所以调用该过程时必须为之指定相应的参数值。对于有默认值的参数，如果不指定参数值，则使用默认值，但调用格式要正确。例如，对于存储过程myPro2，可通过执行下列语句来查询平均成绩在60到90分之间的学生信息（它们都是等价的）：

EXEC myPro2 60, 90;

EXEC myPro2 @mingrade = 60, @maxgrade = 90;

EXEC myPro2 @maxgrade = 90, @mingrade = 60;

EXEC myPro2 @maxgrade = 90; -- 参数@mingrade使用默认值60

 

 

      但如果试图使用下列方式来执行过程myPro2，则是错误的或与题意相背：

 

EXEC myPro2 90;       -- 错误的调用格式，少了一个参数

EXEC myPro2 90, 60;  -- 能成功调用，但与题意相背

 

 

 

 

 

 

【例9.3】（带通配符参数的存储过程）创建一个存储过程，使之能够按照姓名模糊查询并列出学生的学号、姓名和平均成绩；如果在调用时不带参数，则列出所有学生的相关信息。

该存储过程使用带通配符的方法来实现，其代码如下：

 

USE MyDatabase;

GO

CREATE PROCEDURE myPro3          

    @s_name varchar(8) = '%'

AS 

    SELECT s_no, s_name, s_avgrade, s_dept 

    FROM student

    WHERE s_name LIKE @s_name; 

GO

 

 

 

 

Ø 调用该过程时，如果带参数值则按姓名进行模糊查询，如果不带参数值则列出所有学生的相关信息。

 【例子】下列语句将列出所有的姓“王”的学生信息：

EXEC myPro3 '王%';  

 

Ø 而执行下列语句后则列出所有学生的学号、姓名和平均成绩：

EXEC myPro3;

 

 

    【例9.4】（带OUTPUT参数的存储过程）创建一个存储过程，使之能够求出所有学生成绩的总和以及女学生成绩的总和。

      OUTPUT参数可以从存储过程中“带回”返回值，因此利用OUTPUT参数可以让存储过程具有返回值功能。

     本例中的存储过程要求有两个返回结果，因此在定义存储过程时需要声明带两个OUTPUT参数。

       定义该过程的代码如下：

 

USE MyDatabase;

GO

CREATE PROCEDURE myPro4      

    @ s_total real OUTPUT,                                  --声明OUTPUT参数

    @ s_total _female real OUTPUT,           --声明OUTPUT参数

AS

    SELECT @ s_total =SUM(s_avgrade)                     --求所有学生成绩总和

    FROM student;

    SELECT @ s_total _female =SUM(s_avgrade)              --求女学生成绩总和

    FROM student

   WHERE s_sex=‘女’

GO

 

 

 

 

Ø  对于带OUTPUT参数的存储过程，其调用方法与其他的存储过程的调用方法有所不同。首先要声明相应的变量来存放返回结果，然后在调用过程的时候要带关键字OUTPUT，否则无法将返回结果保存下来。

        【例子】要获取存储过程myPro4返回的结果并打印出来，相应的代码如下：

 

        DECLARE @total real, @total_female real;

        EXEC myPro4 @total OUTPUT, @total_female OUTPUT;  -- 调用时要带关键字OUTPUT

        print @total;

        print @total_female;

 

 

 

 

【例9.5】（加密存储过程）创建一个加密的存储过程。

Ø 加密存储过程是指在存储过程被创建后对保存在服务器端的过程文本代码进行加密，从而无法使用文本编辑器来查看代码。

Ø 加密存储过程的方法很简单，只要在定义时使用WITH ENCRYPTION子句即可。以下是一个加密存储过程的定义代码：

USE MyDatabase;        

GO

CREATE PRO CEDURE myPro5   WITH ENCRYPTION          

AS

    SELECT s_no, s_name, s_avgrade, s_dept

    FROM student;

GO

 

Ø 一个存储过程的定义文本可以用系统存储过程sp_helptext来查看。但执行下列语句后，会显示对象已加密的信息，这表示myPro5的定义文本已经被加密：

EXEC sp_helptext myPro5;

 

 

 

9.1.4 存储过程的修改和删除

1. 修改存储过程

Ø  存储过程的修改可用ALTER PROCEDURE语句来实现，修改后用户对该存储过程拥有的权限并没有发生改变。

Ø ALTER PROCEDURE语句的语法如下：

ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
    [ { @parameter [ type_schema_name. ] data_type }
    [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS
     { <sql_statement> [ ...n ] | <method_specifier> }
<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE_AS_Clause ]
<sql_statement> ::= { [ BEGIN ] statements [ END ] }
<method_specifier> ::= EXTERNAL NAME assembly_name.class_name.method_name

 

 注意：如果原来的存储过程在定义时使用了WITH ENCRYPTION或WITH RECOMPILE选项，那么只有在ALTER PROCEDURE语句中也选择了这些选项，这些选项才有效；另外，使用ALTER PROCEDURE修改后，原过程的权限和属性将保持不变。

 

 

Ø  【例9.6】  对例9.3创建的存储过程myPro3进行修改，使之能够按照姓名（s_name）或系别（s_dept）进行查询。

该修改操作可用下列的ALTER PROCEDURE来实现。

ALTER PROCEDURE myPro3                

    @s_name varchar(8) = '赵%',

    @s_dept varchar(50) = '%'

AS 

    SELECT s_no, s_name, s_avgrade, s_dept 

    FROM student

    WHERE s_name LIKE @s_name OR s_dept LIKE @s_dept;

GO

 

       修改后的过程与原来过程的权限完全一样。不同的是它除了可以按姓名查询外，还可以按系别查询。

Ø  在SSMS中修改存储过程的方法是，在对象资源管理中右击要修改的存储过程所对应的节点，并在弹出的菜单中选择“修改”命令，然后在打开的查询编辑器窗口中修改过程的定义代码即可。但对加密存储过程，则无法用这种方法修改。

 

 

2. 删除存储过程

Ø 当一个存储过程不再使用时，就应该将它从数据库中删除。删除一个存储过程的SQL语句是DROP PROCEDURE。实际上，在前面介绍的例子中已经多次用到。其语法如下：

DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ]

 

     从该语法中可以看出，一条DROP PROCEDURE语句可以同时删除一个或多个存储过程。

 【例子】同时删除过程myPro1, myPro2, myPro3，可使用下列语句来完成：

DROP PROCEDURE myPro1, myPro2, myPro3;

 

 

Ø 也可以在SSMS中删除一个存储过程。方法是：在对象资源管理器中右击要删除的存储过程所对应的节点，然后在弹出的菜单中选择“删除”命令，最后根据提示删除存储过程。

Ø 注意，当一个存储过程被删除以后，所有用户对其拥有的操作权限也将全部被删除。

 

•    9.2 触发器

 

9.2.1 关于触发器

Ø 触发器是数据库服务器中发生事件时自动执行的一种特殊的存储过程。与一般存储过程不同的是，触发器不是被调用执行，而是在相应的事件发生时激发执行的，并且不能传递参数和接受参数。它与数据表关系密切，一般用于实现比较复杂的数据完整性规则、检查数据的有效性、实现对用户操作和数据状态的实时监控、实现数据库的一些管理任务和其他的一些附加功能等。

 

Ø 触发器执行的前提是要有相应事件的发生，这些事件主要是针对数据表。在SQL语言中，引发事件的语句主要是DML和DDL语句，因此又有DML事件和DDL事件、以及DML触发器和DDL触发器之称。另外，自从SQL Server 2005开始，SQL Server增加了一类新的触发器——LOGON触发器（登录触发器）。利用登录触发器可以实现对登录用户的锁定、限制和跟踪等。

 

 

1. DML触发器

Ø 数据库操纵语言（DML）主要包含INSERT、UPDATE、DELETE等语句。这些语句作用于数据表或视图的时候，将产生相应的事件——DML事件。此类事件一旦发生可引起相关触发器的执行，因此这类事件通常称为DML事件，相应的触发器称为DML触发器。也可以这样理解，DML触发器是在运行DML语句时由于产生DML事件而被执行的一类触发器。

 

Ø 根据触发器的执行与触发事件发生的先后关系，又可以将DML触发器分为AFTER触发器和INSTEAD OF触发器。

（1）AFTER触发器：在DML触发事件发生后才激发执行的触发器，也就是说，先执行INSERT、UPDATE、DELETE语句然后才执行AFTER触发器。这类触发器只适用于数据表，不适用于视图。AFTER触发器一般用于检查数据的变动情况，以便采取相应的措施。例如，如发现错误，将拒绝或回滚更改的数据；

 （2）INSTEAD OF触发器：“INSTEAD OF”的中文意思就是“代替”之意，由此不难理解：INSTEAD OF触发器是在DML触发事件发生之前（即数据被更新之前）执行的，这种执行将代替DML语句的执行。也就是说，INSTEAD OF触发器是在DML触发事件发生之前执行，并且取代相应的DML语句（INSERT、UPDATE或DELETE语句），转而去执行INSTEAD OF触发器定义的操作（此后不再执行此DML语句）。INSTEAD OF触发器既适用于数据表，也适用于视图。但对同一个操作只能定义一个INSTEAD OF触发器。

  

  如果根据触发事件的类型划分，DML触发器通常又可以分为INSERT触发器、UPDATE触发器和DELETE触发器：

Ø INSERT触发器：执行INSERT语句而激发执行的触发器；

Ø UPDATE触发器：执行UPDATE语句而激发执行的触发器；

Ø DELETE触发器：执行DELETE语句而激发执行的触发器。

 

 

2. DDL触发器

Ø DDL触发器是一种由执行DDL语句产生触发事件而触发执行的触发器。DDL语句包括CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和UPDATE STATISTICS 等语句。

Ø 与DML触发器不同的是，DDL触发器的触发事件是执行DDL语句而引起的事件，这种触发器是在触发事件发生后执行的；而DML触发器的触发事件则是由执行DML语句引起的，可在事件发生前或发生后执行。另外，DDL触发器的作用域不是架构，因而不能使用OBJECT_ID来查询有关DDL触发器的元数据。DDL触发器可用于执行数据库级的管理任务，如审核和规范数据库操作等。

Ø DML触发器的触发事件类型比较简单，主要包括INSERT、DELETE和UPDATE等三种事件。但DDL触发器的触发事件就比较多，表9.1列了出常用的几种事件。记住这几种事件对以后的触发器编程很有帮助。

 

 

 

 

3. LOGON触发器（登录触发器）

Ø 登录触发器是SQL Server 2005开始新增加的一类为响应LOGON事件（登录）而激发执行的触发器。也就是说，只要有用户登录，登录触发器即可激发执行。因此，通过登录触发器可以知道谁登录了服务器以及何时登录的，并可以实现如何跟踪用户的活动，还可以限制特定用户只能在特定时间段登录等。

Ø 触发事件对触发器来说是关键的，所以许多时候又用引发触发事件的SQL语句来对触发器进行分类和命名。

 【例子】  INSERT触发器、DELETE触发器、UPDATE触发器等。但这种分类不是严格的，只是为阐明问题之便。

 

 

9.2.2 创建触发器

1. 创建DML触发器

    创建DML触发器的SQL语法如下：

CREATE TRIGGER [ schema_name. ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement  [ ; ] [ ...n ] | EXTERNAL NAME <method specifier [ ; ] > }
<dml_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]
<method_specifier> ::= assembly_name.class_name.method_name

参数说明如下：

Ø trigger_name：设置触发器的名称，但不能以#或##开头。

Ø schema_name：设置触发器所属架构的名称。

Ø table | view：执行DML触发器的表或视图，也分别称为触发器表或触发器视图。

Ø WITH ENCRYPTION：选择该子句，则表示对触发器文本进行加密。

Ø EXECUTE AS：指定用于执行该触发器的安全上下文，即设置操作权限。

Ø AFTER：表示定义AFTER触发器，即DML触发器在触发事件发生后执行。如果仅指定FOR关键字，则默认使用AFTER。

Ø INSTEAD OF：表示定义INSTEAD OF触发器，即DML触发器在触发事件发生之前执行。

Ø { [DELETE] [,] [INSERT] [,] [UPDATE] }：指定触发事件，如果选择了DELETE，则表示创建DELETE触发器，其他类推。

Ø WITH APPEND：指定添加一个与当前触发器类型相同的另外一个触发器。该子句不适用于INSTEAD OF触发器。该功能在未来中将被删除，建议不要使用。

Ø NOT FOR REPLICATION：该选项用于指示复制代理修改到触发器表时不执行触发器。

Ø sql_statement：SQL语句。

Ø < method_specifier >：只适用于CLR触发器，指定程序集与触发器绑定的方法。

 

 

Ø  如果在CREATE TRIGGER语句中选择了INSERT 、UPDATE或DELETE选项，则表示创建INSERT、UPDATE或DELETE触发器。对于这类触发器（DML触发器），有两种临时表与它们有着密切的联系，它们是表DELETED和表INSERTED。这两种临时表都是在触发器执行时被创建，执行完毕后被删除。对它们的维护和管理是由SQL Server自动完成，用户不能对这两个表进行直接操作。

Ø  具体地，在执行INSERT触发器时创建表INSERTED，执行DELETE触发器时创建表DELETED，执行UPDATE触发器时则同时创建表INSERTED和表DELETED，其中表INSERTED保存了更新的数据记录，表DELETED则保存更新前的数据记录（不受到更新影响的记录不含在其中）。

Ø  SQL Server对这两个表的操作过程如下：

ü 表INSERTED：在执行INSERT或UPDATE语句时，对用于插入或用于更新的数据记录拷贝一个副本，并将该副本保存到表INSERTED中。可见，表INSERTED是触发器表被插入或被更新后的一个子集。

ü 表DELETED：在执行DELETE和UPDATE语句时，将触发器表中被删除或被更新的数据记录拷贝到表DELETED中。可见，表DELETED和触发器表是不相交的（不会含有相同的记录）。

 

 

【例9.7】创建一个触发器，使之拒绝执行UPDATE操作，并输出“对不起，您无权修改数据！”。

Ø 这是一个INSTEAD OF触发器，其实现代码如下：

USE MyDatabase
GO
CREATE TRIGGER myTrigger1
ON student
INSTEAD OF UPDATE
AS
BEGIN
  PRINT '对不起，您无权修改数据！';
END
GO

 

Ø 该触发器的作用是，执行对表student的UPDATE操转为执行触发器myTrigger1（输出“对不起，您无权修改数据！”），而不再执行此UPDATE操作，因此表student中的数据并未受到该UPDATE操作的任何影响。

 

 

【例9.8】假设表student是记录学生的一些注册登记信息，表SC则是记录注册后的学生的选课信息。有的学生试图不注册而直接选课（显然，在实际中是不允许出现这种情况的），因此一个管理系必须能够杜绝这一点。这就涉及到两个表之间的约束问题，以下定义一个AFTER触发器来实现这种约束。

USE MyDatabase
GO
CREATE TRIGGER myTrigger2 ON SC
AFTER INSERT
AS
BEGIN
  DECLARE @s_no char(8), @n int;
  SELECT @s_no = P.s_no             -- 将正在插入的记录的s_no字段值保存在@s_no中
  FROM SC AS P INNER JOIN INSERTED AS I 
  ON P.s_no = I.s_no;
SELECT @n = COUNT(*)                 -- 在表student中查找是否有s_no字段值等于@s_no的学生
  FROM student
  WHERE s_no = @s_no;  
IF @n <> 1
  BEGIN
     RAISERROR (‘该学生没有注册，选课无效。', 16, 1);
     ROLLBACK TRANSACTION;    -- 回滚（撤销前面的插入操作）
   END
   ELSE PRINT '成功插入数据';
END
GO

 

Ø  上述触发器通过使用内查询来找出当前插入记录的s_no字段值，并将该值保存在变量@s_no中。其中，使用了临时表INSERTED，该表包含了INSERT语句中已插入的记录。然后根据变量@s_no的值在表student中进行查询，如果存在这样的学生则函数COUNT(*)的值为1，否则为0。最后根据函数COUNT(*)的值来决定插入的选课信息是否有效。

 

【例9.9】出于某种原因（如学生因退学、出国而取消学籍），有时候需要将表student中的记录删除，这时也应该将表SC中对应学生的选课信息删除，以保持数据库的完整性。这种完整性的保持可以通过定义如下的AFTER触发器来实现。

USE MyDatabase
GO
CREATE TRIGGER myTrigger3 ON student
AFTER DELETE
AS
BEGIN
  DECLARE @s_no char(8);
  SELECT @s_no = I.s_no        
  FROM DELETED AS I;
  DELETE FROM SC
  WHERE s_no = @s_no;
END
GO

 

      该触发器的作用就是，当在表student中删除某一条件记录时，表SC中与该记录对应的记录（字段s_no值相同的记录）将自动被删除，以保持两个表中数据的参照完整性。

 

 

2. 创建DDL触发器

Ø 创建DDL触发器的语法如下：

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }
<ddl_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]
<method_specifier> ::= assembly_name.class_name.method_name

 

 

Ø 其参数意义基本同DML触发器。此外，其特有的参数包含以下几种：

ü DATABASE：将触发器的作用域指定为当前数据库，这时只要数据库中出现event_type或event_group，就会激发该触发器。

ü ALL SERVER：将触发器的作用域指定为当前服务器，这时只要在服务器上出现event_type或event_group即可激发该触发器。

ü event_type | event_group：分别为SQL语言事件的名称和事件分组的名称。

 

 

 【例9.10】创建一个触发器，用于禁止在当前数据库中删除任何的数据表。

Ø 该触发器是一个DDL触发器，其作用范围是整个数据库。其实现代码如下：

USE MyDatabase
IF EXISTS (SELECT * FROM sys.triggers
   WHERE parent_class = 0 AND name = 'myTrigger4')
DROP TRIGGER myTrigger4 ON DATABASE         -- 删除已存在的同名触发器
GO
CREATE TRIGGER myTrigger4
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
   PRINT '禁止删除或修改数据库中的任何数据表!';
   ROLLBACK;
GO

 

Ø 由于DDL触发器不能使用OBJECT_ID来查询有关DDL触发器的元数据，所以只能通过查询系统数据表sys.triggers的方法来判断触发器是否存在。

Ø 该触发器创建以后，发出删除或修改数据表的任何命令都是被禁止的。

 

 

 【例9.11】  创建一个触发器，使之能够禁止在服务器上创建任何服务器登录。

Ø 该触发器的作用范围是整个服务器，其实现代码如下：

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'myTrigger5')
DROP TRIGGER myTrigger5       -- 删除已存在的同名触发器
ON ALL SERVER
GO
CREATE TRIGGER myTrigger5
ON ALL SERVER
FOR CREATE_LOGIN  
AS
    PRINT '禁止创建服务器登录。'   
    ROLLBACK;
GO

 

 

 

9.2.3 修改触发器

Ø 触发器的修改是由ALTER TRIGGER语句来完成。但修改不同类型的触发器，ALTER TRIGGER语句的语法是不相同的。

Ø 以下分别是修改DML触发器和DDL触发器的SQL语句：

ALTER TRIGGER schema_name.trigger_name      -- 修改DML触发器
ON ( table | view )
[ WITH <dml_trigger_option> [ ,...n ] ]
( FOR | AFTER | INSTEAD OF )
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME <method specifier> [ ; ] }
<dml_trigger_option> ::= [ ENCRYPTION ] [ <EXECUTE AS Clause> ]
<method_specifier> ::= assembly_name.class_name.method_name

ALTER TRIGGER trigger_name        -- 修改DDL触发器
ON { DATABASE | ALL SERVER }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type [ ,...n ] | event_group }
AS { sql_statement [ ; ] | EXTERNAL NAME <method specifier> [ ; ] }
}
<ddl_trigger_option> ::= [ ENCRYPTION ] [ <EXECUTE AS Clause> ]
<method_specifier> ::= assembly_name.class_name.method_name

 

 

Ø  其中涉及的参数与触发器定义语法中的参数一样。注意，不能为DDL触发器指定架构schema_name。

Ø  修改触发器的优点：

     主要是用户拥有对它的操作权限不会因为对触发器的修改而发生改变。另外，如果原来的触发器定义时使用WITH ENCRYPTION或WITH RECOMPILE选项创建的，只有在ALTER TRIGGER中也包含这些选项时，这些选项才有效。

 

 

【例9.12】希望修改例9.11中创建的触发器myTrigger5，使之由“禁止创建服务器登录”改为“禁止创建数据库”。

对于这个修改操作，我们可以用下面的ALTER TRIGGER语句来完成：

ALTER TRIGGER myTrigger5
ON ALL SERVER
FOR CREATE_DATABASE  
AS
    PRINT '禁止在服务器上创建数据库。'   
    ROLLBACK;
GO

 

 

 

9.2.4 禁用和删除触发器

1. 禁用和启用触发器

Ø 有时候（特别是在调试阶段）我们并不希望频繁地触发执行一些触发器，但又不能将之删除，这时最好先禁用这些触发器。

Ø 禁用一段时间以后，一般还需重新启用它，这又要涉及到触发器启用的概念。

Ø 以下分别是禁用和启用触发器的SQL语法：

DISABLE TRIGGER { [ schema. ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

 

u 参数说明如下：

Ø trigger_name：要禁用的触发器的名称。

Ø schema_name：触发器所属架构的名称。但DDL触发器没有架构。

Ø ALL：如果选择该选项，则表示对定义在ON子句作用域中的所有触发器全部禁用。

Ø object_name：触发器表或视图的名称。

Ø DATABASE：将作用域设置为整个数据库。

Ø ALL SERVER：将作用域设置为整个服务器。

 

 

【例9.13】对例9.12修改后得到的用于禁止在当前服务器中创建数据库的DDL触发器myTrigger5，我们可以使用列的SQL语句来禁用它。

DISABLE TRIGGER myTrigger5 ON ALL SERVER;

 

  如果将上述语句中的“myTrigger5”改为“ALL”，则表示禁用所有定义在服务器作用域中的触发器。这样就可以避免一个一个地去执行禁用操作。

Ø  以下是启用触发器myTrigger5的语句：

ENABLE TRIGGER myTrigger5 ON ALL SERVER;

Ø  如果要启用所有定义在服务器作用域中的触发器，则可以使用下列的语句来完成：

ENABLE TRIGGER ALL ON ALL SERVER;

 

 

 

【例9.14】以下定义了一个DML触发器，它不允许对表student进行更新操作：

USE MyDatabase
GO
CREATE TRIGGER myTrigger6
ON student
INSTEAD OF UPDATE
AS
BEGIN
   RAISERROR ('对表student进行更新！', 16, 10)
   ROLLBACK;
END
GO

 

Ø  如果要禁用该DML触发器，则可以用下列的DISABLE TRIGGER语句来完成：

DISABLE TRIGGER myTrigger6 ON student;

 

 Ø  重新启用myTrigger6则用下列语句完成：

ENABLE TRIGGER myTrigger6 ON student;

 

 Ø  如果将上面语句中的“myTrigger6”改为“ALL”，则表示启用所有作用在表student上的触发器。

 

2. 删除触发器

Ø 当确信一个触发器不再使用时，应当将之删除。DML触发器和DDL触发器的删除方法有所不同，以下分别是删除这两种触发器的SQL语法：

DROP TRIGGER schema_name.trigger_name [ ,...n ] [ ; ]  -- 删除DML触发器
DROP TRIGGER trigger_name [ ,...n ] ON { DATABASE | ALL SERVER } [ ; ] -- 删除DDL触发器

 

Ø 在删除DDL触发器时需要指定触发器名称和作用域（即，是DATABASE还是ALL SERVER），而删除DML触发器时则只需指定其名称。