存储过程和触发器课件

存储过程和触发器

本章以本书的“图书销售系统”为例，主要介绍了如何在SQLServer2000数据库中建立、修改存储过程和触发器。

10．1存储过程

10.1.1存储过程简介存储过程是一组预先编译好的SQL代码。它是SQLSever上的一个非常有用的特性，是一个独立的数据库对象，可以通过调用命令（EXECUTE）执行存储过程，而不必发送大量Transact-SQL代码，因此大大减少了网络流量，或用户的应用程序调用。存储过程可用于向用户返回数据、向表中插入新数据、修改、执行系统和管理任务等。

存储过程有如下优点：（1）行速度快存储过程在创建时经过语法检查和性能优化，在第一次被调用后就驻留在内存中，下次执行不再经过编译。当某操作需大量使用或重复执行，存储过程将比Transact-SQL代码要快。（2）模块化的程序设计存储过程在创建后，即存储在数据库中，可被应用程序无数次调用。用户可按不同功能的程序模块，设计不同的存储过程以供使用。（3）减少网络流量存储过程是一组Transact-SQL语句，但其是作为一个独立的单元来使用，在调用时，只需一个执行语句即可实现。（4）安全性可以授权无直接执行存储过程语句权限的用户执行该存储过程。（5）可以将复杂的任务划分为多个简单的商业逻辑（6）存储过程对某些需要周期性处理的任务特别有用。（7）存储过程在客户端和服务器端提供了良好的并发性。在下例情况下，将使用到存储过程。（1）不需要任何客户端信息而在服务器端完成的操作（2）涉及到大量行的操作、运算（3）频繁的、周期性的操作（4）那些被多个模块共同执行的操作。10.1.2创建存储过程语法：

CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]

参数解释：

procedure_name

新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。

要创建局部临时过程，可以在procedure_name前面加一个编号符(#procedure_name)，要创建全局临时过程，可以在procedure_name前面加两个编号符(##procedure_name)。完整的名称（包括#或##）不能超过128个字符。指定过程所有者的名称是可选的。

;number

是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。DROPPROCEDUREorderproc语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在procedure_name前后使用适当的定界符。@parameter

过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有2100个参数。使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。【注意】调用存储过程时，必须给出参数值，参数可为空格，但不能为空（NULL）

data_type

参数的数据类型。所有数据类型（包括text、ntext和image）均可以用作存储过程的参数。不过，cursor数据类型只能用于OUTPUT参数。如果指定的数据类型为cursor，也必须同时指定VARYING和OUTPUT关键字。

【说明】对于可以是cursor数据类型的输出参数，没有最大数目的限制。

VARYING

指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。

default

参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或NULL。如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_、[]和[^]）。

OUTPUT

表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可将信息返回给调用过程。Text、ntext和image参数可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。

n表示最多可以指定2100个参数的占位符。

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}RECOMPILE表明SQLServer不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用RECOMPILE选项。

ENCRYPTION表示SQLServer加密syscomments表中包含

CREATEPROCEDURE语句文本的条目。使用ENCRYPTION可防止将过程作为SQLServer复制的一部分发布。

FORREPLICATION

指定不能在订阅服务器上执行为复制创建的存储过程。.使用FORREPLICATION选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和WITHRECOMPILE选项一起使用。

AS指定过程要执行的操作。

sql_statement

过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制。

n是表示此过程可以包含多条Transact-SQL语句的占位符。注释存储过程的最大值为128MB。用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在tempdb中创建）。在单个批处理中，CREATEPROCEDURE语句不能与其它Transact-SQL语句组合使用。默认情况下，参数可为空。如果传递NULL参数值并且该参数在CREATE或ALTERTABLE语句中使用，而该语句中引用的列又不允许使用NULL，则SQLServer会产生一条错误信息。为了防止向不允许使用NULL的列传递NULL参数值，应向过程中添加编程逻辑或为该列使用默认值（使用CREATE或ALTERTABLE的DEFAULT关键字）。1．下面我们以本书的SQLServer数据库“图书销售系统”为例，分别建立一个名为“cunchu”、“cunchu2”的存储过程。【例10.1】本例创建一个名称为“cunchu”的存储过程，该存储过程的作用是根据参数值，返回表BookRecord的相关记录。执行的操作是查询记录。步骤：（1）打开SQL企业管理器，连接服务器，展开数据库列表，选择“图书销售系统”数据库。（2）右击“图书销售系统”数据库，选择“新建”命令中的“存储过程”选项，如图10.1。

图10.1新建存储过程（3）在弹出的“存储过程属性—新建存储过程”对话框中，输入以下语句：

CREATEPROCEDUREcunchu@findBookNochar(30),@findBookNamechar(200)ASselectBookNO,BookName

fromBookRecord

whereBookNO=@findBookNOorBookName=@findBookName

