第5章TransactSQL程序设计进阶

TransactSQL包含许多SQL不具备的扩展编程功能，主要包括批处理、脚本、流程控制语句，以及存储过程和触发器等，本章主要介绍这些内容。
5.1批处理和脚本
批处理是数据库系统中很重要的一个功能，能够对系统的性能进行有效优化。脚本将一组TransactSQL语句以文本文件的形式存储，能够提高数据访问的效率，并进行相关的数据处理。事务是数据库系统上执行并发操作时的最小控制单元，能将逻辑单元的一组操作绑定在一起，便于使服务器保持数据的完整性。
5.1.1批处理概述
1. 批处理的概念

批处理是指一次性地执行包含一条或多条TransactSQL语句的语句组，它表示用户提交给数据库引擎的工作单元。批处理是作为一个单元进行分析和执行的，它要经历的处理阶段有分析(语法检查)、解析(检查引用的对象和列是否存在、是否具有访问权限)、优化(作为一个执行单元)。SQL Server将批处理语句编译为单个可执行的单元，称为执行计划，执行计划中的语句每次执行一条，这种批处理方式有助于节省执行时间。
在书写批处理时，GO语句作为批处理命令的结束标志，当编译器读取到GO语句时会将GO语句前的所有语句当作一个批处理，并将这些语句打包发送给服务器。GO语句本身不是TransactSQL语句的组成部分，只是一个表示批处理结束的前端指令。
2. 批处理的规则
下面的规则适用于批处理的使用： 
(1) 不能被组合在同一个批处理中的语句包括创建默认值(CREATE DEFAULT)、创建函数(CREATE FUNCTION)、创建存储过程(CREATE PROCEDURE)、创建规则(CREATE RULE)、创建模式(CREATE SCHEMA)、创建触发器(CREATE TRIGGER)和创建视图(CREATE VIEW)。
(2) 批处理以CREATE语句开始，所有跟在该批处理后的其他语句将被解释为第一个CREATE语句定义的一部分。




(3) 不能在同一个批处理中更改表结构(例如修改字段名、新增字段、新增或更改约束等)，然后引用新列。因为SQL Server可能还不知道架构定义发生了变化，导致出现解析错误。
(4) 不能在同一个批处理中删除一个对象之后再次引用该对象。
(5) 不可在将规则和默认值绑定到表字段或自定义字段上之后立即在同一个批处理中使用。
(6) 使用SET语句设置的某些SET选项不能应用于同一个批处理中的查询。
(7) 如果批处理中的第一个语句是执行某个存储过程的EXECUTE语句，则EXECUTE关键字可以省略。如果EXECUTE语句不是批处理中的第一条语句，则EXECUTE关键字必须保留。
3. 指定批处理的方法
指定批处理的方法有以下4种:
(1) 应用程序作为一个执行单元发出的所有SQL语句构成一个批处理，并生成单个执行计划。
(2) 存储过程或触发器内的所有语句构成一个批处理，每个存储过程或者触发器都编译为一个执行计划。
(3) 由EXECUTE语句执行的字符串是一个批处理，并编译为一个执行计划。
(4) 由SP_EXECUTESQL存储过程执行的字符串是一个批处理，并编译为一个执行计划。
用户需要注意以下几点： 
(1) 若应用程序发出的批处理过程中含有EXECUTE语句，则已执行字符串或存储过程的执行计划将和包含EXECUTE语句的执行计划分开执行。
(2) SP_EXECUTESQL存储过程所执行的字符串生成的执行计划与SP_EXECUTESQL调用的批处理执行计划分开执行。
(3) 若批处理中的语句激活了触发器，则触发器的执行将和原始的批处理分开执行。
4. 批处理的结束与退出
1) 执行批处理语句
用EXECUTE语句执行标量值的用户自定义函数、系统过程、用户自定义存储过程或扩展存储过程,同时支持TransactSQL批处理内字符串的执行。
2) 批处理结束语句
执行计划中的语句逐条执行，每次执行一条。所有的批处理语句都以GO命令作为结束的标志。当编译器读到GO时，它会把GO前面所有的语句作为一个批处理进行处理，并打包成一个数据包发送给服务器。
GO命令和TransactSQL语句不能在同一行，否则无法识别。但在GO命令行中可包含注释。用户必须遵照使用批处理的规则。
SQL Server 2005以及更高的版本中对GO命令这个客户端工具进行了增强，让它可以支持一个正整数参数，表示GO之前的批处理将执行指定的次数。当需要重复执行批处理时，就可以使用这个增强后的命令。该命令使用的语法格式为： 



GO ［count］



其中，count为一个正整数，用于指定GO之前的批处理将执行的次数。
【例51】查询StudentMIS数据库中学生的基本信息。



USE StudentMIS--将当前使用的数据库切换到"StudentMIS"

GO 

SELECT * FROM Student   --从Student表中查询学生信息

GO



3) 批处理退出语句
批处理退出语句的基本语法格式为： 



RETURN ［整型表达式］



该语句可无条件终止查询、存储过程或批处理的执行。存储过程或批处理不执行RETURN之后的语句。当存储过程使用该语句时，RETURN语句不能返回空值。用户可用该语句指定返回调用应用程序、批处理或存储过程的整数值。
5.1.2脚本
脚本是存储在文件中的一系列TransactSQL语句，是一系列顺序提交的批处理，脚本文件保存时的扩展名为.sql，该文件是一个纯文本文件。在脚本文件中可包含一个或多个批处理，GO作为批处理结束语句，若脚本中无GO语句，则作为单个批处理。
使用脚本可以将创建和维护数据库时进行的操作保存在磁盘文件中，方便以后重复使用该段代码，还可以将此代码复制到其他计算机上执行。因此，对于经常操作的数据库，保存相应的脚本文件是一个良好的使用习惯。
5.2流程控制语句
一般结构化程序设计语言的基本结构有顺序结构、条件分支结构和循环结构。TransactSQL语言也提供了类似的功能。TransactSQL提供了称为流程控制的特殊关键字，用于控制TransactSQL语句、语句块或存储过程的执行流程。
流程控制语句就是用来控制程序执行流程的语句，使用流程控制语句可以在程序中组织语句的执行流程，提高编程语言的处理能力。SQL Server提供的流程控制语句如表51所示。


表51流程控制语句




流程控制语句说明

BEGIN…END定义语句块
BREAK跳出循环语句
CASE分支语句
CONTINUE重新开始循环语句
GOTO无条件跳转语句
IF…ELSE条件处理语句，如果条件成立，执行IF语句； 否则执行ELSE语句
RETURN无条件退出语句
WAITFOR延迟语句
WHILE循环语句


5.2.1BEGIN…END语句块
BEGIN…END语句用于将多个TransactSQL语句组合为一个逻辑块。在执行时，该逻辑块作为一个整体被执行。其语法格式如下： 



BEGIN

{sql_statement | statement_block} 

END



