baidu:触发器 用户 访问
http://dhp3s.blog.sohu.com/9408958.html

   
  select   *   from   另一库.dbo.表   
        
--如果两个库在不 同的sql实例中  
   
  select   *   from   openrowset('sqloledb','另一sql实例名';'sa';'111',另一库.dbo.表)

--访问不同 电脑上的数据库(远程只要联好网就一样)  
   
  --如果经常访问或数据量大,建议用链接服务器  
   
  --创建链接服务器  
  exec   sp_addlinkedserver     'srv_lnk','','SQLOLEDB','远程服务器名或ip地址'  
  exec   sp_addlinkedsrvlogin   'srv_lnk','false',null,'用户名','密码'  
  go  
   
  --查询示例  
  select   *   from   srv_lnk.数据库名.dbo.表名  
   
  --导入示例  
  select   *   into   表   from   srv_lnk.数据库名.dbo.表名  
   
  go  
  --以后不再使用时删除链接服务器  
  exec   sp_dropserver   'srv_lnk','droplogins'  
   
   
  --如果只是临时访问,可以直接用openrowset  
  --查询示例  
  select   *   from   openrowset('SQLOLEDB'  
  ,'sql服务器名';'用户名';'密码'  
  ,数据库名.dbo.表名)  
   
   
  --导入示例  
  select   *   into   表   from   openrowset('SQLOLEDB'  
  ,'sql服务器名';'用户名';'密码'  
  ,数据库名.dbo.表名)   
    

如果你在触发器中访问不同sql实例下的数据,还要注意一个事务的问题.  
   
  必须启动你的sql服务器和你访问的sql服务器上的msdtc服务  
  我的电脑--控制面板--管理工具--服务--右键   Distributed   Transaction   Coordinator--属性--启动,并将启动类型设置为"自动启动"   

---------------------------------------------------

  1、同服务器   select   *   from   数据库名.dbo.表名   where   条件  
  2、不同服务器    
  SELECT   *   FROM   OpenRowset('MSDASQL',   'Driver=SQL   Server;Server=服务器名（或IP）;UID=sa;PWD=服务器数据库密码',   数据库名.dbo.表   )   where   条件  
  SELECT   *  
  FROM   OPENROWSET('SQLOLEDB','服务器名（或IP）';'sa';'服务器数据库密码',  
        '查询的sql语句')    
   
  SELECT       *  
  FROM           OPENDATASOURCE(  
                    'SQLOLEDB',  
                    'Data   Source=服务器名（或IP）;User   ID=sa;Password=服务器数据库密码'  
                    ).数据库名.dbo.表   where   条件  




http://topic.csdn.net/t/20040804/23/3243457.html




oracle
http://wenku.baidu.com/view/d354b764783e0912a2162a78.html

http://ecourse.gdqy.edu.cn/jp_shengji/2007/database/jp/show_text.asp?id=58