该语句新建了一个名为cunchu的存储过程，参数findBookNo对应字段BookNO，参数findBookName对应字段BookName。图10.2存储过程属性（4）检查语法。生成一个存储过程前，应该执行检查语法操作。方法是点击“检查语法”按钮。我们点击该按钮后，系统将会提示，如成功，则显示“语法检查成功”，否则将显示其错误。语法无误，点击“确定”后，系统将生成该存储过程。【例10.2】本例创建一个名为“cunchu2”的存储过程，该存储过程定义了四个参数，对应表Operator的四个字段。执行的操作是向Operator表添加新记录。步骤：步骤（1）、步骤（2）与例10.2的步骤（1）、步骤（2）相同，在此省略。（3）在弹出的“存储过程属性—新建存储过程”对话框中，输入以下语句：

CREATEPROCEDUREcunchu2@Accountchar(10),@Namechar(20),@Passwordchar(16),@IsdeletedBITASinsertOperatorvalues(@account,@name,@password,@isdeleted)

（4）检查语法。点击“检查语法”按钮后，如成功，则显示“语法检查成功”，否则将显示其错误。语法无误，点击“确定”后，系统将生成该存储过程。

2．执行存储过程：要执行存储过程，需在SQL查询分析器中使用关键字“execute”，该关键字后面是存储过程名，及相关参数。下面我们介绍如何执行刚才新建的两个存储过程。【例10.3】本例介绍执行存储过程“cunchu”的步骤。步骤：（1）打开SQL企业管理器，连接服务器，选择“图书销售系统”。（2）选择“工具”菜单，在弹出的菜单中选择“SQL查询分析器”，弹出如图10.3，输入executecunchu"9787302000001",""，在工具栏中点击“执行查询”按钮，即可见执行结果，如图10.4：

图10.3执行存储过程图10.4执行结果【例10.4】本例介绍执行存储过程“cunchu2”的步骤，并查看其结果。步骤：（1）打开SQL企业管理器，连接服务器，选择“图书销售系统”。（2）点击“工具”菜单，选择“SQL查询分析器”，在查询分析器的文本区域内输入如下语句。

executecunchu2yb,杨斌,yb,0（3）在工具栏中点击“执行查询”按钮，第二个文本框出现提示“所影响的行数为一行”，表示执行存储过程成功。如图10.5。我们可以打开表Operator，查看其结果。如图10.6所示，表中新添加了一条记录。

图10.5执行存储过程成功图10.6查看结果

3．修改存储过程：修改存储过程的方法很简单，选择要修改的存储过程，双击即弹出“存储过程属性”对话框，在文本框中直接修改语句，检查语法通过，点击确定后即生效。

4．删除存储过程：删除存储过程最直接的方法是选择相应数据库，在该数据库的存储过程列表中选择要删除的存储过程，右击鼠标，弹出快捷菜单，选择“删除“即可。如图10.7：图10.7删除存储过程10．2触发器

10.2.1触发器简介触发器是一种特殊的存储过程，其作用是当表中数据被UPDATE、INSERT、DELETE语句修改，或直接在表中数据被直接更改后，触发器就会生效，自动执行触发器中的语句。对每个SQL语句，触发器执行一次，在SQL语句执行完成后它立即启动。如遇到错误，触发器和激活它的SQL语句自动取消。使用触发器的优点：

1．触发器是自动执行的，它在对表的数据作了任何修改之后立即被激活。

2．触发器可以通过数据库中的相关表进行层叠和更改。

3．触发器可以强制执行。

4．触发器可以引用其他表中的列。

使用触发器的限制：

1．一个表中最多可以有三个触发器。一个是“INSERT”触发器，一个是“UPDATE”触发器，还有一个是“DELETE”触发器。

2．一个触发器只能运行一个表，但是可以运行三个用户动作，包括INSERT、UPDATE和DELETE。在下列情况下，将使用到触发器。

1．beforeinsert/update触发器可以检查操作是否完成以及数据的一致性，或者为那些需要唯一编号的列提供数值

2．beforedelete触发器可以在删除时检查主要明细表之间的关系，也可以终止删除。

3．所有的before触发器均可以通过激发异常终止操作回退事务

4．所有的after触发器均可以在更改完成后做一些审计或更改其他相关表的操作。10.2.2创建触发器语法

CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{

{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}

[WITHAPPEND]

[NOTFORREPLICATION]

AS

[{IFUPDATE(column)

[{AND|OR}UPDATE(column)]

[...n]

|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask)

{comparison_operator}column_bitmask[...n]

}]

sql_statement[...n]

}

参数

trigger_name

触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。

Table|view

是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。

WITHENCRYPTION

加密syscomments表中包含CREATETRIGGER语句文本的条目。使用WITHENCRYPTION可防止将触发器作为SQLServer复制的一部分发布。

AFTER

指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。

如果仅指定FOR关键字，则AFTER是默认设置。不能在视图上定义AFTER触发器。

INSTEADOF

指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEADOF触发器。然而，可以在每个具有INSTEADOF触发器的视图上定义视图。