其中，BEGIN和END是控制语句的关键字，分别表示语句块的开始和结束； {sql_statement|statement_block}是任何有效的TransactSQL语句或以语句块定义的语句分组。在任何时候，当流程控制语句必须执行一个包含两条或两条以上TransactSQL语句的语句块时，都可以使用BEGIN和END语句。它们必须成对使用，任何一条语句均不能单独使用。BEGIN…END语句块的功能类似于程序设计语言中的{…}。此外，BEGIN…END语句块可以嵌套使用。
下面几种情况经常要用到BEGIN…END语句块： 
(1) WHILE循环需要包含语句块。
(2) CASE函数的元素需要包含语句块。
(3) IF或ELSE子句需要包含语句块。
在上述情况下，如果只有一条语句，则不需要使用BEGIN…END语句块。
5.2.2IF…ELSE语句
使用IF…ELSE语句，可以有条件地执行语句。其语法格式如下： 



IF  <逻辑表达式>

<Transact-SQL语句或用语句块定义的语句分组>

［ELSE

<Transact-SQL语句或用语句块定义的语句分组>］



其中，<逻辑表达式>可以返回TRUE或FALSE。如果<逻辑表达式>中含有SELECT语句，必须用圆括号将SELECT语句括起来。
IF…ELSE语句的执行方式是： 如果逻辑表达式的值为TRUE，则执行IF后面的语句块； 否则执行ELSE后面的语句块。
在IF…ELSE语句中，IF和ELSE后面的子句都允许嵌套，嵌套层数不受限制。但是，嵌套最好不要超过3层，否则会降低程序的可读性。
5.2.3CASE语句
使用CASE语句可以实现程序的多重分支选择。虽然使用IF…ELSE语句也能够实现多重分支结构，但是使用CASE语句的程序可读性更强。在SQL Server 2016中，CASE语句有两种格式。
(1) 简单CASE语句： 将某个表达式与一组简单表达式进行比较以确定结果。其语法格式为： 



CASE  <输入表达式>

WHEN  <when表达式>  THEN  <结果表达式>

WHEN  <when表达式>  THEN  <结果表达式>

［…n］

［ELSE  <else结果表达式>］

END




简单CASE语句的执行过程为： 将<输入表达式>与各个<when表达式>进行比较，如果相等，则返回对应的<结果表达式>的值，然后跳出CASE语句，不再执行后面的语句； 如果没有<输入表达式>等于<when表达式>的值，则返回<else结果表达式>的值。如果没有ELSE子句，则返回NULL。需要注意的是，各条件分支返回的<结果表达式>的数据类型必须一致，最好明确地写上ELSE子句。
【例52】显示出版社数据库pubs中作者所在州的情况。



SELECT au_fname, au_lname, 

CASE state

WHEN 'CA' THEN 'California'

WHEN 'KS' THEN 'Kansas'

WHEN 'TN' THEN 'Tennessee'

WHEN 'MI' THEN 'Michigan'

WHEN 'IN' THEN 'Indiana'

WHEN 'MD' THEN 'Maryland'

END AS StateName 

FROM authors WHERE au_fname LIKE 'M%'



执行结果如下： 



au_fnameau_lnameStateName

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

MarjorieGreen 		California

Michael O'Leary 	California

Meander   	Smith 	Kansas

Morningstar 	Greene 	Tennessee

Michel 		DeFrance Indiana




(2) 搜索CASE语句： 计算一组布尔表达式以确定结果。其基本语法格式为： 



CASE

WHEN  <逻辑表达式> THEN  <结果表达式>

WHEN  <逻辑表达式> THEN  <结果表达式>

［ …n］

［ ELSE  <else结果表达式>］

END



搜索CASE语句的执行过程为： 如果<逻辑表达式>的值为TRUE，则返回THEN后面的<结果表达式>，然后跳出CASE语句； 否则继续测试下一个WHEN后面的<逻辑表达式>。如果所有的WHEN后面的<逻辑表达式>均为FALSE，则返回ELSE后面的<else结果表达式>。如果没有ELSE子句，则返回NULL。WHEN子句中的“<逻辑表达式>”就是类似“列=值”这样，返回值为真值(TRUE、FALSE、UNKNOWN)的表达式。用户也可以将其看作使用=、<>或者LIKE、BETWEEN等谓词编写出来的表达式。用户在编写SQL语句的时候需要注意，在发现为真的WHEN子句时，CASE表达式的真假值判断就会中止，而剩余的WHEN子句会被忽略。为了避免引起不必要的混乱，在使用WHEN子句时要注意条件的排他性。
【例53】在StudentMIS数据库中，采用“优”“良”“中”“差”“不及格”五级打分制来显示5名学生成绩表的情况，结果如图51所示。



SELECT TOP 5 StuNo AS 学号, CNo AS 课程号,

CASE

WHEN Score>=90 THEN '优'

WHEN Score>=80 THEN '良'

WHEN Score>=70 THEN '中'

WHEN Score>=60 THEN '差'

ELSE '不及格'

END

AS '成绩等级'

FROM StudentMIS.dbo.SC



执行结果如图51所示。
【例54】在StudentMIS数据库中，统计每门课程选修的男生总数和女生总数，结果如图52所示。



SELECT CNo AS课程号,

SUM( CASE WHEN sex='男' THEN 1 ELSE 0 END) AS男生数,

SUM (CASE WHEN sex='女' THEN 1 ELSE 0 END) AS女生数

FROM Student AS S INNER JOIN SC ON S.StuNo=SC.StuNo GROUP BY CNo






图51例5.3的查询结果




图52例5.4的查询结果



上面这段代码所做的是分别统计选修每门课程的“男生”(即sex='男')人数和“女生”(即sex='女')人数。也就是说，这里是将“行结构”的数据转换成了“列结构”的数据。除了SUM()，COUNT()、AVG()等聚合函数也都可以用于将行结构的数据转换成列结构的数据。
【例55】在StudentMIS数据库中，为了使学生的成绩更符合正太分布且方差尽可能小，将成绩高于90分的降低5%，将成绩低于80分的提高10%。



UPDATE SC SET

Score=CASE WHEN Score >90 THEN Score*0.95

WHEN Score < 80 THEN Score*1.1

ELSE Score END





这样通过一条语句即可实现上述业务规则。尽管对例5.5分别执行下面两个UPDATE操作的结果与上面的执行效果是一致的，但这种逻辑是不正确的。问题在于，第一次的UPDATE操作执行后，学生的成绩发生了变化，如果继续拿它当作第二次UPDATE的判定条件，结果就会不准确。所以，例5.5只能使用CASE语句进行成绩的更新。




UPDATE SC SET Score=Score * 0.95 WHERE Score >90  --条件1

UPDATE SC SET Score=Score * 1.1 WHERE Score < 80      --条件2




需要注意的是，SQL语句最后一行的ELSE Score非常重要，必须写上。因为如果没有它，Score介于80分和90分之间的学生成绩就会被更新成NULL。
5.2.4WHILE语句
WHILE语句可以设置重复执行SQL语句或语句块的条件。只要指定的条件为真，就重复执行语句。用户可以使用BREAK和CONTINUE关键字在循环内部控制WHILE循环中语句的执行。其语法格式如下： 