本章学习目标
◎ 通过创建、修改、删除存储过程与触发器，了解存储过程与触发器的基本概念，理解存储过程和触发器的作用；
◎ 通过对实例的存储过程与触发器的管理，掌握存储过程与触发器的使用方法和操作技巧；
◎ 通过对常用系统存储过程的使用，了解存储过程的类型；
本 章知识要点
◇ 存储过程与触发器的概念、功能、类型；
◇ 创建、修改、删除存储过程和触发器的Transact-SQL语句；
◇ 在存储过程中定义和使用输入、输出参数，以及禁止和启用触发器。
本章操作技能
△ 能够使用对象资源管理器和SQL编辑器创建存储过程与触发器；
△ 能够掌握存储过程调用和触发器xx的方法；
△ 能够实施存储过程和触发器的应用管理。
7.1 存储过程概述
SQL Server应用操作中，存储过程和触发器扮演相当重要的角色，基于其预编译并存储在SQL Server数据库中的特性，不仅能提高应用效率，确保一致性，更能提高系统执行速度。同时，使用触发器来完成业务规则，达到简化程序设计的目的。本章将 介绍存储过程和触发器的作用，并讨论使用对象资源管理器和Transact-SQL语句创建、修改、删除存储过程和触发器的方法。
7.1.1 什么是存储过程
当开发一个应用程序时，为了易于修改和扩充方便，我们经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复 调用，而这些独立放置且拥有不同功能的语句，即是“过程”(Procedure)。SQL Server的存储过程包含一些Transact SQL语句并以特定的名称存储在数据库中(存储过程也是一种数据库对象)。可以在存储过程中声明变量、有条件执行以及其他各项强大的程序设计功能。
SQL Server的存储过程与其他程序设计语言的过程类似，同样能按下列方式运行：
⑴ 它能够包含执行各项数据库操作的语句，并且可以调用其他的存储过程。
⑵ 能够接受输入参数并以输出参数的形式将多个数据值返回给调用程序(Ca11ing Procedure)或批处理(Batch)。
⑶ 向调用程序或批处理返回一个状态值，以表明成功或失败(以及失败的原因)。
存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数) 来执行它。
7.1.2 存储过程的好处
1. 允许模块化的程序设计
存储过程一旦创建完成并存储于数据库中，即可在应用程序中反复 调用，因此利用存储过程完成某些例行操作是最恰当不过了。一般来说，我们将存储过程的创建和维护操作交由专人负责，由于各个用于完成特定操作的存储过程均 独立放置，因此根本不需担心当修改存储过程时会影响到应用程序的程序代码。
此外，通过在存储过程中编写业务逻辑和策略，不仅可让不同的应用程序共 享，同时可要求所有的客户端使用相同的存储过程从而达到数据访问和更新的一致性。
2. 更快的执行速度
当执行批处理和Transact- SQL程序代码时，SQL Server必须先检查语法是否正确、接着进行编译、优化然后再执行它，因此如果所要执行的Transact SQL程序代码非常庞大，执行前的处理过程将会耗费一些时间。
对存储过程而言，当它们创建时就己经检查过语法的正确性、编译并加以优化，因此当执 行存储过程时，可以立即直接执行，自然速度会比较快。顾名思义，存储过程就是预先编译和优化并存储于数据库中的过程。
更重要的是，存储过程在它第 一次执行后会在内存中保留，因此以后的调用并不需再将存储过程从磁盘中装载。然而如果从客户端传送Transact SQL语句到后端的SQL Server执行，则每次执行时都必须重新编译和优化，速度当然比较慢。
3. 有效降低网络流量
假设某一项操作需要数百行的 Transact SQL程序代码完成，如果从客户端将这数百行的Transact SQL程序代码传送到后端的SQL Server执行，则在网络上传输的将是程序代码的数千或数万个字符；但是如果事先将这数百行的Transact_SQL程序代码编写成一个存储在SQL Server数据库中的存储过程，则只需从客户端调用该存储过程的名称即可执行它，此时在网络上传输的仅仅是存储过程名称的几个字符。
显而易 见，使用存储过程，所造成的网络流量比较小，因此使用存储过程较好。
4. 较好的安全机制
对于存储过程，我们可以设置哪些用户有权执行 它。这样，就可达到较完善的安全控制和管理。例如：不希望某一位用户有权直接访问某个表，但是又必须要求他针对该表执行特定的操作。这时可以将该位用户所 能针对表执行的操作编写成一个存储过程，并赋予他执行该存储过程的权限，如此一来，虽然这位用户没有权直接访问表，仍然能通过执行存储过程来完成所需的操 作。
7.1.3 存储过程类型
1. 系统存储过程
存储过程在运算时生成执行方式，其后再运行时执行速度很快。SQL Server不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。
系统存储过程(System Stored Procedures)主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。通过系统存储过程，SQL Server中的许多管理性或信息性的活动(如了解数据库对象、数据库信息)都可以被顺利有效地完成。尽管这些系统存储过程被放在master数据库中， 但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创 建。
系统存储过程所能完成的操作多达千百项。例如，提供帮助的系统存储过程有：sp_helpsql显示关于SQL语句、存储过程和其他主题的信 息；sp_help提供关于存储过程或其他数据库对象的报告；sp_helptext显示存储过程和其他对象的文本；sp_depends列举引用或依赖 指定对象的所有存储过程。事实上我们在前面的学习中就己使用过不少系统存储过程，例如，sp_tables取得数据库中关于表和视图的相关信 息；sp_renamedb更改数据库的名称等。
SQL Server系统存储过程是为用户提供方便的，它们使用户可以很容易地从系统表提取信息、管理数据库，并执行涉及更新系统表的其他任务。
系统存储 过程是在安装过程中在master数据库中创建，由系统管理员拥有。所有系统存储过程的名字均以sp_开始。
如果过程以sp_开头，又在当前数据 库中找不到，SQL Server就在master数据库中寻找。以sp_前缀命名的存储过程中引用的表如果不能在当前数据库中解析出来，将在master数据库查找。
当 系统存储过程的参数是保留字或对象名，且对象名由数据库或拥有者名字限定时整个名字必须包含在单引号中。一个用户可以在所有数据库中执行一个系统存储过程 的许可权，否则在任何数据库中都不能执行该系统存储过程。
2. 本地存储过程
本地存储过程(Local Stored Procedures)也就是用户自行创建并存储在用户数据库中的存储过程。事实上我们一般所说的存储过程指的就是本地存储过程。
用户创建的存储 过程是由用户创建并能完成某一特定功能(如查询用户所需数据信息)的存储过程。
3. 临时存储过程
临时存储过程(Temporary Stored Procedures)可分为下列两种：
⑴ 本地临时存储过程
不论哪一个数据库是当前数据库，如果在创建存储过程时，以井 字号(#)作为其名称的{dy}个字符，则该存储过程将成为一个存放在tempdb中的本地临时存储过程(例如：CREATE PROCEDURE #stud_proc…)。本地临时存储过程只有创建它并连接的用户能够执行它，而且一旦这位用户断开与SQL Server的连接(也就是注销SQL Server)，本地临时存储过程会自动删除，当然，这位用户也可以在连接期间用DROP PROCEDURE命令删除他所创建的本地临时存储过程。
由 于本地临时存储过程的适用范围xx于创建它的连接，因此不需担心其名称会和其他连接所采用的名称相同。
⑵ 全局临时存储过程
不论哪一个数 据库是当前数据库，只要所创建的存储过程名称是以两个井字号(##)开头，则该存储过程将成为一个存放在tempdb中的全局临时存储过程(例 如：CREATEP ROCEDURE ##stud_proc…)。全局临时存储过程一旦创建，以后连接到SQL Server的任何用户都能够执行它，而且不需要特定的权限。
当创建全局临时存储过程的用户断开与SQL Sewer的连接，SQL Server将检查是否有其他用户正在执行该全局临时存储过程，如果没有，便立即将全局临时存储过程删除；如果有，SQL Server会让这些正在执行中的操作继续进行，但是不允许任何用户再执行全局临时存储过程，等到所有未完成的操作执行完毕后，全局临时存储过程会自动删 除。
由于全局临时存储过程能够被所有的连接使用，因此必须注意其名称不能和其他连接所采用的名称相同。
不论创建的是本地临时存储过程还是 全局临时存储过程，只要SQL Server一停止运行，它们将不复存在。
4. 远程存储过程
在SQL Server中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常，我们可以使用分布式查询和EXECUTE命令执行一个远程存储过程。

5. 扩展存储过程
扩展存储过程(Extended Stored Procedures)使用户可以使用外部程序语言编写的存储过程。显而易见，通过扩展存储过程我们可以弥补SQL Server不足之处并按需要自行大幅扩展其功能。扩展存储过程在使用和执行上与一般的存储过程xx相同。可以将参数传递给扩展存储过程，扩展存储过程也 能够返回结果和状态值。
为了区别，扩展存储过程的名称通常以xp_开头。扩展存储过程是以动态链接库(DLLs)的形式存在，能让SQL Server动态装载和执行。扩展存储过程一定要存放在系统数据库master中。
7.2创建和执行存储过程
在SQL Server中创建一个存储过程有两种方法：一种是使用Transaction-SQL命令CREATE PROCEDURE，另一种是使用对象资源管理器。用Transaction-SQL创建存储过程是一种较为快速的方法，但对于初学者，使用对象资源管理 器更易理解，更为简单。

7.2.1 创建和执行不带参数的存储过程
1. 使用对象资源管理器创建存储过程
⑴ 单击“开始”按钮，选择“程序”→“Microsoft SQL Server 2005”→“SQL Server Management Studio”→“对象资源管理器”。
⑵ 分别展开“数据库”、欲创建的数据库“student”、“可编程性”、“存储过程”。
⑶ 右击“存储过程”项，在弹出菜单中选择“新建存储过程”选项，打开“SQL编辑器”存储过程模板文件，如图7-2所示。

图7-2 存储过程模板
⑷ 在“SQL编辑器”存储过程模板文件中的相应位置填入存储过程的正文内容，也可以单击“SQL编辑器”工具栏上的“指定模板参数的值”按钮，弹出如图 7-3所示“指定模板参数的值”对话框，输入模板相关的参数值。然后单击“确定”更新存储过程中的值。

图7-3 “指定模板参数的值”对话框
⑸ 单击“SQL编辑器”工具栏上的“分析”按钮，检查语法是否正确。
⑹ 单击“SQL编辑器”工具栏上的“执行”按钮，创建这一存储过程。
⑺ 单击“标准”工具栏上的“保存”按钮，保存创建存储过程的SQL代码。
2. 使用SQL命令创建存储过程
创建不带参数的存储过程的命令 格式：
CREATE PROC[EDURE] procedure_name
AS sql_statements
其 中：procedure_name指明所创建的存储过程的名字，sql_statements为在存储过程中需要执行的数据库操作。
【示例 7.1】针对教师基本信息表teacher_info，创建一个名称为teacher_proc1的存储过程，该存储过程的功能是从数据表 teacher_info中查询所有男教师的信息。
USE student
GO
CREATE PROCEDURE teacher_proc1
AS
SELECT * FROM teacher_info WHERE gender = '男'
GO
欲 要执行已创建的存储过程，可以使用EXECUTE命令。
执行存储过程的命令格式：
[EXEC[UTE]]
[＠return_status=] {procedure_name | ＠procedure_name_var}
[[＠parameter=]{value | ＠variable [OUTPUT] | [DEFAULT]} [,…n]][WITH RECOMPILE]
其中，各个参数意义如下：
① @return_status是一个可选的整型变量，保存存储过程的返回状态。
② procedure_name是拟调用的存储过程名称，@procedure_name_var是局部定义变量名，代表存储过程名称。
③ @parameter是过程参数，在CREATE PROCEDURE语句中定义。
④ value是过程中参数的值，@variable是用来保存参数或者返回参数的变量。
⑤ OUTPUT指定存储过程必须返回一个参数，DEFAULT根据过程的定义，提供参数的默认值。
⑥ n表示可以指定一个或者多个@parameter、value或@variable。
⑦ WITH RECOMPILE强制编译存储过程。
【示 例7.2】在SQL编辑器中编写SQL命令执行数据库student中的存储过程teacher_proc1。
USE student
GO
EXECUTE teacher_proc1
GO
在“SQL编辑器”中运行上面的命令，在结果窗格中将返回教师信息表teacher_info中的所有 男教师的信息。可以看出其结果符合存储过程中定义的查询语句。

3. 创建存储过程的步骤及注意事项

在创建存储过程中，用户需要考虑一些列准则。
⑴ 不能将CREATE PROCEDURE语句与其他SQL语句组合到单个批处理中，每一个批处理就是一个GO语句段。
因为CREATE PROCEDURE命令和USE命令位于同一个批处理之中。下列程序必须如下写才能顺利执行。
USE student
GO
CREATE PROCEDURE stud_proc1
AS
SELECT * FROM stud_info
在AS子句之后且在下一个批处 理之前的语句将被视为是存储过程所要包含的程序代码。例如，假设要一次创建两个存储过程，下面的写法将是错误的：
CREATE PROCEDURE stud_proc1
AS
SELECT stud_id,name,birthday,gender FROM stud_info
GO
CREATE PROCEDURE stud_proc2
AS
SELECT stud_id,name, address, telcode FROM stud_info
⑵ 创建存储过程是有权限的，其默认权限属于数据库所有者，其他用户如果希望获得创建存储过程的权限，必须通过数据库所有者授权，值得注意的是，应尽量避免存 储过程的使用者和所涉及表的所有者不是同一个人的情况。
⑶ 由于存储过程是一个数据库对象，其名称必须遵守标识符规则，在命名用户自定义的存储过程时应避免使用sp_前缀，以免和系统存储过程混淆。
⑷ 尽量不要使用临时存储过程，以避免tempdb上造成的对系统表资源的争夺，从而导致影响系统的执行性能。
⑸ 只能在当前数据库中创建属于当前数据库的存储过程。根据可使用的内存，存储过程的{zd0}尺寸被限制为128MB，存储过程还可以嵌套，最多嵌套至32级。
7.2.2 创建和执行带参数的存储过程
由于不带参数的存储过程的创建是非常简单，所以采用直接创建的办法。但是，对于创建比较复杂的存储过程，当存储过程 时，需要确定存储过程的三个组成部分：
⑴ 所有的输入参数以及传给调用者的输出参数；
⑵ 被执行的针对数据库的操作语句包括调用其它存储过程的语句；
⑶ 返回给调用者的状态值以指明调用是成功还是失败。
建议按照如下四个步骤创 建存储过程。
⑴ 编写Transact_SQL语句
例如【示例7.2】中的SQL语句SELECT * FROM teacher_info WHERE gender = '男'
⑵ 测试Transact_SQL语句
执行编写的SQL语句，确认结果 符合要求。
⑶ 如果返回结果符合要求，则按照存储过程的语法创建其存储过程。
⑷ 在服务器上执行存储过程以验证该存储过程的正确性。
可 以将数据值传递给存储过程，而存储过程用来接收传递进来数据值的参数就是所谓的“输入参数”(Input Parameters)。创建带参数的存储过程的命令格式：
CREATE PROC[EDURE] procedure_name
[{＠parameter data_type} [=DEFAULT][OUTPUT]][ ,…n]
[WITH{RECOMPILE | ENCRYPTION| RECOMPILE, ENCRYPTI○N}]
AS sql_ statements
其中：
① procedure_name为所创建的存储过程的名字。
② ＠parameter用来指定输入和输出参数的名称，参数的名称是以字符＠开头，而且其名称必须符合SQL Server的命名规则。data_type用来指定输入参数的数据类型。输入参数可以是任何的数据类型(包括text、ntext、image和用户定 义数据类型)。
当然，输入参数的数据类型也就决定了这一输入参数所能接收数据值的类型和范围。例如，输入参数的数据类型是smallint，则它 只能接收-32,768~32,767之间的整数值，将不符合此条件的数据值传递给它都会发生错误。
DEFAULT用来指定输入参数的默认值，它 是一个常量或NULL。在创建存储过程时如果给出了默认值，那么在执行该存储过程时，如果没有向具有默认值的参数传递参数值时，则具有默认值的参数就可以 使用它们的默认值。OUTPUT表明参数是返回参数。
③ WITH RECOMPILE决定执行计划不保存在过程高速缓存中，每次执行时都重新编译它。WITH ENCRYPTION对含有CREATE PROCEDURE正文的syscomments项进行加密。
④ sql_statements：在存储过程中需要执行的数据库操作。

1. 具有输入参数的存储过程

【示例7.3】针对教师基本信息表teacher_info，创建一个名称为 teacher_proc2的存储过程，执行存储过程将完成向数据表teacher_info中插入一条记录，新记录的值由参数提供。
USE student
GO
CREATE PROCEDURE teacher_proc2
(@no char(6), @nam varchar(8), @sex char(2), @age int, @title char(10), @tel varchar(12), @sala decimal(7), @num char(10))
AS
INSERT INTO teacher_info VALUES(@no, @nam, @sex, @age, @title, @tel, @sala, @num)
GO
执行带有输入 参数的存储过程有两种方法：一是使用参数名传送参数值，二是按位置传送参数值。
⑴ 使用参数名传送参数值
在执行存储过程的语句中，通过语 句＠parameter_name=value给出参数的传递值。当存储过程含有多个输入参数时，参数值可以以任意顺序指定，对于允许空值和具有默认值的 输入参数可以不给出参数的传递值。语法命令如下：
[[EXEC[UTE]] procedure_name
[@parameter_name = value][ ,…n]
其中各参数的意义为：procedure_name为存储过程名；＠parameter_name为输入参数 名；value为传递给输入参数的值。
【示例7.4】使用参数名传送参数值的方法来执行存储过程teacher_proc2，完成向数据表 teacher_info中插入一条记录。
USE student
GO
EXECUTE teacher_proc2 @no ='010108', @nam='李铁烙', @sex='男', @age=49,@title='高讲', @tel='02034301299', @sala=250.0, @num='0401010207'
为了说明使用参数名传递参数值时可以以 任意顺序指定参数值，将两个参数的次序进行了颠倒，此外由于目的地的查询值与该参数的默认值一致，所以也没有对该输入参数的参数值进行指定。
⑵ 按位置传送参数值
在执行存储过程的语句中，不参照被传递的参数而直接给出参数的传递值。当存储过程含有多个输入参数时，传递值的顺序必须与存储过 程中定义的输入参数的顺序相一致。
语法命令如下：
[EXEC[UTE]] proc_name
[value1,value2,…]
其 中各参数的意义为：proc_name为存储过程名；value1,value2,…为传递给各输入参数的值。按位置传送参数值时，也可以忽略允许空值和 具有默认值的参数，但是不能因此破坏输入参数的指定次序。因此，在一个含有四个输入参数的存储过程中，用户可以忽略第三和第四个参数，但无法在忽略第三个 参数的情况下而指定第四个参数的输入值。
【示例7.5】使用按位置传送参数值的方法来执行teacher_proc2存储过程，实现向数据表 teacher_info中插入一条记录。具体命令如下：
USE student
GO
EXEC teacher_proc2 '010109', '李烙铁', '女', 48,'讲师','02034367719',250.0,'0401010108'
这里由于使用 的是按位置传送参数值的方法，所以不可以将参数的次序进行颠倒。此外由于目的地的查询值与该参数的默认值一致，也可以不对该输入参数的参数值进行指定。

2. 具有默认值的存储过程

【示例7.6】针对教师基本信息表teacher_info，创建一个名称为 teacher_proc3的存储过程，执行存储过程时将向数据表teacher_info中插入一条记录，新记录的值由参数提供，如果未提供职称 tech_title的值时，由参数的默认值代替。
USE student
GO
CREATE PROCEDURE teacher_proc3
(@no char(6), @nam varchar(8), @sex char(2), @age int, @title char(10)='无', @tel varchar(12), @sala decimal(7),@num char(10))
AS
INSERT INTO teacher_info VALUES(@no,@nam,@sex,@age,@title,@tel,@sala,@num)
GO
EXECUTE teacher_proc3 @no ='010110', @nam='张小波', @sex='女', @age=18, @tel='02034361954', @sala=250.0, @num='0401010108'
【示例7.7】在student数据库 上新建一个名为stud_proc1的存储过程，该存储过程定义了两个日期时间类型的输入参数和一个字符型输入参数，返回所有出生日期在两个输入日期之 间，性别与输入的字符型参数相同的学生信息，其中字符型输入参数指定的默认值为“女”。具体命令如下：
USE student
GO
CREATE PROC stud_proc1
@startdate datetime,@enddate datetime,@sex char(2)='女'
AS
IF (@startdate IS NULL or @enddate IS NULL or @sex IS NULL)
BEGIN
RAISERROR ('NULL value are invalid',5,5)
RETURN
END
SELECT * FROM stud_info
WHERE (birthday BETWEEN @startdate AND @enddate) AND gender=@sex
GO
上述代码中用到了RAISERROR函数，该函数返回用户定义的错误信息并设系统标志，以记录发生错 误。RAISERROR函数语法结构如下：
RAISERROR({msg_id | msg_str}{, severity, state}[, argument[ ,…n]])
其中：
⑴ msg_id是存储于sysmessages表中的用户定义的错误信息号。
⑵ msg_str是一条可包含400个字符的错误消息，其格式与C语言中使用的PRINTF格式样式相似。
⑶ severity为用户定义的与消息关联的严重级别。用户可以使用从0到18之间严重级别。19到25之间的严重级别只能由sysadmin固定服务器角 色成员使用。
⑷ state是从1至127的任意整数，表示有关错误调用状态的信息。state的值默为1。
⑸ argument是用于取代在msg_str中定义的变量或取代对应于msg_id的消息参数。

3. 具有返回值的存储过程
⑴ 创建具有返回值的存储过程
从存储过程中返回一个或多个 值，可以通过在创建存储过程的语句中定义输出参数来实现。为了使用输出参数，需要在CREATE PROCEDURE语句中指定OUTPUT关键字。通过使用输出参数，任何由于执行了存储过程的参数变化都可以保留，即使在存储过程xx完成之后。具体语 法如下：
＠parameter_name datatype[=default] OUTPUT [ ,…n]
其中各参数的意义如下：
① ＠parameter_name存储过程的输出参数名，必须以符号＠为前缀。存储过程通过该参数返回结果。
② datatype指明输出参数的数据类型，它既可以是系统提供的数据类型，也可以是用户自定义的数据类型，但必须是除了text和image以外的数据类 型。
③ default指定输出参数的默认值，如果执行存储过程时未对输出参数进行赋值，则存储过程在返回输出参数的变量值时，使用default值。
④ OUTPUT关键字指明参数为输出参数。输出参数必须位于所有输入参数说明之后。
【示例7.8】在student数据库上新建一名为 stud_proc2的存储过程，其功能是输入两个日期型数据，并使用输出参数返回这两个出生日期之间的所有学生人数。
USE student
GO
CREATE PROCEDURE stud_proc2
@startdate datetime,@enddate datetime,@recordcount int OUTPUT
AS
IF @startdate IS NULL or @enddate IS NULL
BEGIN
RAISERROR('NULL value are invalid',5,5)
RETURN
END
SELECT * FROM stud_info
WHERE birthday BETWEEN @startdate AND @enddate
SELECT @recordcount = @@ROWCOUNT
GO
其中，＠＠ROWCOUNT是SQL Server用来返回受上一语句影响的行数的系统变量，在这里用它来返回符合条件记录数。
⑵ 执行具有返回值的存储过程
在调用含有输出参 数的存储过程的程序中，为了接收存储过程的返回值，必须声明作为输出的传递参数，即在EXECUTE语句中指定OUTPUT关键字。在执行存储过程时，如 果OUTPUT关键字被忽略，存储过程仍能被执行，只是不返回值。命令语法格式为：
[EXECUTE]
{[＠return_status=] {procedure_name | ＠procudure_name_var}
｝
[[＠parameter_name=]{value | @variable[OUTPUT]}][ ,…n]
[WITH RECOMPILE]
其中各参数的意义如下：
① procedure_name:需执行的存储过程的名字；
② [＠parameter_name=]{value | @variable}为输入参数传递值；
③ [＠parameter_name=]＠variable OUTPUT为传递给输出参数的变量，＠variable用来存放返回参数的值。OUTPUT指明这是一个输出传递参数，与响应的存储过程中的输出参数相 匹配。
【示例7.9】执行stud_proc2存储过程，返回出生日期在1986年1月1日与1986年12月31日的学生记录的条数。具体命令 如下：
USE student
GO
DECLARE @recordnumber int /*声明为局部变量，用来存放输出参数的值*/
EXEC stud_proc2 '01/01/1986','12/31/1986',@recordnumber OUTPUT
PRINT 'The order count is:'+str(@recordnumber)
在上述命令中，变量＠recordnumber被说明为存储过程stud_proc2中的输出 参数＠recordcount的返回值。此外，为了接收存储过程的返回值，在调用存储过程的命令中，必须声明作为输出的传递参数，这个输出传递参数需要声 明为局部变量，用来存放参数的值。
4. 存储过程的重编译处理
⑴ 存储过程的处理
在创建存储过程时，SQL Server需要对存储过程中的语句进行语法检查。如果存储过程定义中存在语法错误，将返回错误，并且将不能创建该存储过程。如果语法正确，则存储过程的 文本将存储在syscomments系统表中。
首次执行存储过程时，查询处理器从syscomments系统表中读取该存储过程的文本，并检查过 程所使用的对象名称是否存在，这一过程称为延迟名称解析。因此存储过程引用的对象只需在执行该存储过程时存在，而不需要在创建该存储过程时就存在。在解析 阶段，SQL Server还将执行数据类型检查和变量兼容性等其他验证活动。如果执行存储过程时出现存储过程所引用的对象丢失，则存储过程在到达引用丢失对象的语句时 将停止执行并将回错误信息。
如果存储过程顺利通过解析阶段，SQL Server将分析存储过程的语句，并创建一个执行计划。
在分析完存 储过程中的这些因素表中的数据量(表中是否存在索引和索引的性质，以及数据在索引列中的分布；WHERE条件子句所用的比较运算符和比较值；是否存在联接 以及UNION、GROUP BY或ORDER BY关键字)后，将执行计划置于内存中。优化的内存的执行计划将用来执行该查询。执行计划将驻留在内存中，直到重新启动SQL Server或要空间以存储另一个对象时为止。
以上介绍的分析存储过程和创建执行计划的过程统称为编译，编译工作完成之后，系统就可以开始执行这 个存储过程了。在执行存储过程时，如果现有的执行计划仍在内存中，SQL Server将再次使用它。如果执行计划不再位于内存中，则创建新的执行计划。
⑵ 存储过程的重编译处理
SQL Server为用户提供了三种设定重编译选项的方法，分别介绍如下：
① 在建立存储过程时设定重编译选项
具 体语法如下：
CREATE PROCEDURE [WITH RECOMPILE]
通过在创建时设定重编译选项，可以使SQL Server在每次执行时对存储过程进行重编译处理。
【示例7.10】在创建一个按照性别统计人数的存储过程stud_proc3，要求输入性别 的值后，返回对应性别的学生人数，但需保证其在每次被执行时都被重编译处理。具体命令如下：
USE student
GO
CREATE PROCEDURE stud_proc3
(@in_sex CHAR(2), @out_num INT OUTPUT)
WITH RECOMPILE
AS
BEGIN
IF @in_sex='男'
SELECT @out_num=count(gender)
FROM stud_info WHERE gender='男'
ELSE
SELECT @out_num=count(gender)
FROM stud_info WHERE gender ='女'
END
执行 所定义的存储过程：
DECLARE @man_num int
EXEC stud_proc3 '女', @man_num OUTPUT
SELECT @man_num
② 在执行存储过程时设定重编译选项
EXECUTE procedure_name
[＠parameter_name=]＠variable OUTPUT [WITH RECOMPILE]
通过在执行存储过程的EXECUTE语句中设定WITH RECOMPILE选项，可以让SQL Server在执行存储过程时重新编译该存储过程。
【示例7.11】执行存储过程stud_proc3 时，对其进行重编译处理。
DECLARE @man_num int
EXEC stud_proc3 '男', @man_num OUTPUT WITH RECOMPILE
PRINT @man_num
③ 通过系统存储过程设定重编译选项
EXEC sp_recompile OBJECT
其中，sp_recompile为用于重编译存储过程的系统存储过程，OBJECT为当前数据库中的存储 过程、触发器、表或视图的名称。
【示例7.12】对student数据库中stud_info表上的所有存储过程或触发器进行重编译处理。
EXEC sp_recompile stud_info
7.3 修改和删除存储过程
7.3.1 查看存储过程
存储过程被创建以后，它的 名字存储在系统表sysobjects中；它的源代码存放在系统表syscomments中。我们既可以通过对象资源管理器查看存储过程的源代码，也可以 通过SQL Server提供的系统存储过程来查看用户创建的存储过程信息。

1. 通过对象资源管理器
通过对象资源管理器，用户可以查看所创建的存储过程。在对象资源管理 器中，分别展开“数据库”、“用户数据库”、“可编程性”、“存储过程”，双击“存储过程”，在存储过程列表中，用户即可看到该数据库的所有的存储过程。 右击某一的存储过程，在出现的快捷菜单中选择“执行存储过程”，在“SQL编辑器”窗口中就可以看到存储过程的执行结果。
2. 使用系统存储过程
使 用系统存储过程的查看存储过程源代码的命令是：sp_helptext procedure_name。
【示例7.13】查看数据库 student中存储过程teacher_proc1的源代码。
EXEC sp_helptext teacher_proc1
如果在创 建存储过程时使用了WITH ENCRYPTION选项，那么无论是使用对象资源管理器，还是系统存储过程sp_helptext都无法查看到存储过程的源代码。
7.3.2修 改存储过程
修改存储过程通常是指编辑它的参数和Transact-SQL语句。下面我们分别说明如何使用对象资源管理器和Transact- SQL语句命令完成这项工作。
1. 使用对象资源管理器
⑴ 单击“开始”按钮，选择“程序”→“Microsoft SQL Server 2005”→“SQL Server Management Studio”→“对象资源管理器”。
⑵ 分别展开“数据库”、欲修改存储过程所处的数据库、“可编程性”、“存储过程”、欲修改的存储过程。
⑶ 右击欲修改存储过程，在弹出菜单中选择“修改”，此时立即在“SQL编辑器”窗格中出现欲修改的存储过程文件。
⑷ 在“SQL编辑器”中编辑存储过程的参数和Transact-SQL语句。此时，一般不要改变ALTER PROCEDURE语句中的存储过程名称。如果觉得存储过程的重命名以及参数和Transact-SQL语句的编辑要分开来完成很麻烦，可以直接删除存储 过程后再重新创建符合要求的存储过程。
⑸ 编辑了存储过程的参数和Transact-SQL语句之后，单击“SQL编辑器”工具栏上“分析”按钮检查所编写的程序代码语法无误，然后单击“SQL编 辑器”工具栏上“执行”按钮完成存储过程的参数和Transact-SQL语句修改。
⑹ 单击“标准”工具栏上“保存”按钮，以保存修改存储过程的SQL文件。
2. 使用ALTER PROCEDURE命令
具体语法如下：
ALTER PROC[EDURE] procedure_name
[{＠parameter data_type}[=DEFAULT][OUTPUT]][ ,…n]
[WITH{RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYTION}]
AS
Sql_statement[,…n]
其中，各参数的意义如下：
procedure_name 为要修改的存储过程的名称，＠parameter为存储过程中包含的输入和输出参数，data_type指定输入和输出参数的数据类型，default为 输入输出参数指定的默认值，必须为一个常量，WITH RECOMPILE为存储过程指定重编译选项，WITH ENCRYPTION是对包含ALTER PROCEDURE文本的syscomments表中的项进行加密。
【示例7.14】修改存储过程teacher_proc1，返回所有性别为 “女”的学生学号、姓名、地址、电话等基本信息。并对存储过程指定重编译处理和加密选项。
USE student
GO
ALTER PROCEDURE teacher_proc1
WITH RECOMPILE, ENCRYPTION
AS
SELECT teacher_id,name,tech_title,telephone FROM teacher_info WHERE gender = '女'
GO
注意：修改具有任何选项，例如WITH RECOMPILE的存储过程时，必须在ALTER PROCEDURE语句中包括该选项以保留该选项提供的功能；ALTER PROCEDURE语句只能修改一个单一的过程。如果存储过程中调用了其他存储过程，嵌套的存储过程将不受影响。
3. 重新命名存储过程
修 改存储过程的名字使用系统存储过程sp_rename，其命令格式为：
sp_rename old_procedure_name, new_procedure_name
【示例7.15】将存储过程teacher_proc1修改为teacher_info_proc1。
sp_rename teacher_proc1, teacher_info_proc1
另外，通过对象资源管理器也可修改存储过程的名字，其操作过程与 Windows下修改文件名字的操作类似。即首先选中需修改名字的存储过程，然后右击鼠标，在弹出菜单中选取“重命名”选项，{zh1}输入新存储过程的名字。
7.3.3 删除存储过程
1. 使用对象资源管理器
使用对象资源管理器删除一个或多个存储过程，请先将它们选取，然后使用鼠标右键按下其中一个被选取 的存储过程，并从快捷菜单中选取“删除”命令，接着再用鼠标左键单击“删除对象”对话框中的“确定”按钮。
2. 使用DROP PROCEDURE语句
删除存储过程使用DROP命令，DROP命令可将一个或多个存储过程或者存储过程组从当前数据库中删除。具体语法如下：
DROP PROC[EDURE] procedure_name[,…n]
其中各参数的意义与修改存储过程命令中参数的意义相同。
【示例 7.16】将存储过程teacher_info_proc1从数据库中删除。
DROP PROCEDURE teacher_info_proc1
7.4 创建和管理触发器
7.4.1 触发器概述
1. 触发器的基本概念
在SQL Server数据库系统中，存储过程和触发器都是SQL语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程，一种在数据表被修改时自动执行的 内嵌过程，主要通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE 这些操作时，SQL Server就会自动执行触发器所定义的SQL语句。从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器的主要作用就是其能够实现由主键 和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能。
2. 使用触发器的优点
由于在触发器中可 以包含复杂的处理逻辑，因此应该将触发器用来保持低级的数据完整性，而不是返回大量的查询结果。使用触发器主要可以实现以下操作：
⑴ 强制比CHCEK约束更复杂的数据完整性
在数据库中要实现数据完整性的约束，可以使用CHECK约束或触发器来实现。但是在CHECK约束中不允 许引用其他表中的列来完成检查工作，而触发器则可以引用其他表中的列来完成数据完整性的约束。
⑵ 使用自定义的错误信息
用户有时需要在数 据完整性遭到破坏或其他情况下，发出预先自定义好的错误信息或动态自定义的错误信息。通过使用触发器，用户可以捕获破坏数据完整性的操作，并返回自定义的 错误信息。
⑶ 实现数据库中多张表的级联修改
用户可以通过触发器对数据库中的相关表进行级联修改。
例如，可以在 lesson_info表course_id列上写入一个删除触发器，以使其他表中的各匹配行采取删除操作。该触发都course_id列作为惟一键，在 teacher_info、stud_grade及teach_schedule表中对各匹配行进行定位删除。
⑷ 比较数据库修改前后数据的状态
触 发器提供了访问由INSERT、UPDATE或DELETE语句引起的数据变化前后状态的能力。因此用户就可以在触发器中引用由于修改所影响的记录行。
⑸ 维护非规范化数据
用户可以使用触发器来保证非规范数据庠中的低级数据的完整性。维护非规范化数据与表的级联是不同的。表的级联指的是不同表之间 的主外键关系，维护表的级联可以通过设置表的主键与外键的关系来实现。而非规范数据通常是指在表中的派生的、冗余的数据值，维护非规范化数据应该通过使用 触发器来实现。
7.4.2 创建触发器
1. 用对象资源管理器创建触发器
⑴ 打开SQL Server Management Studio。
⑵ 分别展开“数据库”→“用户数据库”→“表”。
⑶ 单击将在其上创建触发器的数据表（如stud_info），再右击“触发器”，在弹出的快捷菜单上单击“新建触发器”，SQL编辑器中出现触发器创建的模 板文件，如图7-14所示。

图7-14 触发器创建的模板
⑷ 在“SQL编辑器”触发器模板文件中的相应位置填入创建触发器的Transact-SQL语句，也可以单击“SQL编辑器”工具栏上的“指定模板参数的 值”按钮，弹出如图7-15所示“指定模板参数的值”对话框，输入模板相关的参数值。然后单击“确定”更新触发器的参数值。

图7-15 “指定模板参数的值”对话框
⑸ 单击“SQL编辑器”的工具栏上“执行”按钮，完成触发器的创建，如需保存触发器创建的Transact-SQL语句，单击“标准”工具栏上的“保存”按 钮。
2. 使用命令创建触发器
基本语法如下：
CREATE TRIGGER trigge_name
ON {table | view}
{FOR | AFTER | INSTEAD OF}{[INSERT],[UPDATE],[DELETE]}
[WITH ENCRYPTION]
AS
IF UPDATE(column_name)
[{and｜or} UPDATE(column_name)…] sql_statesments
其中：
① trigge_name是触发器的名称，用户可以选择是否指定触发器所有者名称。
② table | view为执行触发器的表或视图，可以选择是否指定表或视图的所有者名称。
③ AFTER在对表的相关操作正常操作后，触发器被触发。如果仅指定FOR关键字，则AFTER是默认设置。
④ INSTEAD OF指定执行触发器而不是执行触发语句，从而替代触发语句的操作。可以为表或视图中的每个INSERT、UPDATE或DELETE语句定义一个 INSTEAD OF触发器。如果一个可更新的视图定义时，使用了WITH CHECK OPTION选项则INTEAD OF触发器不允许在这个视图上定义。用户必须用ALTER VIEW删除选顼后才能定义INSTEAD OF触发器。
⑤ {[INSERT],[UPDATE],[DELETE]}是指定在表或视图上执行哪些数据修改语句时xx触发器的关键字。这其中必须至少指定一个选项。 在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。对于INSTEAD OF触发器，不允许在具有ON DELETE级联操作引用关系的表上使用DELETE选顼。同样，也不允许在具有ON UPDATE级联操作引用关系的表上使用UPDATE选项。
⑥ ENCRYPTION是加密含有CREATE TRIGGER语句正文文本的syscommnents项，这是为了满足数据安全的需要。
⑦ sql_statesments定义触发器被触发后，将执行数据库操作。它指定触发器执行的条件和动作。触发器条件是除了引起触发器执行的操作外的附加条 件；触发器动作是指当用户执行激发触发器的某种操作并满足触发器的附加条件时，触发器所执行的动作。
⑧ IF UPDATE指定对表内某列做增加或修改内容时触发才起作用，它可以指定两个以上列，列名前可以不要加上表名。IF子句中多个触发器动作可以放在 BEGIN和END之间。
⑴ INSERT触发器
【示例7.17】在student数据库的表teacher_info上创建一个 teacher_trigger1触发器，当执行INSERT操作该触发器被触发(即向所定义触发器的表中插入数据时将触发其触发器)。具体命令如下：
USE student
GO
CREATE TRIGGER teacher_trigger1
ON teacher_info
FOR INSERT
AS
RAISERROR('unauthorized',10,1)
当用户向表teacher_info中插入数 据时将触发触发器，但是数据仍能被插入表中，如向表中加入如下记录内容：
INSERT INTO teacher_info
VALUES('010111',' 目火柴','男','55', '政工师','02061230901',9999,'0401010100')
用户可以用SELECT * FROM teacher_info语句查看一下表的内容，可以发现上述记录己经插入到teacher_info表中。这是由于在定义触发器时，指定的是FOR选 项，因此AFTER成了默认设置。此时触发器只有在触发SQL语句INSERT中指定的所有操作都己成功执行后才激发。因此用户仍能将数据插入 teacher_info表中。有没有什么办法能实现触发器被执行的同时，取消触发触发器的SQL语句的操作呢？这就需要使用INSTEAD OF关键字来实现。
【示例7.18】在student数据库的表teacher_info上创建一个teacher_trigger2触发器，当 执行DELETE操作时触发器触发，且要求触发触发器的DELETE语句在执行后被取消。具体命令如下：
USE student
GO
CREATE TRIGGER teacher_trigger2
ON teacher_info
INSTEAD OF DELETE
AS
RAISERROR('You Have No Right To Delete The Record',10,1)
在表teacher_info中删除上例中新增的记录。 运行如下命令：
DELETE * FROM teacher_info
WHERE teacher_id='010111'
在 “SQL编辑器”的消息返回窗口中将出现报错信息。
用户此时再用SELECT语句查看一下表的内容，可以发现上例新添加的记录仍然保留在 teacher_info表中，可见在定义触发器时定义的INSTEAD OF选顼取消了触发teacher_trigger2的DELETE操作，所以该记录未被删除。
⑵ UPDATE触发器
在带有 UPDATE触发器的表上执行UPDATE语句时，将触发UPDATE触发器。使用UPDATE触发器时，用户可以通过定义IF UPDATE (column name)来实现。当特定列被更新时触发触发器，而不管更新影响的是表中的一行或是多行。如果用户需要实现多个特定列中的任意一列被更新时触发触发器，可 以通过在触发器定义中使用多个IF UPDATE(column name)语句来实现。
【示例7.19】在数据库student的表 teacher_info上建立一个名为teacher_trigger3的触发器，该触发器将被操作UPDATE所xx，该触发器将不允许用户修改表的 name列(这里将不使用INSTEAD OF而是通过ROLLBACK TRANSACTION子句恢复原来数据的方法来实现字段不被修改)。命令如下：
USE student
GO
CREATE TRIGGER teacher_trigger3
ON teacher_info
FOR UPDATE
AS
IF UPDATE (name)
BEGIN
RAISERROR('Unauthorized!',10,1)
ROLLBACK TRANSACTION
END
建好触发器后试着执行UPDATE操作：
USE student
GO
UPDATE teacher_info
SET name='黄活才'
WHERE teacher_id='010111'
运行结果显 示：“Unauthorized！”，说明操作无法进行，触发器起到了保护作用。
在“SQL编辑器”中运行命令：
USE student
GO
SELECT teacher_id, name
FROM teacher_info
WHERE name LIKE '黄%'
查询结果中可 以发现上述更新操作并不能实现对表中name列的更新。
但是UPDATE操作可以对没有建立保护性触发的其他列进行更新而不会激发触发器，例如在 “SQL编辑器”中运行如下命令：
USE student
GO
UPDATE teacher_info
SET tech_title ='无职称'
WHERE teacher_id='010111'
执行后返回的消息：“所影响的行数为1行”，检 索表teacher_info可以看到职称列的内容确实被更新了。通过如下的命令对teacher_info建立如下查询：
SELECT teacher_id, name , tech_title
FROM teacher_info
WHERE teacher_id='010111'
从查询结果可以看出，tech_title的内容确实被更新了。
⑶ DELETE触发器
【示 例7.20】在数据库student的表teacher_info上建立一个名为teacher_trigger4的DELETE触发器，该触发器将实现 对表teacher_info中删除记录的操作给出报警，并取消当前的删除操作。
USE student
GO
CREATE TRIGGER teacher_trigger4
ON teacher_info
FOR DELETE
AS
BEGIN
RAISERROR('Unauthorized!',10,1)
ROLLBACK TRANSACTION
END
7.4.3 管理触发器
1. 查看触发器信息
像存储过程一样，触发器在创建后，其名称保 存在系统表sysobjects中，并把创建的源代码保存在系统表syscomments中。SQL Server为用户提供多种查看触发器信息的方法。
⑴ 使用系统存储过程
系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。
通过 sp_help系统存储过程，可以了解触发器的一般信息(名字、属性、类型、创建时间)。例如，输入sp_help 'stud_trigger'命令查看已经建立的stud_trigger触发器信息。
通过sp_helptext能够查看触发器的定义信息。例 如：输入sp_helptext 'stud_trigger'命令查看已经建立的stud_trigger触发器的定义文本。
通过 sp_depends能够查看指定触发器所引用的表或指定的表涉及到的所有触发器。例如：输入sp_depends 'stud_trigger'命令查看已经建立的stud_trigger触发器所涉及的表，输入sp_depends 'stud_info'命令查看指定的表stud_info所涉及的触发器。
注意：用户必须在当前数据库中查看触发器的信息，而且被查看的触发器 必须已经被创建。和存储过程的加密类似，用户也可以在创建触发器时，通过指定WITH ENCRYPTION来对触发器的定义文本信息进行加密，加密后的触发器无法用sp_helptext来查看相关信息。
用户还可以通过使用系统存 储过程sp_helptrigger来查看某张特定表上存在的触发器的某些相关信息。具体命令的语法如下：
EXEC sp_helptrigger table_name
【示例7.21】使用系统存储过程sp_helptrigger查看表 teacher_info上存在的所有触发器的相关信息。具体命令如下：
USE student
GO
EXEC sp_helptrigger teacher_info
GO
在“SQL编辑器”中运行上面的命令，在结果窗格中将返回在表 teacher_info上定义的所有触发器的相关信息。从返回的信息中，用户可以了解到触发器的名称、所有者以及触发条件的相关信息。
⑵ 使用系统表
用户还可以通过查询系统表sysobjects得到触发器的相关信息。
【示例7.22】使用系统表sysobjects查看数 据库student上存在的所有触发器的相关信息。具体命令如下：
USE student
GO
SELECT name FROM sysobjects
WHERE type='TR'
GO
在“SQL编辑器”中运行上面的命令，在结果窗格中将返回在数据库 student上定义的所有触发器的名称。
⑶ 使用对象资源管理器查看触发器依赖关系
⑴ 打开SQL Server Management Studio。
⑵ 在“对象资源管理器”中分别展开“数据库”→“用户数据库”→“表”→含触发器的数据表（如teacher_info）→“触发器”。
⑶ 右击将在其上查看依赖关系的触发器（如teacher_trigger1），在弹出的快捷菜单上单击“查看依赖关系”，出现“对象依赖关系”窗口，查看完 毕单击“确定”。
2. 修改触发器
通过对象资源管理器、系统存储过程、Transact-SQL命令，可以修改触发器的名字和正文。
⑴ 使用sp_rename命令修改触发器的名字，其语法格式为：
sp_rename oldname, newname
其 中，oldname为触发器原来的名称，newname为触发器的新名称。
⑵ 通过对象资源管理器修改触发器定义
打开“SQL Server Management Studio”→在“对象资源管理器”中分别展开“数据库”→“用户数据库”→“表”→含触发器的数据表（如teacher_info）→“触发器”→右 击将要修改触发器（如teacher_trigger1）→在弹出菜单中选择“修改”，此时在“SQL编辑器”窗格中出现欲修改的触发器文本→编辑触发器 的参数和Transact-SQL语句→单击“SQL编辑器”工具栏上“分析”按钮检查所修改的代码语法无误→单击“SQL编辑器”工具栏上“执行”按钮 完成触发器修改→单击“标准”工具栏上“保存”按钮，以保存触发器修改的SQL文件。
⑶ 通过ALERT trigger命令修改触发器正文。在实际应用中，用户可能需要改变一个已经存在的触发器，可以通过使用SQL Server提供的ALTER TRIGGER语句来实现。SQL Server可以在保留现有触发器名称的同时，修改触发器的触发动作和执行内容。修改触发器的具体语法如下：
ALTER TRIGGER trigge_name
ON {table | view}
{FOR | AFTER | INSTEAD OF}{INSERT,UPDATE,DELETE｝
[WITH ENCRYPTION]
AS
IF UPDATE(column_name)
{and | or} UPDATE(column name)…]
sql_statesments
其 中各参数的意义与建立触发器语句中参数的意义相同。
【示例7.23】修改数据库student中的表teacher_info上建立的 INSTEAD OF触发器teacher_trigger2，使得用户执行删除、增加、修改操作时，自动给出错提示信息并撤消此次操作。具体命令如下：
USE student
GO
ALTER TRIGGER teacher_trigger2
ON teacher_info
INSTEAD OF DELETE,INSERT,UPDATE
AS
RAISERROR('You can not insert,delete or update records from this table',10,1)
3. 删除触发器
删除已创建的触发器有三种方法：
⑴ 使用命令DROP TRIGGER删除指定的触发器，删除触发器的具体语法形式如下：
DROP TRIGGER trigger_name
例 如，用户可以使用DROP TRIGGER teacher_trigger2来删除触发器teacher_trigger2。
⑵ 删除触发器所在的表时，SQL Server将自动删除与该表相关的触发器。
⑶ 使用对象资源管理器删除触发器，右击将要删除触发器（如teacher_trigger2），在弹出菜单中选择“删除”，接着再单击“删除对象”对话框中 的“确定”按钮。

4. 禁止和启用触发器

在使用触发器时，用户可能遇到在某些时候需要禁止某个触发器起作用的场合，例如用户需要对某个建有 INSERT触发器的表中插入大量数据。当一个触发器被禁止后，该触发器仍然存在于数据表上，只是触发器的动作将不再执行，直到该触发器被重新启用。禁止 和启用触发器的具体语法如下：
ALTER TABLE table_name
{ENABLE | DISABLE} TRIGGER
{ALL | trigger_name[,…n]}
其中，{ENABLE | DISABLE} TRIGGER指定启用或禁用trigger_name。当一个触发器被禁用时，它对表的定义依然存在；然而，当在表上执行INSERT、UPDATE或 DELETE语句时，触发器中的操作将不执行，除非重新启用该触发器。ALL指定启用或禁用表中所有的触发器；trigger_name指定要启用或禁用 的触发器名称。
【示例7.24】禁止或启用在数据库student中表teacher_info上创建的所有触发器。
ALTER TABLE teacher_info DISABLE TRIGGER ALL
ALTER TABLE teacher_info ENABLE TRIGGER ALL
用户可以自己尝试禁止或启用在数据库student中表stud_info上创建的某个触发器。
本 章小结
存储过程是一系列预先编辑好的、能实现特定数据操作功能的Transact-SQL代码集，它与特定的数据库相关联，存储在SQL Server服务器上。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。
触发器是一种特殊字段的存储过程，当数据表中数据被修 改时，SQL Server自动执行触发器，使用触发器可以实施更为复杂的数据完整性约束，如果需要对数据库进行各种更新操作，即添加数据、修改数据以及删除数据，则需 要通过INSERT、UPDATE、DELETE等语句来实现。
本章在学习存储过程、触发器的概念与功能的基础上，讲述了存储过程与触发器的类 型，介绍了使用对象资源管理器和Transact-SQL语句(CREATE PROCEDURE、ALTER PROCEDURE、DROP PROCEDURE、CREATE TRIGGER、ALTER TRIGGER、DROP TRIGGER)创建、修改、删除存储过程和触发器，以及在存储过程中定义和使用输入、输出参数，由INSERT、UPDATE或DELETE语句引起的 数据变化而xx触发器。
实习训练
1. 实训目的
⑴ 理解存储过程和触发器的作用。
⑵ 学会使用对象资源管理器和SQL编辑器创建存储过程。
⑶ 学会使用对象资源管理器和SQL编辑器创建触发器。
⑷ 学会存储过程和触发器的管理方法。
2. 实训环境与条件
3. 实训内容和步骤
⑴ 创建和执行存储过程
① 针对教师基本信息表teacher_info，创建一个名称为teacher_select的存储过程，执行存储过程将从数据表teacher_info 中根据编号检索某一教师的姓名和职称。
USE student
GO
CREATE PROCEDURE teacher_select
(@no char(6), @nam varchar(8) OUTPUT, @title char(10) OUTPUT)
AS
SELECT @nam=name, @title = tech_title FROM teacher_info WHERE teacher_id=@no
执行已经定义的存储过程：
DECLARE @nam varchar(6) /*声明为局部变量，用来存放姓名的值*/
DECLARE @title char(10) /*声明为局部变量，用来存放职称的值*/
EXECUTE teacher_select '010104',@nam OUTPUT,@title OUTPUT
SELECT '姓名:',@nam,'职称:',@title
② 建立一个插入教师记录的存储过程teacher_insert，在SQL编辑器中输入存储过程的代码并执行。
CREATE PROCEDURE teacher_insert
(@no char(6), @nam varchar(8), @sex char(2), @age int, @title char(10), @tel varchar(12), @sala decimal(7), @num char(10))
AS
INSERT INTO teacher_info VALUES(@no,@nam,@sex,@age,@title,@tel,@sala,@num)
GO
EXEC teacher_insert '010119', '聂小蛋', '男', 40,'研究员','02034367219',250.0,'0401010113'
在对象资源管理器中检查新记录是否已经插入至教师基本信息 表中。
③ 建立一个修改教师记录的存储过程teacher_update，并执行之。
CREATE PROCEDURE teacher_update
(@no char(6), @nam varchar(8), @sex char(2), @age int, @title char(10), @tel varchar(12), @sala decimal(7), @num char(10))
AS
BEGIN
UPDATE teacher_info
SET name = @nam,
gender = @sex,
age = @age,
tech_title = @title,
telephone = @tel,
salary = @sala,
course_id = @num
WHERE teacher_id = @no
END
在SQL编辑器运行EXEC命令调用存储过程teacher_update，修改一个具体的教 师记录。
④ 建立一个删除教师信息表中记录的存储过程teacher_delete，并执行之。
CREATE PROCEDURE teacher_delete
(@no char(6))
AS
DELETE FROM teacher_info WHERE teacher_id =@no
在SQL编辑器运行EXEC命令调用存储过程teacher_delete，删除teacher_info中的 teacher_id为“010119”的教师记录。
思考练习：编写一个存储过程来对student数据库中表stud_info进行检索、插 入、修改、删除的操作，然后去调用这个存储过程。
⑵ 创建触发器
① 使用CREATE TRIGGER命令创建一个触发器teacher_tri1，当向表teacher_info中插入一条记录时，自动显示表teacher_info中的 记录。在SQL编辑器中输入触发器的代码并执行。
CREATE TRIGGER teacher_tri1
ON stud_info
FOR INSERT
AS
SELECT * FROM teacher_info
触发器建立完毕后，当执行如下操作时将会显示数据表 teacher_info中的全部记录。
INSERT INTO teacher_info
VALUES('010119', '聂晓基', '男', 40,'讲师','02034360338',250.0,'0401010108')
② 使用系统存储过程sp_helptext查看触发器teacher_tri1的定义文本信息。具体命令如下：
USE student
EXEC sp_helptext teacher_tri1
GO
在“SQL编辑器”的查询窗口中运行上面的命令，在结果窗格中将返回触发器 teacher_tri1的定义信息。
③ 在数据库student中的teacher_info上建立DELETE触发器teacher_tri2，使得在删除表teacher_info中记录的 同时，自动检查课程安排表teach_schedule中是否有该教师的记录，如果存在该教师记录，则取消删除。具体命令如下：
USE student
GO
CREATE TRIGGER teacher_tri2
ON teacher_info
FOR DELETE
AS
IF(SELECT COUNT(*) FROM
teach_schedule INNER JOIN Deleted
ON teach_schedule.teacher_id=Deleted.teacher_id)>0
BEGIN
RAISERROR('You can not delete the teacher_info with the order record.\
The transaction will be cancelled',10,1)
ROLLBACK TRANSACTION
END
在 SQL编辑器中输入上述语句后，可以使用下述命令加以验证。
DELETE FROM teacher_info WHERE teacher_id='010101'
思考练习：编写一个触发器，在对student数据库中数据表stud_info执行插入、更新和删除3 种操作后给出相应提示。

课后练习

1. 简要回答下列问题。

⑴ 什么是存储过程？请分别写出使用对象资源管理器和SQL命令创建存储过程的主要步骤。
⑵ 如何将数据传递到一个存储过程中？又如何将存储过程的结果值返回？
⑶ 同删除和重建存储过程相比，修改存储过程有什么好处？请分别写出使用对象资源管理器和SQL命令修改存储过程的主要步骤。
⑷ 写出删除存储过程stud_info_proc的命令。
⑸ 使用触发器有什么优点？
⑹ 当一个表同时具有约束和触发器时，如何执行？
⑺ 举个实例，分别创建INSERT、UPDATE、DELETE触发器。
⑻ 如果触发器运行ROLLBACK TRANSACTION命令后，引起触发器触发的操作命令是否还会有效？
2. 按照题目要求写出SQL命令，并在机器进行测试。
⑴ 创建一个存储过程，使其调用后能返回年龄为40以上职称为“副教授”的教师基本信息。
⑵ 针对学生基本信息表(stud_info)，创建一个按性别统计人数的存储过程。
⑶ 调用上述存储过程people_num，统计性别为“男”的人数。
⑷ 编写一个(teacher_cancel)触发器，当在teacher_info表中删除一个记录，将触发该触发器。在触发器中将判断老师是否已经授课？ 如果已经安排某位教师授课，它将激发一个例外，把无法删除的信息返回用户。
⑸ 编写一个(teacher_insert_update)触发器，当向teacher_info表中插入或修改一个记录时，触发器xx检查记录的 course_id值是否存在于lesson_info表中，若不存在，则取消插入或修改操作。
⑹ 编写一个(teacher_update)触发器，当修改lesson_info表的course_id字段值时，该字段在teacher_info表中 的对应值也做相应修改。
⑺ 编写一个(teacher_delete)触发器，删除lesson_info表中记录的同时，也删除该记录course_id字段值在 teacher_info表中的对应记录。
⑻ 对于student数据库，表stud_info的stud_id列与表stud_grade的stud_id列应满足参照完整性规则，请用触发器实现两 个表间的参照完整性。