第九章 存储过程与触发器
本章涉及两方面的内容：存储过程与触发器。存储过程是存储在服务器上的一组预编译的SQL语句，可以在服务器上的SQL Server环境下运行；而触发器是一种特殊类型的存储过程。
本章将主要介绍以下内容：
n 存储过程概述
n 存储过程的使用和管理
n 触发器概述
n 触发器的使用与管理
9.1 存储过程概述
存储过程是一种把重复的任务操作封装起来的一种方法，支持用户提供的变量，可以返回、修改值，也可以将用户提供的信息和系统预请示的信息进行比较。具有非常强大的功能。
使用存储过程有以下优点：
n 性能：存储过程运行在SQL Server服务器上，因为服务器一般都是功能和性能非常良好的机器，所以执行时间比较短。
n 一致性：存储过程与其它应用程序共享逻辑，可以确保访问的一致性。另外，在存储过程中可以封装企业的功能模块，这种功能模块也称为商业规则或者商业策略。所有的客户机可以使用同一个存储过程进行各种操作，从而最大程度地保证数据的一致性。
n 安全性：可以使用存储过程来加强系统操作的安全性。可以创建存储过程来完成所有增加、删除和修改操作。如果用户被赋予执行存储过程的权限，即使用户没有对在存储过程中所参考的表或者视图进行操作的权利，也可以执行该存储过程。
n 减少网络交通：如果在服务器和客户机之间传送1000条SQL Server语句，可能需要很长时间，但是如果把这1000条SQL Server语句写成一条比较复杂的存储过程，就会大大减少服务器和客户机之间的网络传输时间。
n 客户机/服务器系统：使用存储过程可以将服务器和客户机的开发任务分离，有时候可以减少项目的开发周期。
在Microsoft SQL Server 2000中，有五种存储过程：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。下面分别介绍这几种存储过程。
系统存储过程存储在master数据库中，前缀为sp_，为检索系统表信息提供快捷方式。系统存储过程允许系统管理员执行修改系统表的系统管理任务。
本地存储过程是创建在用户自己数据库中的存储过程。这种存储过程是用户创建的普通数据库，没有前缀sp_。
临时存储过程是一种特殊的本地存储过程。如果在本地存储过程前面有一个“#”号，这种存储过程称为局部临时存储过程，只能在一个用户会话中使用；如果在本地存储过程前面有一个“＃＃”号，那么该存储过程称为全局临时存储过程，可以在所有用户会话中使用。
远程存储过程指远程服务器上的存储过程，也就是非本地服务器上的存储过程。
扩展存储过程指的是SQL Server环境外执行的DLL动态链接库，前缀为xp_。
9.2 存储过程的使用与管理
9.2.1 创建存储过程
在创建存储过程之前，首先应该测试用来创建存储过程的Transact-SQL语句，如果得到预期的结果，就可以创建存储过程。
@ 只能在当前数据库中创建存储过程。
可以使用以下两种方法创建存储过程：
n 使用SQL Server Enterprise Manager。
n 使用Transact-SQL语句创建存储过程。
在创建存储过程的时候，需要考虑以下因素：
n 为了避免存储过程的所有者和存储过程基表的所有者不同，推荐在数据库中使dbo用户拥有数据库的全部对象。
n 每个存储过程应该完成一项单独的工作。
n 一般情况下，存储过程都是在服务器上创建、测试和调试，在客户机上使用存储过程的时候，还应该进行测试。
n 可以在创建存储过程的时候使用WITH ENCRYPTION选项防止别的用户看到自己所编写存储过程的文本。如果在创建存储过程的时候没有使用WITH ENCRYPTION选项，可以使用系统存储过程sp_helptext查看系统表syscomment中存储的文本。
n 在使用存储过程的时候使用SET选项设置可以进行特殊选项的设置。
1．使用SQL Server Enterprise Manager
可以使用SQL Server Enterprise Manager创建一个新的存储过程，使用SQL Server Enterprise创建存储过程的步骤如下：
（1）运行SQL Server Enterprise Manager。
（2）选择Tools菜单，单击Wizard，打开选择向导对话框。
（3）单击Database左边的“＋”，展开其子菜单，如图9-1所示，双击Create Stored Procedure Wizard，启动创建存储过程向导，如图9-2所示。
图9-1 选择向导
图9-2 创建存储过程向导
（4）单击“下一步”按钮，打开如图9-3所示的对话框，在该对话框中选择要创建的存储过程所在的数据库。
（5）选择完成后单击“下一步”按钮，打开如图9-4所示的对话框，在该对话框中选择存储过程需要执行的操作，选择完成后单击。
（6）选择完成后，单击“下一步”按钮，打开如图9-5所示的对话框，在该对话框中显示了该存储过程的一些设置，如果需要修改，单击“上一步”按钮，单击“完成”按钮完成存储过程的创建。
图9-3 选择数据库
图9-4 选择存储过程
图9-5 完成存储过程创建
2．使用Transact-SQL语句创建存储过程
可以使用CREATE PROCEDURE语句创建存储过程。在默认情况下，执行所创建的存储过程的许可权归数据库的所有者。在创建存储过程的时候，需要考虑以下因素：
n 存储过程可以参考表、视图或者存储过程，也可以参考临时表。
n 在一个批处理语句中，一个CREATE PROCEDURE不能和其它Transact-SQL语句一起使用。
n 如果使用存储过程创建了临时表，该临时表只能用于存储过程，当存储过程执行完毕后，临时表消失。
n 在CREATE PROCEDURE语句中，可以包含任意数量和类型的Transact-SQL语句，但是不能包含以下语句：
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TRIGGER
CREATE VIEW
n 只有具有CREATE PROCEDURE权限的成员或者以下角色才可以执行CREATE.PROCEDURE语句。
System Adminstration（sysadmin）
Database Owner（db_owner）
Data definition language administrator（db_ddladmin）
n 存储过程支持嵌套，最多可以嵌套32层。当前的存储过程的数据存储在全局变量＠＠nestlevel中。假设有两个存储过程A和B，如果A存储过程调用B存储过程，那么B存储过程就可以调用A存储过程所创建的全部对象。
CREATE PROCEDURE的语法形式如下：
CREATE PROC[EDURE]procedure_name[:number]
[({[@paraneter data_type][=default][OUTPUT]})]
[，…n]
[WITH {RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}]
AS
Sql_statement
下面这个例子创建了一个存储过程，列出了数据库中在1993-5-29以后的订单：
USE pubs
GO
CREATE PROC dbo.books
AS
SELECT *
FROM sales
WHERE ord_date>‘1993-5-29’
GO
在存储过程创建之后，用户可以根据需要查询存储过程的信息，有如下两种方法：
n 使用SQL Server Enterprise Manager。
n 使用系统存储过程查询系统表。
在SQL Server Enterprise Manager窗口中，单击要查看的存储过程所在数据库左边的“＋”将其展开，然后单击Stored Procedures，在右边的窗格中列出了在该数据库中的所有存储过程，如图9-6所示，可以查看存储过程的名称、所有者、类型和创建时间等信息。
图9-6 存储过程信息
找到需要查看信息的存储过程，然后单击鼠标右键，弹出如图9-7所示的快捷菜单，包括七个菜单选项：New Stored Procedure、所有任务、复制、删除、重命名、属性和帮助的常规管理任务，比如管理存储过程的许可、创建出版物、生成用来产生存储过程的SQL脚本文件和显示各种对象信息。
图9-7 存储过程快捷菜单
“复制”用来复制存储过程，“删除”用来删除存储过程，使用“重命名”可以重新命名存储过程。
单击“属性”菜单命令，可以打开如图9-8所示的属性对话框。在该对话框中，显示了存储过程的名称、所有者、创建日期和Transact-SQL语句文本。
图9-8 存储过程属性对话框
单击Permissions按钮可以查看该存储过程的许可信息；单击Check Syntax按钮可以检查创建存储过程的Transact-SQL语句语法的正确性；单击OK按钮，确认所作的修改并关闭属性对话框；单击Cancel按钮取消所作的修改并关闭对话框；单击Apply按钮确认所作的修改但不关闭对话框；单击Help按钮查看相关的帮助信息。
9.2.2 修改存储过程
在存储过程创建之后，用户可能需要对存储过程进行修改。可以使用ALTER PROCEDURE语句用新的存储过程定义替换以前的存储过程定义。
ALTER PROCEDURE的语法形式如下：
ALTER PROC[EDURE]procedure_name[:number]
[({[@]parameter data_type}[=default][OUTPUT])]
[，…n]
[WITH [RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION）]
AS
Sq1_statement
使用ALTER PROCEDURE修改存储过程的时候，需要考虑以下因素：
n 在CREATE PROCEDURE语句中使用的选项，也必须在ALTER PROCEDURE语句中使用。
n 使用ALTER PROCEDURE只能修改一个存储过程，如果该存储过程又调用了其它存储过程，被调用的存储过程不被修改。
n 只有存储过程的创建者和db_owner、db_ddladmin的成员才能使用ALTER PROCEDURE语句修改存储过程。
下面的例子修改存储过程books，使存储过程只显示指定的列。
USE pubs
GO
ALTER PROC books
AS
SELECT CONVERT（char(8)，old_date，1），old_num，stor_id
FROM sales
ORDER BY ord_date
GO
9.2.3 删除存储过程
当存储过程不再需要时，可以用DROP PROCEDURE语句删除存储过程。在执行删除存储过程的操作之前，应该先执行存储过程sp_depends来确定与该存储过程相关联的对象。
删除存储过程的语法形式如下：
DROP PROC[EDURE]stored procedure[，…n]
下面的例子删除了存储过程books：
USE pubs
GO
DROP PROC books
9.2.4 执行存储过程
如果具有执行存储过程的权限EXECUTE，就可以执行存储过程。有以下两种执行存储过程的方法：
n 直接执行存储过程。
n 使用INSERT语句执行存储过程。
可以使用EXECUTE语句直接执行存储过程。EXECUTE语句的语法形式如下：
[EXEC[UTE]]
{
[@return_status=]
{procedure_name[;number]|@procedure_name_var
}
[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]|[，…n]}
[WITH RECOMPILE]
下面这个例子执行存储过程books：
EXEC books
另外，也可以使用INSERT语句执行存储过程。这是SQL Server把SELECT语句返回的数据加载到一个已经存在的并且定义了与存储过程返回的数据类型相同的数据类型。下面的例子假设sales_ok表已经存在，并且它所定义的数据类型与存储过程books返回的数据类型相匹配，该例子创建了一个可以在INSERT语句中使用的存储过程。
USE pubs
GO
CREATE PROC dbo.books
AS
SELECT
ord_num.ord_date，payterms
FROM sales
WHERE WHERE ord_date>‘1993-5-29’
GO
执行该存储过程的语句如下：
INSERT into sales_ok
EXEC books
9.2.5 扩展存储功能
可以通过在存储过程中使用参数来扩展存储过程的功能。通过使用参数可以使存储过程与外部进行通信，也就是说，可以把外部信息通过参数传输到存储过程，也可以把存储过程内的信息通过参数传输到外部。
使用输入参数可以把外部信息传输到存储过程中，为了使用输入参数，必须在用CREATE PROCEDURE语句创建存储过程的时候定义一个或多个变量。声明参数的语法形式如下：
＠parameter data_type[=default]
在声明变量的时候，应该考虑以下因素：
n 在存储过程中，参数最多可以为255个。
n 在创建存储过程的时候对所有参数进行检查。
n 可以为参数指定一个默认值，该默认值可以是常数或者NULL。
n 在不同的存储过程中，可以使用名称相同的变量，但是可以具有不同的含义。可以使用以下两种方法将参数值传送到存储过程中：
n 根据参数的名称指定。
n 根据参数的位置指定。
根据参数的名称指定输入参数就是在EXECUTE语句中用格式＠parameter=value来指定输入参数。其语法形式如下：
[EXECUTE]procedure_name
[@parameter=value]
[@parameter=value]
根据参数位置来指定输入参数就是仅提供数值。需要注意的是所提供的数值顺序必须与在创建存储过程语句中的参数顺序一致。根据参数来指定输入参数的语法形式如下：
[EXECTUE]prcedure_name[value[，value]…]。
9.3 触发器概述
触发器是一种特殊类型的存储过程。触发器和表紧密相连，用户的操作影响到触发器保护的数据时，触发器就会自动发生。一般，称触发器所在的表为触发器表。
触发器为应用程序开发人员和数据库管理人员提供保证数据完整性（关于数据完整性在后面的章节将会介绍）的方法，对于有较多应用程序访问的数据库，可以使用触发器来推行商业规则而不信赖应用程序。
@ 触发器虽然是一种特殊的存储器，但是它不能被直接调用，而存储过程可以直接调用。
一般情况下，对表的操作主要有插入数据、修改数据和删除数据，所以，触发器也有三种类型：INSERT、UPDATET、DELETE。当向表中插入数据的时候，如果该表中有INSERT触发器，INSERT触发器就会自动执行；如果表中有UPDATE触发器，在对表中的数据进行修改时，触发器就会执行；；如果表中有DELETE触发器，在对表中的数据进行删除的时候，触发器就会执行。
触发器主要用来维护行级数据的完整性，因为触发器可以包含比较复杂的逻辑过程。另外，也可以使用触发器修改数据库中相关表中的数据。
在使用触发器的时候，需要考虑以下因素：
n 表的所有者必须有执行全部在触发器中定义的语句的权限。也只有表的所有者才可以对该表的触发器进行创建和删除等操作。
n 只可以在表上创建触发器，不能在视图和临时表中创建触发器。
n 在Microsoft SQL Server系统中，允许在一个表中嵌套使用多个触发器，一个触发器可以定义一种或者多种操作。
n 触发器是在操作发生之后执行的，比如，在DELETE语句删除表中的一行数据之后，该表的触发器就会自动启动。
n 触发器不返回结果集，但是触发器包含一组Transact-SQL语句，这些语句可以返回结果集。
9.4 触发器的使用与管理
9.4.1 创建触发器
只有数据库得所有者才可以创建触发器，因为当为表列、表行或者表增加触发器的时候，将会改变表的访问方式以及与其它对象的关系。所以，只能将触发器的操作权利保留在数据库所有者那里，以避免别人修改数据库的系统结构。
可以使用CREATE TRIGGER语句创建触发器，在该语句中，指定了定义触发器的基表、触发器执行的时间和触发器的所有指令。其语法形式如下：
CREATE TRIGGER[owner.]trigger_name
ON[owner.]table_name
[WITH ENCRYPTION]
{FOR{INSERT|UPDATE|DELETE}
AS
[IF UPDATE（column_name）……]
[{AND|OR}UPDATE（column_name）……]
Sqi_statements}
其中，各个选项的含义如下：
n trigger_name：触发器名称，必须符合SQL Server命名规则。
n INSERT，UPDATE，DELETE：定义触发器的范围，也就是初始化触发器。
n WITH ENCRYPTION：如果选择该选项，当触发器文本装载到服务器的时候，SQL Server系统会阻止其它没有密码的用户阅读该文本。
只有下面的用户才具有创建触发器的权利：
n 表的所有者。
n database owner(db_owner）角色成员。
n System Administration(sysadmin)角色成员。
另外，以下SQL Server语句不能用在触发器中：
n 所有CREATE语句，比如CREATE TABLE、CREATE DATABASE、CREATE VIEW、CREATE INDEX等。
n 所有的DROP语句。
n ALTER TABLE和ALTER DATABASE语句。
n TRUNCATE TABLE语句。
n GRANT和REVOKE语句。
n UPDATE STATICS语句。
n RECONFIGURE语句。
n LOAD、RESTORE DATABASE和LOG。
n 所有的DISK语句。
n SELECT INTO语句。
@ TRUNCATE操作被初始化的时候，不初始化DELETE触发器，因为TRUNCATE的操作没有注册。
下面这段代码创建了一个触发器。该触发器向customer表中插入的每一行数据生成一个客户ID。
CREATE TRIGGER test_trigger ON customers FOR INSERT
AS
UPDATE c SET CustomerID=
(SELECT REPLICATE(‘0’，(4(DATALENGTH(CONVERT(varchar(10)，I，id)))))
+CONVERT(varchar(10)，I，id)
+(SUBSTRING(i，CompanyName，1，3)
+SUBSTRING(I，ContactName，1，1)
FROM sustomers c INNER JOIN inserted i ON i.id=c.id)
FROM sustomers c INNER JOIN inserted i ON i.id=c.id
如果触发器不再需要，可以将其删除，可以使用DROP TRIGGER语句删除触发器，语法形式如下：
DROP TIRIGGER trigger_name
@ 当删除与触发器相关的表时，触发器也被删除。
9.4.2 修改触发器
在触发器创建之后，可以根据需要修改触发器定义。可以使用ALTER TRIGGER语句修改触发器，其语法形式如下：
ALTER TRIGGER
ON
[WITH ENCRYPTION]
{
{FOR{[，][DELETE][，][UPDATE][，][INSERT]
[NOT FOR REPLICATION]
AS
Sql_statement[…n]
}
|
{FOR {[，][INSERT][，][UP'DATE]}
[NOT FOR REPLICATION]
AS
IF UPDATE(column)
[{AND|OR{UPDATE(column)[，…n]}
sql_statement[…n]
}
}
下面的例子为表sale创建了一个INSERT操作的触发器sale_insert，当向sale表中插入数据的时候，触发器自动修改表result的列sale_out为Y，表示该书已经售完。
USE libray
GO
CREATE TRIGGER sale_insert
ON sale
FOR INSERT
AS
UPDATE c SET sale_out=‘Y’
FROM result c INNER JOIN inserted i
ON c.isbn=i.isbn AND c.copyID=i.copyID
下面的例子用于检查删除操作，也就是说当新到一批书的时候，修改表中result中的列sale_out为N。
USE liarary
GO
ALTER TRIGGER sale_insert
ON sale FOR INSERT，DELETE
IF EXISTS(SELECT*FROM inserted)
BEGIN
UPDATE c SET sale_out=‘Y’
FROM result c INNER JOIN inserted i
ON c.isbn=i.isbn AND c.copyID=i.copyID
END
ELSE
BEGIN
UPDATE c SET sale_out=‘N’
FROM result c INNER JOIN inserted i
ON c.isbn=i.isbn AND c.copyID=i.copyID
END