WHILE  <逻辑表达式>

BEGIN

<Transact-SQL语句或用语句块定义的语句分组>

［ BREAK ］

［ CONTINUE ］

<Transact-SQL语句或用语句块定义的语句分组>

END




其中，BREAK语句无条件地退出WHILE循环，即从最内层的WHILE循环中退出，将执行出现在END关键字后面的语句，END关键字为循环结束标记。CONTINUE结束本次循环，进入下次循环，也就是使WHILE循环重新开始执行，忽略CONTINUE关键字后面的任何语句。
WHILE语句的执行方式为： 如果逻辑表达式的值为TRUE，则反复执行WHILE语句后面的语句块； 否则将跳过后面的语句块。
【例56】计算并输出1+2+3+…+100的和。



DECLARE @sum int, @i int

SET @i=0

SET @sum=0

WHILE @i<=100

BEGIN

SET @sum=@sum+@i

SET @i=@i+1

END

PRINT  '1~100的和为:'+CAST(@sum AS char(25))





执行结果为： 



1~100的和为： 5050



5.2.5GOTO语句
GOTO语句可以实现无条件跳转，让执行流程跳转到SQL代码中的指定标签处。其语法格式为： 



GOTO标签名



GOTO语句的执行方式为： 遇到GOTO语句后，直接跳转到“标签名”处继续执行，而GOTO后面的语句将不被执行。
5.2.6RETURN语句
使用RETURN语句，可以从查询或过程中无条件退出。RETURN语句可在任何时候用于从过程、批处理或语句块中退出，而不执行位于RETURN之后的语句。其语法格式为： 



RETURN ［整数值］



5.2.7WAITFOR语句
使用WAITFOR语句，可以在指定的时间或者过了一定时间后执行语句块、存储过程或者事务。其语法格式为： 



WAITFOR { DELAY <'时间'> | TIME <'时间'>} 



DELAY指示SQL Server一直等到指定的时间过去，最长可达24小时。'时间'是要等待的时间。用户可以按datetime数据可接受的格式指定'时间'，也可以用局部变量指定此参数。注意不能指定日期，因此在datetime值中不允许有日期部分。TIME指示SQL Server等待到指定时间。
【例57】等待30秒后对SC表执行SELECT语句。



WAITFOR DELAY '00:00:30'

SELECT *FROM StudentMIS.dbo.SC



【例58】指定在15∶30∶00时执行一个输出当前时间的语句。



WAITFOR TIME  '15:30:00'

PRINT '现在是15:30:00'



执行后，等计算机上的时间到了15∶30∶00时将出现结果“现在是15∶30∶00”。
5.2.8TRY…CATCH语句
TRY…CATCH语句实现类似于Java和C++语言中的异常处理。TransactSQL语句或用语句块定义的语句分组可以包含在TRY中，如果TRY内部发生错误，则会将控制传递给CATCH中包含的另一个语句分组。其语法格式如下： 



BEGIN TRY

<Transact-SQL语句或用语句块定义的语句分组>

END TRY

BEGIN CATCH

<Transact-SQL语句或用语句块定义的语句分组>

END CATCH



在CATCH模块中，可以使用下面的函数来实现错误处理。
(1) ERROR_NUMBER()：返回错误号。
(2) ERROR_MESSAGE()：返回错误消息的完整文本。
(3) ERROR_SEVERITY()：返回错误严重性。
(4) ERROR_STATE()：返回错误状态号。
(5) ERROR_LINE()：返回导致错误的例程中的行号。
(6) ERROR_PROCEDURE()：返回出现错误的存储过程或触发器的名称。
5.2.9PRINT语句
PRINT语句用于向客户端返回用户信息。PRINT语句只允许显示常量、表达式或变量，不允许显示列名。它的语法格式如下： 



PRINT 字符串|局部变量|字符串表达式




5.3存储过程
5.3.1存储过程概述
1. 存储过程的概念


存储过程(Stored Procedure)是一组为了完成特定功能、可以接收和返回用户参数的TransactSQL语句的预编译集合，经过编译后存储在数据库中，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，用户通过指定存储过程名及给出参数进行调用执行，而且允许用户声明变量、带参数执行以及拥有其他强大的编程功能。存储过程在第一次执行时进行语法检查和编译，执行后它的执行计划就驻留在高速缓存中，用于后续调用。存储过程可以接受和输出参数、返回执行存储过程的状态值，还可以嵌套调用。存储过程在数据库开发过程以及数据库维护和管理等任务中有非常重要的作用。
2. 存储过程的特点
存储过程可包含流程控制、逻辑以及对数据库的查询。它们可以接收参数、输出参数、返回单个或多个结果集以及返回值。与单纯的TransactSQL语句相比，存储过程具有以下优点： 
(1) 允许模块化的程序设计。存储过程被创建、存储在数据库中，可以在程序中被多次调用。用户可以在自己的存储过程内引用其他存储过程，这可以简化一系列复杂语句； 而且存储程序可以独立于应用程序进行修改，极大地提高了程序的可移植性。
(2) 执行速度快，改善系统性能。存储过程在创建时即在服务器上进行编译和优化。程序调用一次后，它的执行计划就驻留在高速缓存中，下次调用时可以直接执行； 而批处理的TransactSQL语句在每次运行时都要进行编译和优化，因此速度相对较慢。
(3) 有效降低网络流量。用户可以在单个存储过程中执行一系列TransactSQL语句。有了存储过程，在网络上只要一条语句就能执行一个存储过程，因此有效地减少了网络流量，提高了应用程序的执行效率。
(4) 保证数据库的安全性。通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。
3. 存储过程的分类
在SQL Server 2016中存储过程分为3类,即系统存储过程、用户自定义存储过程以及扩展存储过程。
(1) 系统存储过程主要存放在master数据库中，并以“sp_”为前缀名。系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。SQL Server 2016提供了一千多个系统存储过程，下面对常用的系统存储过程做一个简单的介绍。
 sp_tables： 返回可在当前环境中查询的对象列表。这代表可在FROM子句中出现的任何对象。
 sp_stored_procedures： 返回当前环境中的存储过程列表。
 sp_rename： 在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、列、别名等数据类型。
 sp_renamedb： 更改数据库的名称。
 sp_help： 报告有关数据库对象(sys.sysobjects兼容视图中列出的所有对象)、用户定义数据类型或SQL Server 2016提供的数据类型的信息。
 sp_helptext： 表示用户定义规则的定义、默认值、未加密的TransactSQL存储过程、用户定义TransactSQL函数、触发器、计算列、CHECK约束、视图或系统对象(例如系统存储过程)。
 sp_who： 提供有关SQL Server Database Engine实例中的当前用户和进程的信息。
 sp_password： 为SQL Server登录名添加或更改密码。