INSTEADOF触发器不能在WITHCHECKOPTION的可更新视图上定义。如果向指定了WITHCHECKOPTION选项的可更新视图添加INSTEADOF触发器，SQLServer将产生一个错误。用户必须用ALTERVIEW删除该选项后才能定义INSTEADOF触发器。

{[DELETE][,][INSERT][,][UPDATE]}

是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。

对于INSTEADOF触发器，不允许在具有ONDELETE级联操作引用关系的表上使用DELETE选项。同样，也不允许在具有ONUPDATE级联操作引用关系的表上使用UPDATE选项。

WITHAPPEND

指定应该添加现有类型的其它触发器。

WITHAPPEND不能与INSTEADOF触发器一起使用，或者，如果显式声明AFTER触发器，也不能使用该子句。只有当出于向后兼容而指定FOR时（没有INSTEADOF或AFTER），才能使用WITHAPPEND。

NOTFORREPLICATION

表示当复制进程更改触发器所涉及的表时，不应执行该触发器。

AS是触发器要执行的操作。

Sql_statement

是触发器的条件和操作。触发器条件指定其它准则，以确定DELETE、INSERT或UPDATE语句是否导致执行触发器操作。

当尝试DELETE、INSERT或UPDATE操作时，Transact-SQL语句中指定的触发器操作将生效。触发器可以包含任意数量和种类的Transact-SQL语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的Transact-SQL语句常常包含控制流语言。CREATETRIGGER语句中使用几个特殊的表：

·

deleted和inserted是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。例如，若要检索deleted表中的所有值，请使用：

·

SELECT*

·

FROMdeleted

n

是表示触发器中可以包含多条Transact-SQL语句的占位符。对于IFUPDATE(column)语句，可以通过重复UPDATE(column)子句包含多列。

IFUPDATE(column)

测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作。可以指定多列。因为在ON子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。若要测试在多个列上进行的INSERT或UPDATE操作，请在第一个操作后指定单独的UPDATE(column)子句。在INSERT操作中IFUPDATE将返回TRUE值，因为这些列插入了显式值或隐性(NULL)值。【说明】IFUPDATE(column)子句的功能等同于IF、IF...ELSE或WHILE语句，并且可以使用BEGIN...END语句块。可以在触发器主体中的任意位置使用UPDATE(column)。

column

是要测试INSERT或UPDATE操作的列名。该列可以是SQLServer支持的任何数据类型。但是，计算列不能用于该环境中。IF(COLUMNS_UPDATED())

测试是否插入或更新了提及的列，仅用于INSERT或UPDATE触发器中。COLUMNS_UPDATED返回varbinary位模式，表示插入或更新了表中的哪些列。

COLUMNS_UPDATED函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。如果在表上创建的触发器包含8列以上，则COLUMNS_UPDATED返回多个字节，最左边的为最不重要的字节。在INSERT操作中COLUMNS_UPDATED将对所有列返回TRUE值，因为这些列插入了显式值或隐性(NULL)值。可以在触发器主体中的任意位置使用COLUMNS_UPDATED。

bitwise_operator

是用于比较运算的位运算符。updated_bitmask

是整型位掩码，表示实际更新或插入的列。例如，表t1包含列C1、C2、C3、C4和C5。假定表t1上有UPDATE触发器，若要检查列C2、C3和C4是否都有更新，指定值14；若要检查是否只有列C2有更新，指定值2。

comparison_operator

是比较运算符。使用等号(=)检查updated_bitmask中指定的所有列是否都实际进行了更新。使用大于号(>)检查updated_bitmask中指定的任一列或某些列是否已更新。

column_bitmask

是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。我们以“图书销售系统”数据库中的表InRecord为例创建三个触发器：

1．INSERT触发器【例10.5】本例创建了一个名为OrdDet_Insert的触发器，该触发器的作用是当有新书入库的时候，则更新库存中该书的库存量。步骤：（1）打开SQL企业管理器，连接服务器，双击“数据库”，展开数据库列表，选择“图书销售系统”数据库。（2）展开表，右击“InRecord”，选择“所有任务”下的子菜单“管理触发器”，如图10.8：图10.8创建触发器(3)在“触发器属性”对话框的文本区域输入以下语句：

CREATETRIGGEROrdDet_Insert

ONInRecord

FORINSERTAS

UPDATEPSETQuantity=P.Quantity+I.Quantity

FROMBookRecordASPINNERJOINInRecordASIONP.BookNO=I.BookNO

（4）单击“检查语法”按钮，无误后单击“确定”命令。2．UPDATE触发器【例10.6】本触发器的名称为OrdDet_Update，当SalesDetail表发生更新的时候，更新BookRecord表的相关记录。步骤：（1）打开SQLServer企业管理器，连接服务管理器，展开数据库列表，选择“图书销售系统”数据库。（2）展开表，右击“SalesDetail”表，选择“所有任务”下子菜单的“管理触发器”。（3）在“触发器属性”对话框的文本区