(2) 用户自定义存储过程是由用户创建的、存放在用户创建的数据库中，并能完成某些特定功能的存储过程。本节主要介绍用户自定义存储过程。在SQL Server 2016中，用户自定义存储过程又分为TransactSQL存储过程和CLR存储过程两种。TransactSQL存储过程是指保存TransactSQL语句的集合，可以接收和返回用户提供的参数。CLR存储过程是针对微软公司的.NET Framework公共语言运行时(CLR)方法的引用，可以接收和返回用户提供的参数。
(3) 扩展存储过程是指使用其他编程语言(例如C语言)创建自己的外部存储过程，是SQL Server数据库的实例可以动态加载和运行的动态链接库(DLL)，扩展了SQL Server 2016的性能，常以“xp_”为前缀名，其内容并不存储在SQL Server 2016中，而是以DLL的形式单独存在。常用的扩展存储过程如下： 
 xp_cmdshell： 用来运行平常在命令提示符下执行的程序，例如DIR(显示目录)和MD(更改目录)命令等。
 xp_sscanf： 将数据从字符串读入每个格式参数所指定的参数位置。
 xp_sprintf： 设置一系列字符和值的格式并将其存储到字符串输出参数中。每个格式参数都用相应的参数替换。
存储过程与视图之间的关系如表52所示。


表52存储过程与视图之间的关系




对 比 项 目视图存 储 过 程

语句只能是SELECT语句可以包含控制流程、逻辑以及SELECT语句
输入、返回结果不能接受参数，只能返回结果集可以有输入、输出参数，也可以有返回值
典型应用多个表的连接查询完成某个特定的较复杂的任务


5.3.2创建存储过程
如果要使用存储过程，首先要创建一个存储过程，可以使用TransactSQL语句的CREATE PROCEDURE语句，也可以使用SQL Server Management Studio来完成。使用SQL Server Management Studio创建容易理解，较为简单； 使用TransactSQL语句创建较为快捷。在创建存储过程时，需要确定存储过程的3个组成部分： 
 所有的输入参数及执行后传给调用者的输出参数。
 被执行的针对数据库的操作语句，包括调用其他存储过程的语句。
 返回给调用者的状态值，以指明执行是否成功。
1. 使用SQL Server Management Studio创建存储过程
使用SQL Server Management Studio创建存储过程的操作步骤如下： 
(1) 打开SQL Server Management Studio并连接到目标服务器，在“对象资源管理器”窗口中找到“数据库”结点，打开要创建存储过程的数据库(例如StudentMIS)。
(2) 展开“可编程性”结点，然后右击“存储过程”项，在打开的快捷菜单中选择“新建存储过程”命令。此时，右侧窗口中显示了CREATE PROCEDURE语句的框架，可以修改要创建的存储过程的名称，然后加入存储过程所包含的SQL语句，如图53所示。


图53新建存储过程


(3) 在模板中输入完成后，单击工具栏上的“执行”按钮，可以立即执行SQL语句以创建存储过程。用户也可以单击“保存”按钮保存该存储过程的SQL语句。
2. 使用TransactSQL语句创建存储过程
创建存储过程的CREATE PROCEDURE语句的语法为： 



CREATE ［ PROC | PROCEDURE］ procedure_name ［; number ］

［{ @parameter data_type } 

［VARYING ］ ［=default ］ ［ OUTPUT ］ ］ ［ , …n ］ 

［WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ］

［FOR REPLICATION］ AS sql_statement ［…n ］



其中,各参数的含义如下： 
(1) procedure_name是新建存储过程的名称，必须符合标识符命名规则。
(2) ;number是可选的整数，用来对同名的存储过程分组，以便用一条DROP PROCEDURE语句即可将同组的存储过程一起删除。例如，名为student的应用程序使用的存储过程可以命名为“studentProc;1”“studentProc;2”等。DROP PROCEDURE studentProc语句将删除整个组。
(3) @parameter为存储过程的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。存储过程最多可以有2100个参数。
(4) data_type为参数的数据类型。所有数据类型均可以用作存储过程的参数。不过，游标(CURSOR)数据类型只能用于OUTPUT参数。如果指定的数据类型为CURSOR，必须同时指定VARYING和OUTPUT关键字。
(5) VARYING指定作为输出参数支持的结果集(由存储过程动态构造，内容可以变化)。该参数仅适用于游标参数。
(6) default为参数的默认值。如果定义了默认值，则不必指定该参数的值即可执行过程。默认值必须是常量或NULL。
(7) OUTPUT表明参数是返回参数。该选项的值可以返回给EXEC［UTE］。使用OUTPUT参数可将信息返回给调用过程。
(8) 在{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}中，RECOMPILE表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译； ENCRYPTION表示SQL Server加密syscomments表中包含CREATE PROCEDURE语句文本的条目。
(9) FOR REPLICATION指定不能在订阅服务器上执行为复制创建的存储过程。本选项不能和WITH RECOMPILE选项一起使用。
(10) sql_statement为过程中要包含的任意数目和类型的TransactSQL语句。
用户在创建存储过程时应该注意下面几点： 
(1) 存储过程最大为128MB。
(2) 用户自定义的存储过程只能在当前数据库中创建(临时存储过程除外，临时存储过程总是在tempdb中创建)。
(3) 在单个批处理中，CREATE PROCEDURE语句不能与其他TransactSQL语句组合使用。
(4) 存储过程可以嵌套使用，在一个存储过程中可以调用其他的存储过程。嵌套的最大深度不能超过32层。
(5) 存储过程如果创建了临时表，则该临时表只能用于该存储过程，而且当存储过程执行结束后，临时表自动被删除。
【例59】使用CREATE PROCEDURE语句创建一个存储过程studentProc，查询某籍贯某学生的情况： 



USE StudentMIS

GO

CREATE PROCEDURE studentProc

@name NVARCHAR(64), @address NVARCHAR(256)

AS SELECT * FROM Student WHERE StuName=@name AND Address=@address

GO



【例510】使用CREATE PROCEDURE语句创建计算两个整数和的存储过程SumProc，并将结果返回给用户。



CREATE PROCEDURE SumProc

@i1 INT, @i2 INT, @result INT OUTPUT

AS

SET @result=@i1+@i2



从存储过程中返回一个或多个值是通过在创建存储过程的语句中定义输出参数来实现的，通过关键字OUTPUT指明这是一个输出参数。值得注意的是，输出参数必须位于所有输入参数之后，如例5.10中输出参数@result位于最后。
5.3.3执行存储过程
存储过程与函数不同，存储过程不返回取代其名称的值，其参数不需要用括号括起，存储过程也不能直接在表达式中使用。执行存储过程使用EXECUTE语句，其完整语法格式如下： 



［ EXEC | EXECUTE ］ {

［ @return_status=］

{ procedure_name ［ ;number ］ | @procedure_name_var }

［ ［ @parameter=］ { value | @variable ［ OUTPUT ］ | ［ DEFAULT ］ }］ ［ , …n ］

［ WITH RECOMPILE ］}



其中,各参数的含义如下： 
(1) @return_status是一个可选的整型变量，用于保存存储过程的返回状态。这个变量用在EXECUTE语句前，必须在批处理、存储过程或函数中声明过。
(2) procedure_name为调用的存储过程名称。
(3) number是可选的整数，用于将相同名称的过程进行组合，使得它们可以用DROP PROCEDURE语句删除。
(4) @procedure_name_var是局部定义变量名，代表存储过程名称。
(5) @parameter是存储过程参数，在CREATE PROCEDURE语句中定义，参数名称前必须加上符号@。
(6) value是存储过程中参数的值。
(7) @variable是用来保存参数或者返回参数的变量。
(8) OUTPUT指定存储过程必须返回一个参数。该存储过程的匹配参数也必须由关键字OUTPUT创建。在使用游标变量做参数时使用该关键字。
(9) DEFAULT为根据存储过程的定义提供参数的默认值。
(10) WITH RECOMPILE为强制编译新的计划。
在调用存储过程时有两种传递参数的方法： 第一种是在传递参数时，使传递的参数和定义时的参数顺序一致，对于使用默认值的参数可以用DEFAULT代替； 第二种是采用参数名称引导的形式(例如@name='张颖')，此时各个参数的顺序可以任意排列。当输入参数较多时，建议使用参数名称引导的形式调用存储过程。
例如要执行例5.9的存储过程，使用如下语句： 



EXECUTE studentProc@name='张颖',@address='北京'--参数由参数名标识，顺序任意

或EXECUTE studentProc '张颖', '北京'--参数以位置标识，必须按照定义参数的顺序



如果要执行例5.10的存储过程，使用如下语句： 



DECLARE @answer INT

EXEC SumProc 10,20, @answer OUTPUT

SELECT @answer  '两个整数相加的结果'



存储过程在执行后都会返回一个整型值。如果执行成功，返回0； 否则返回-1~-99的数值。用户也可以使用RETURN语句来指定一个返回值。
5.3.4查看存储过程
在存储过程被创建之后，存储过程的名称被存储在系统表sysobjects中，它的源代码被存储在系统表syscomments中。用户可以使用系统存储过程来查看用户自定义的存储过程。
(1) sp_help用于显示存储过程的参数及其数据类型，其语法格式如下:



sp_help ［［@objname=］存储过程名］



(2) sp_helptext可以查看未加密的存储过程的定义信息，其语法格式如下: 



sp_helptext  ［［@objname=］存储过程名］



【例511】查看studentProc存储过程的定义信息。
可以执行下面的SQL语句： 



EXEC sp_helptext  studentProc



执行的结果如图54所示。



图54查看studentProc存储过程的定义信息


如果在创建存储过程中使用了WITH ENCYPTION选项，那么使用sp_helptext就无法看到存储过程的定义。
5.3.5修改存储过程
1. 使用SQL Server Management Studio修改存储过程

使用SQL Server Management Studio修改存储过程较简单，步骤如下： 
(1) 打开SQL Server Management Studio并连接到目标服务器，在“对象资源管理器”窗口中找到“数据库”结点，然后选择存储过程所在的数据库(例如StudentMIS)。
(2) 依次展开“可编程性”结点和“存储过程”结点，然后右击要修改的存储过程名，例如studentProc，在弹出的快捷菜单中选择“修改”命令，则会在右侧窗口中打开查询编辑器，显示该存储过程的源代码。
(3) 修改代码后重新执行，保存即可。

2. 使用TransactSQL语句修改存储过程
在SQL Server 2016中，可以使用ALTER PROCEDURE语句修改已经存在的存储过程，即直接将创建中的CREATE关键字替换为ALTER。虽然删除并重新创建该存储过程也可以达到修改存储过程的目的，但是将丢失与该存储过程相关联的所有权限。修改存储过程的语法格式如下： 



ALTER  ［ PROC | PROCEDURE］  procedure_name ［ ; number ］

［ { @parameter data_type } 

［ VARYING ］ ［=default ］ ［ OUTPUT ］ ］ ［ , …n ］ 

［ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ］

［ FOR REPLICATION ］ AS sql_statement ［…n ］



通过对ALTER PROCEDURE语句语法的分析可以看出，其与CREATE PROCEDURE语句的语法构成完全一致，各参数的说明请参考CREATE PROCEDURE语句的语法说明。
【例512】出于对安全性的考虑，对例5.9中创建的存储过程进行加密处理。



USE StudentMIS

GO

ALTER PROCEDURE studentProc

@name NVARCHAR(64), @address NVARCHAR(256) 

WITH ENCRYPTION      --增加了加密处理

AS SELECT * FROM Student WHERE StuName=@name AND Address=@address

GO



5.3.6删除存储过程
1. 使用SQL Server Management Studio删除存储过程


使用SQL Server Management Studio删除存储过程的步骤如下： 
(1) 打开SQL Server Management Studio并连接到目标服务器，在“对象资源管理器”窗口中找到“数据库”结点，然后选择存储过程所在的数据库(例如StudentMIS)。
(2) 依次展开“可编程性”结点和“存储过程”结点，然后右击要删除的存储过程名，例如studentProc，在弹出的快捷菜单中选择“删除”命令，则会弹出“删除对象”对话框，单击“确定”按钮，完成删除存储过程。
2. 使用TransactSQL语句删除存储过程
使用DROP PROCEDURE语句可以在当前数据库中删除一个或多个存储过程，其语法格式如下： 



DROP［ PROC | PROCEDURE］ procedure_name ［ , …n ］



【例513】删除例5.9创建的studentProc存储过程。



DROP PROC studentProc



5.3.7重命名存储过程
使用系统存储过程sp_rename可以重命名存储过程。其语法格式如下： 



sp_rename ［@objname=］'object_name', ［@newname=］'new_name'［,［@objtype=］'object_type'］



其中，各参数的说明如下： 
(1) ［@objname=］'object_name'为存储过程的当前名称。
（2） ［@newname=］'new_name'为要执行存储过程的新名称。
（3） ［,［@objtype=］'object_type'］为要重命名的对象的类型。当对象类型为存储过程或触发器时，其值为OBJECT。
5.4触发器
5.4.1触发器概述
1. 触发器的概念

触发器(Trigger)是一种特殊类型的存储过程，是一个在修改指定表值的数据时执行的存储过程，它的执行不是由程序显式地调用或执行，也不是手工启动，而是通过事件触发被执行。例如，当对一个表进行操作(INSERT、UPDATE或DELETE语句)时就会激活触发器的执行。但是触发器又与存储过程不同，存储过程可以由用户直接使用EXEC语句调用并执行，但是触发器不能被直接调用并执行，它只能自动执行。与存储过程相比，触发器通常可以完成一定的业务规则，用于SQL Server约束、默认值和规则的完整性检查，实施完整性和强制执行业务规则。
2. 触发器的特点
触发器作为一种非程序调用的存储过程，在应用过程中具有如下优点： 
(1) 预编译、已优化、自动执行且效率高，避免了SQL语句在网络传输后再解释的低效率。
(2) 业务逻辑封装性好，数据库中很多问题都是可以在程序代码中去实现的，但是将其分离出来在数据库中处理，这样逻辑上更加清晰，对于后期维护和二次开发的作用比较明显。
(3) 触发器可通过数据库中的相关表实现级联更改。但是，通过级联引用完整性约束可以更有效地执行这些更改。
(4) 与CHECK约束定义相比，触发器可以强制定义更为复杂的约束。与CHECK约束不同，触发器可以引用其他表中的列。例如，触发器可以使用另一个表中的SELECT比较插入或修改的数据，以及执行其他操作，例如修改数据或显示用户定义错误信息。
(5) 触发器也可以评估数据更新前后的表状态，并根据其差异采取对策。
(6) 一个表中的多个同类触发器(INSERT、UPDATE或DELETE)允许采取多个不同的对策，以响应同一个更新语句。
(7) 确保数据规范化。使用触发器可以维护非正规化数据库环境中的记录级数据的完整性。
3. 触发器的分类
按照触发事件的不同，SQL Server 2016将系统提供的触发器分为3类,即DML触发器、DDL触发器和登录触发器。在SQL Server中，可以创建CLR(Common Language Runtime，公共语言运行库)触发器，它既可以是DML触发器，也可以是DDL触发器。CLR触发器将执行在托管代码(在.NET Framework中创建并在SQL Server中加载的程序集的成员)中编写的方法，而不需要执行TransactSQL存储过程。
1) DML触发器
当数据库中发生数据操纵语言(DML)事件时将调用DML触发器。DML事件包括在指定表或视图中更新数据的INSERT语句、UPDATE语句或DELETE语句。按照DML触发器事件类型的不同，可以把SQL Server系统提供的DML触发器分成3种类型，即INSERT类型、UPDATE类型和DELETE类型。这也是DML触发器的基本类型。
DML触发器按照触发时机通常分为两类，即AFTER触发器和INSTEAD OF触发器。AFTER触发器在数据更新完成后被激活，执行顺序为： 数据表约束检查→更新表中的数据→激活触发器。INSTEAD OF触发器会取代原来要进行的操作，在数据更改之前发生，数据如何更新完全取决于触发器的内容，执行顺序为： 激活触发器→若触发器涉及数据更新，则检查表约束。
2) DDL触发器
在CREATE、ALTER、DROP和其他DDL语句上操作时发生的触发器称为DDL触发器。DDL触发器用于执行管理任务，并强制影响数据库的业务规则。它们通常在数据库或服务器中某一类型的所有命令执行时激活。
3) 登录触发器
登录触发器将为响应LOGON事件而激发触发器。与SQL Server实例建立用户会话时将触发该事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前触发。因此，来自触发器内部且通常将到达用户的所有消息传送到SQL Server错误日志。如果身份验证失败，将不触发登录触发器。
任何触发器都可以包含影响另外一个表的INSERT、UPDATE或DELETE语句。当允许触发器嵌套时，一个触发器可以修改触发第二个触发器的表，第二个触发器又可以触发第三个触发器。在默认情况下，系统允许触发器最多嵌套32层。
4. inserted表和deleted表
在触发器执行的时候会产生两个临时表——inserted表和deleted表。它们的结构和触发器所在的表的结构相同，SQL Server 2016自动创建和管理这些表。用户可以使用这两个临时的驻留在内存中的表测试某些数据修改的效果及设置触发器操作的条件； 然而，用户不能直接对这两个表中的数据进行修改，但可以读取。触发器执行完成后，与该触发器相关的这两个表也会被删除。
deleted表用于存储DELETE和UPDATE语句所影响的行的副本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到deleted表中。deleted表和触发触发器的表中不会有相同的行。
inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。inserted表中的行是触发器表中新行的副本。
在对具有触发器的表进行INSERT、DELETE和UPDATE操作时，其操作过程如下： 
(1) 执行INSERT操作。插入触发器表中的新行被插入inserted表中。
(2) 执行DELETE操作。从触发器表中删除的行被插入deleted表中。
(3) 执行UPDATE操作。先从触发器表中删除旧行，然后再插入新行。其中被删除的旧行被插入deleted表中，插入的新行被插入inserted表中。
5.4.2创建触发器
在SQL Server中，创建触发器可以使用TransactSQL语句的CREATE TRIGGER语句，也可以使用SQL Server Management Studio来完成，本书只介绍使用TransactSQL语句创建触发器的语法，使用SQL Server Management Studio创建触发器的方法请读者自学完成。
1. 创建DML触发器
在创建DML触发器前，用户应该考虑到下列问题： 
(1) CREATE TRIGGER语句必须是批处理中的第一个语句。将该批处理中随后的其他所有语句解释为CREATE TRIGGER语句定义的一部分。
(2) 创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。
(3) 触发器为数据库对象，其名称必须遵循标识符的命名规则。
(4) 虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。
(5) 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。注意，不应引用系统表，而应使用信息架构视图。
(6) 在含有用DELETE或UPDATE操作定义的外键的表中，不能定义INSTEAD OF和INSTEAD OF UPDATE触发器。
(7) 虽然TRUNCATE TABLE语句(用于删除行)类似没有WHERE子句的DELETE语句，但它并不会激活DELETE触发器，因为TRUNCATE TABLE语句没有记录。
触发器可以由CREATE TRIGGER语句创建，其语法格式如下： 



CREATE TRIGGER trigger_name ON { table | view }

［ WITH ENCRYPTION ］

{{ FOR | AFTER | INSTEAD OF } { ［DELETE］ ［,］ ［INSERT］ ［,］ ［UPDATE］ }

［ WITH APPEND ］ ［ NOT FOR REPLICATION ］ AS 

［ { IF UPDATE (column) ［ { AND | OR } UPDATE (column) ］ ［…n ］

| IF ( COLUMNS_UPDATED () { bitwise_operator } updated_bitmask )

{ comparison_operator } column_bitmask ［…n ］

} ］

sql_statement ［…n ］

}




其中,各参数的含义如下： 
(1) trigger_name为触发器的名称。
(2) table | view是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。
(3) WITH ENCRYPTION为加密syscomments表中包含CREATE TRIGGER语句的条目。
(4) AFTER指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才触发。所有的引用级联操作和约束检查也必须成功完成后才能执行此触发器。如果仅指定FOR关键字，则AFTER是默认设置。注意，不能在视图上定义AFTER触发器。
(5) INSTEAD OF指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。INSTEAD OF触发器不能在WITH CHECK OPTION的可更新视图上定义。
(6) {［DELETE］ ［,］ ［INSERT］ ［,］ ［UPDATE］}是指定在表或视图上执行哪些数据更新语句时将激活触发器的关键字，必须至少指定一个选项。当向表中插入或者修改记录时，INSERT或者UPDATE触发器被执行。在一般情况下，这两种触发器常用来检查插入或者修改后的数据是否满足要求。DELETE触发器通常用于两种情况： ①防止那些确实要删除，但是可能会引起数据一致性问题的情况，一般是为那些用作其他表的外键记录。②级联删除操作。
(7) WITH APPEND指定应该添加现有类型的其他触发器。注意，只有当兼容级别是65或更低时才需要使用该可选子句。
(8) NOT FOR REPLICATION表示当复制进程更改触发器所涉及的表时不执行该触发器。
(9) AS是触发器要执行的操作。
(10) sql_statement是触发器的条件和操作。触发器条件指定其他准则，以确定DELETE、INSERT或UPDATE语句是否导致执行触发器操作。
IF子句说明了触发器条件中的列值被修改时才触发触发器。判断列是否被修改有以下两种办法。
(1) UPDATE ( column )： 参数为表或者视图中的列名称，说明这一列的数据是否被INSERT或者UPDATE操作更新过。如果更新过，返回TRUE； 否则返回FALSE。
(2) （COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask［…n］： COLUMNS_UPDATED()检测指定列是否被INSERT或者UPDATE操作更新过。它返回varbinary位模式，表示插入或修改了表中的哪些列。COLUMNS_UPDATED()函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列； 向右的下一位表示第二列，以此类推。如果在表上创建的触发器包含8列以上，则COLUMNS_UPDATED()返回多个字节，最左边的为最不重要的字节。在INSERT操作中COLUMNS_UPDATED()将对所有列返回TRUE值，因为这些列插入了显式值或隐性(NULL)值。bitwise_operator是用于比较运算的位运算符。updated_bitmask是整型位掩码，表示实际修改或插入的列。例如，表t1包含列C1、C2、C3、C4和C5。假定表t1上有UPDATE触发器，若要检查列C2、C3和C4是否都有更新，指定值14(对应二进制数为01110)； 若要检查是否只有列C2有更新，指定值2(对应二进制数为00010)。comparison_operator是比较运算符。使用等号(=)检查updated_bitmask中指定的所有列是否都实际进行了更新，使用大于号(>)检查updated_bitmask中指定的任一列或某些列是否已更新。column_bitmask是要检查列的整型位掩码，用来检查是否已修改或插入了这些列。
在创建触发器时需要指定下面的选项： 触发器的名称，必须遵循标识符的命名规则； 在其上定义触发器的表； 触发器将何时激活； 激活触发器的数据修改语句，有效选项为INSERT、UPDATE或DELETE； 多个数据修改语句可激活同一个触发器，例如触发器可由INSERT或UPDATE语句激活； 执行触发操作的编程语句。
1) INSERT触发器
INSERT触发器通常被用来验证被触发器监控的字段中的数据满足要求的标准，以确保数据完整性。
【例514】为StudentMIS数据库中的Student表创建一个名为tr_student_ins的INSERT触发器，在用户插入记录时，该触发器被触发，并自动显示表中的内容。



USE StudentMIS

GO

/*如果触发器tr_student_ins存在，则删除*/

IF EXISTS (SELECT name FROM sysobjects WHERE name='tr_student_ins' AND type='TR')

DROP TRIGGER tr_student_ins

GO

/*创建触发器tr_student_ins*/

CREATE TRIGGER tr_student_ins ON Student FOR INSERT AS

SELECT * FROM Student

GO




单击“执行”按钮，创建该触发器。后面例子中的触发器也需要输入代码后单击“执行”按钮创建，这里不再赘述。
说明： 
(1) 当触发INSERT触发器时，新的数据记录就会被插入触发器所在的表和inserted表中。inserted表包含了INSERT语句中已记录的插入动作。
(2) 触发器通过检查inserted表来确定是否执行触发器动作或如何执行它。
2) DELETE触发器
当触发DELETE触发器后，SQL Server 2016将被删除的记录转存到deleted表中，此时触发器所在的表中将不再存在该记录。也就是说，触发器所在的表和deleted表中不可能有相同的记录信息。临时表deleted存放在内存中，以提高系统性能。
【例515】为StudentMIS数据库中的Student表创建一个名为tr_student_del的DELETE触发器，因为SC表中包含学生的学号和成绩，如果还存在一个Student表，其中包含学生的学号和姓名，它们之间以学号相关联。如果要删除Student表中的一条记录，则与该记录的学号对应的学生成绩也应该删除。



USE StudentMIS

GO

IF EXISTS (SELECT name FROM sysobjects WHERE name='tr_student_del' AND type='TR')

DROP TRIGGER tr_student_del

GO

CREATE TRIGGER tr_student_del ON Student AFTER DELETE AS

DELETE FROM SC WHERE SC.StuNo=deleted.StuNo

GO



此时，要删除Student表中的记录，则SC表中对应的记录也被删除。如果使用SELECT语句来查询SC表，将看到对应的记录已经被删除，这样就保证了数据的参照完整性。
3) UPDATE触发器
UPDATE触发器的工作过程相当于删除一条旧的记录，插入一条新的记录。因此，可将UPDATE语句看成两步操作： 
(1) 捕获更新前的记录的DELETE语句。
(2) 捕获更新后的记录的INSERT语句。
当在定义有触发器的表上执行UPDATE语句时，更新前的记录被存储到deleted表，更新后的记录被存储到inserted表。
【例516】为StudentMIS数据库中的Student表创建一个名为tr_student_update的UPDATE触发器，用户对Student表执行更新操作后触发，并返回更新的记录信息。



USE StudentMIS

GO

IF EXISTS (SELECT name FROM sysobjects WHERE name='tr_student_update' AND type='TR')

DROP TRIGGER tr_student_update







GO

CREATE TRIGGER tr_student_update ON Student AFTER UPDATE AS

BEGIN

SELECT StuNo AS 更新前学号, StuName 更新前姓名 FROM deleted

SELECT StuNo AS 更新后学号, StuName 更新后姓名 FROM inserted

END

GO



4) INSTEAD OF触发器
与前面介绍的3种AFTER触发器不同，SQL Server服务器在执行触发AFTER触发器的SQL代码后，先建立临时的inserted和deleted表，然后执行SQL代码中对数据的操作，最后才激活触发器中的代码。而对于INSTEAD OF触发器，SQL Server服务器在执行触发INSTEAD OF触发器的代码时，先建立临时的inserted和deleted表，然后直接触发INSTEAD OF触发器，而拒绝执行用户输入的DML操纵语句。基于多个基本表的视图必须使用INSTEAD OF触发器来支持引用多个表中数据的插入、更新和删除操作。
【例517】为StudentMIS数据库中的SC表创建一个名为tr_sc_insteadof_ins的INSERT触发器，当用户插入SC表中的成绩大于100分时，拒绝插入，并给出“插入成绩不能大于100分”的提示信息。



USE StudentMIS

GO

IF EXISTS (SELECT name FROM sysobjects WHERE name='tr_sc_insteadof_ins' AND type='TR')

DROP TRIGGER tr_sc_insteadof_ins

GO

CREATE TRIGGER tr_sc_insteadof_ins ON SC INSTEAD OF INSERT AS

BEGIN

DECLARE @score decimal(18,2);

SELECT @score=(SELECT score FROM inserted)

IF @score>100

PRINT '插入成绩不能大于100分'

END

GO



2. 创建DDL触发器
在CREATE、ALTER、DROP和其他DDL语句上操作时发生的触发器称为DDL触发器。DDL触发器常用于以下情况： 防止对数据库架构进行某些更改，以响应数据库架构中的更改； 记录数据库架构中的更改或事件。
创建DDL触发器的语法格式如下:



CREATE TRIGGER  trigger_name

ON  { ALL SERVER | DATABASE }

［WITH  ENCRYPTION ］

{ FOR | AFTER } { event_type} ［ ,…n ］

AS

sql_statement



其中，各参数的说明如下： 
(1) ALL SERVER表示将DDL触发器的作用域应用于当前服务器。如果指定了该参数，则当前服务器中的任何数据库都能触发该触发器。
(2) DATABASE表示将DDL触发器的作用域应用于当前数据库。如果指定了该参数，则只有当前数据库能触发该触发器。
(3) AFTER表示事后触发器，DDL触发器没有INSTEAD OF触发器。
(4) event_type指定触发DDL触发器的TransactSQL语言事件的名称。每一个DDL事件都对应一个TransactSQL语句，DDL事件的名称是DDL语句的语法经过修改，在关键字之间包含了下画线(_)。例如删除表事件为DROP_TABLE，修改表事件为ALTER_TABLE，修改索引事件为ALTER_INDEX等。
【例518】创建一个触发器，用于防止用户删除和修改StudentMIS数据库中的任一数据表。



USE StudentMIS

GO

CREATE TRIGGER tr_deny_delete ON DATABASE    --指定作用域

FOR DROP_TABLE, ALTER_TABLE AS             --指定触发事件

BEGIN

PRINT '禁止删除或修改该数据表!'

ROLLBACK TRANSACTION

END

GO




5.4.3查看触发器
因为触发器是一种特殊的存储过程，所以可以使用查看存储过程的方法来查看触发器的内容。因此，用户可以使用系统存储过程sp_help、sp_helptext和sp_depents分别查看触发器的不同信息。
 sp_ help: 显示触发器的所有者和创建时间。
 sp_ helptext: 显示触发器的源代码。
 sp_depends: 显示该触发器参考的对象清单。
【例519】查看tr_student_ins触发器的源代码。



USE StudentMIS

GO

sp_helptext  tr_student_ins



5.4.4修改触发器
当触发器不满足需求时，可以修改触发器的定义和属性。在SQL Server中可以通过两种方式进行修改： 先删除原来的触发器，再重新创建与之同名的触发器； 直接修改现有触发器的定义。修改触发器的定义可以使用ALTER TRIGGER语句。
1. 修改DML触发器
修改DML触发器可以使用ALTER TRIGGER语句，其语法格式如下： 




ALTER TRIGGER trigger_name ON (table | view) 

［ WITH ENCRYPTION ］ 

{ ( FOR | AFTER | INSTEAD OF ) { ［ DELETE ］ ［ , ］ ［ INSERT ］ ［ , ］ ［ UPDATE ］ }

［ NOT FOR REPLICATION ］ AS sql_statement ［…n ］ } |

{ ( FOR | AFTER | INSTEAD OF ) { ［ INSERT ］ ［ , ］ ［ UPDATE ］ }

［ NOT FOR REPLICATION ］

AS { IF UPDATE (column) ［ { AND | OR } UPDATE (column) ］ ［…n ］ 

| IF ( COLUMNS_UPDATED () { bitwise_operator } updated_bitmask ) { comparison_operator }

column_bitmask ［…n ］ } sql_statement ［…n ］ }




各参数的含义和CREATE TRIGGER语句相同，这里不再介绍。
2. 修改DDL触发器
修改DDL触发器的语法格式如下:



ALTER TRIGGER trigger_name

ON  { ALL SERVER | DATABASE }

［WITH  ENCRYPTION ］

{ FOR | AFTER } { event_type} ［ ,…n ］

AS

sql_statement




5.4.5删除触发器
当触发器不再使用时，可以将其删除。删除触发器不会影响其操作的数据表，而当某个表被删除时，该表上的触发器也同时被删除。用户可以使用DROP TRIGGER语句来删除触发器，其语法格式如下： 



DROP TRIGGER { trigger_name } ［ ,…n ］--删除DML触发器

DROP TRIGGER { trigger_name } ［ ,…n ］  ON  { ALL SERVER | DATABASE }--删除DDL触发器



其中，trigger_name是要删除的触发器名称，而n是表示可以指定多个触发器的占位符。
【例620】删除DML触发器tr_student_ins。
在查询编辑器中执行下面的TransactSQL语句： 



DROP TRIGGER tr_student_ins



5.4.6重命名触发器
重命名触发器使用sp_rename命令，其语法格式为： 



sp_rename ［@objname=］'object_name', ［@newname=］'new_name'［,［@objtype=］'object_type'］



其参数说明和用例请参考重命名存储过程的说明，在此不再赘述。
5.4.7启用和禁用触发器
在默认情况下，触发器创建之后便启用了，如果暂时不需要使用某个触发器，可以将其禁用。触发器被禁用后并没有删除，它仍然作为对象存储在当前数据库中。
1. 禁用触发器
当不再需要某个触发器时可将其禁用。禁用触发器可以使用ALTER TABLE语句或者DISABLE TRIGGER语句。使用DISABLE TRIGGER语句的语法格式如下:



DISABLE TRIGGER {ALL|trigger_name ［,…n］}

ON {table | view | DATABASE | ALL SERVER}



2. 启用触发器
已禁用的触发器可以被重新启用。启用触发器可以使用ALTER TABLE语句或者ENABLE TRIGGER语句。使用ENABLE TRIGGER语句的语法格式如下:



ENABLE TRIGGER {ALL| trigger_name［,…n］}

ON {table | view | DATABASE | ALL SERVER}



5.5本章知识点小结
批处理是一次性将多个TransactSQL语句发送给服务器以完成执行的工作方式，这有助于节省语句的执行时间。脚本是指存储在文件中的一系列SQL语句，将常用的TransactSQL语句保存为脚本文件，可方便以后重复使用或复制到其他计算机上执行。
TransactSQL中提供了一些常用的流程控制语句，通过这些语句使得TransactSQL除了具备标准SQL的优点之外，还实现了顺序、选择、循环等程序结构的流程控制。
本章介绍了存储过程与触发器的概念、特点和作用，介绍了创建和管理存储过程与触发器的方法与技巧。存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。触发器则是一种特殊的存储过程，在对表执行INSERT、DELETE和UPDATE操作时自动执行。存储过程和触发器在数据库开发过程以及数据库维护和管理等任务中有非常重要的作用。使用存储过程和触发器可以有效地检查数据的有效性和数据的完整性、一致性。
5.6习题
1. 什么是批处理？使用批处理有何限制？
2. 什么是存储过程？存储过程分为哪几类？使用存储过程有什么好处？
3. 什么是触发器？其主要功能是什么？
4. 触发器分为哪几种？
5. INSERT触发器、UPDATE触发器和DELETE触发器有什么不同？
6. AFTER触发器和INSTEAD OF触发器有什么不同？
7. 创建一个存储过程，用于查询订单信息，包括订单日期、客户名称、定购的书籍名称、单价、数量和总价。
8. 创建一个触发器，用于在向author表或者修改插入数据时检查telephone字段的长度不大于13位(必须为区号＋电话号码的格式，例如047111111111)。