《SQL Server数据库系统基础》课件009

第9章存储过程和触发器9.1存储过程概述9.2创建存储过程9.3执行存储过程9.4查看和修改存储过程9.5重命名和删除存储过程9.6触发器9.7创建触发器9.8查看、修改和删除触发器9.9触发器的应用9.10实战训练小结

思考题

在前面的章节中，我们已经学习了数据库的编程，但是，如何来保存和方便地运行用户存储过程呢？这是我们进一步要关心的问题。本章任务：学会查找和使用系统存储过程，编写和运行用户存储过程，学会触发器的设计和应用。9.1存储过程概述9.1.1存储过程的概念

SQLServer的存储过程类似于编程语言中的过程。使用Transact-SQL语言编程可以把某些需要多次调用以实现某个特定任务的代码段编写成一个程序块，将其保存在数据库中，在需要使用时由SQLServer服务器通过它们的名称来调用，这样的程序块就叫做存储过程。存储过程在创建时经过语法检查被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。存储过程有以下特点：

(1)存储过程可以包含一条或多条Transact-SQL语句。(2)存储过程可以接受输入参数并返回输出值。

(3)一个存储过程可以调用另一个存储过程。

(4)存储过程会返回执行情况的状态代码给调用它的程序。9.1.2存储过程的优点使用存储过程有很多优点，具体如下：

(1)执行速度快。存储过程在创建时已经通过语法检查和编译，调用时可直接执行，程序的运行效率高，其执行速度要比标准SQL语句快得多。当含有大量SQL语句的批处理需要重复多次执行时，定义为存储过程可大大提高运行效率。

(2)有利于模块化程序设计。存储过程创建后可多次调用。可根据不同的功能模式设计不同的存储过程以供调用。(3)便于程序的维护和管理。当用户对数据库的使用功能改变时，只需对相应的存储过程进行修改而不用修改应用程序。

(4)减少网络通信量。存储过程可包含大量对数据库进行复杂操作的SQL语句，它的存储执行都在SQLServer服务器(数据库)端，网络用户使用时只需发送一个调用语句就可以实现，大大减少了网络上SQL语句的传输。

(5)保证系统的安全性。可以在存储过程中设置用户对数据的访问权限，只允许用户调用存储过程而不允许直接对数据进行访问，以充分发挥安全机制的作用。

(6)具有业务逻辑的保密性。对存储过程中代表业务逻辑的程序语句可以加密，存储过程一旦加密，即使是系统管理员也难以解密。9.1.3存储过程的分类

SQLServer中的存储过程可分为两类，即系统存储过程和用户自定义存储过程。

(1)系统存储过程：安装数据库系统时由系统自动创建，主要存储在master数据库中，以sp_或xp_为前缀。系统存储过程的功能主要是从系统表中获取信息，通过系统存储过程，SQLServer中的许多管理性或信息性的活动都可以被顺利而有效地完成。可以在其他数据库中调用系统存储过程，在调用时不必在存储过程名前加上数据库名。

(2)用户自定义存储过程：由用户创建并完成某一特定功能的存储过程。下面介绍用户自定义存储过程的创建和使用方法。9.2创建存储过程创建存储过程应遵守的规则如下：

(1)作为存储过程名称的标识符其长度最大为128个字符，且必须唯一。

(2)每个存储过程最多可以使用1024个参数。

(3)存储过程的最大容量有一定的限制。

(4)存储过程支持多达32层嵌套。

(5)在对存储过程命名时，最好与系统存储过程名加以区分。在SQLServer中，可以使用以下三种方法创建存储过程。

(1)使用SQLServer企业管理器创建存储过程。

(2)使用创建存储过程向导创建存储过程。(3)使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。默认情况下，创建存储过程的许可权归数据库的所有者，数据库的所有者可以把许可权授给其他用户。当创建存储过程时，需要确定存储过程的以下三个组成部分：

(1)所有输入参数以及传给调用者的输出参数。

(2)被执行的针对数据库的操作语句，包括调用其它存储过程的语句。

(3)返回给调用者的状态值，以指明调用是成功还是失败。9.2.1使用企业管理器创建存储过程使用SQLServer企业管理器创建存储过程的步骤如下：

(1)在SQLServer企业管理器中，选择指定的服务器和数据库，右击要创建存储过程的数据库，在弹出的快捷菜单中依次选择“新建”→“存储过程”选项，如图9-1所示，或者右击数据库中的存储过程图标，从弹出的快捷菜单中选择“新建存储过程”选项，如图9-2所示，均会出现“新建存储过程”对话框，如图9-3所示。图9-1选择新建存储过程窗口(1)图9-2选择新建存储过程窗口(2)图9-3“新建存储过程”对话框(2)在文本框中可以输入创建存储过程的Transact-SQL语句，这里创建一个名称为“显示学生信息”的存储过程，输入的代码如下：

CREATEPROCEDURE显示学生信息ASSELECT班级表.班级名称,学生表.学号,学生表.姓名,学生表.性别,

学生表.出生年月

FROM学生表INNERJOIN班级表ON班级表.班级编号=学生表.

班级编号存储过程“显示学生信息”完成的功能是：在学生表中查询学生的姓名、性别、出生年月、地址身份证、学号和班级名称等字段的内容。

(3)输入完毕单击“检查语法”按钮进行语法检查，检查成功后系统会弹出如图9-4所示的提示信息框。图9-4语法检查提示信息框(4)在提示信息框中单击“确定”按钮，再在存储过程属性框中单击“确定”按钮保存该存储过程，并关闭该对话框。9.2.2使用Transact-SQL语句创建存储过程可以使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。创建存储过程前，应该注意下列事项：

(1)不能将CREATEPROCEDURE语句与其他SQL语句组合到单个批处理中。

(2)创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。

(3)存储过程是数据库对象，其名称必须遵守标识符规则。

(4)只能在当前数据库中创建存储过程。

创建存储过程的Transact-SQL语句其语法形式如下：

CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][，…n]；

WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}[FORREPLICATION]ASsql_statement[,…n]其中，各参数的说明如下：● procedure_name：用于指定所要创建存储过程的名称。存储过程的命名必须符合标识符命名规则。在一个数据库中或者对其所有者而言，存储过程的名称必须唯一。● @parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个声明参数的值(除非定义了该参数的默认值)。存储过程最多可以有2100个参数。● data_type：用于指定参数的数据类型。在存储过程中，所有的数据类型(包括text、ntext和image)均可以用作存储过程的参数。● VARYING：用于指定作为输出OUTPUT参数支持的结果集(由存储过程动态构造，内容可以变化)。该参数仅适用于游标参数。● Default：用于指定参数的默认值。如果定义了默认值，则不必指定该参数的值即可执行过程。默认值必须是常量或空值。如果过程对该参数使用LIKE关键字，那么默认值中可以包含通配符(%、_、[]和[^])。● OUTPUT：表明该参数是一个返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可将信息返回给调用过程。text、ntext和image参数可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。● RECOMPILE：表明SQLServer不会保存该存储过程的执行计划，该存储过程每执行一次都要重新编译。● ENCRYPTION：表示对存储过程文本进行加密。在系统表syscomments中的text字段是包含CREATEPROCEDURE语句的存储过程文本。使用ENCRYPTION关键字无法通过查看syscomments表来查看存储过程的内容。● FORREPLICATION：用于指定该存储过程只能在数据复制时使用。本选项不能和WITHRECOMPILE选项一起使用。● AS：用于指定该存储过程要执行的操作。● sql_statement：是存储过程中包含的任意数目和类型的Transact-SQL语句。

【例9-1】创建一个存储过程“学生成绩信息”，完成的功能是在班组表、学生表、课程表和成绩表中查询以下字段：班级名称、学号、姓名、性别、课程名称、考试成绩。

下列程序清单如图9-5上半部分所示，下半部分是选中存储过程中SELECT语句的运行结果。

--打开college数据库

USEcollege--创建存储过程

CREATEPROCEDURE学生成绩信息

ASSELECT班级表.班级名称,学生表.学号,学生表.姓名,学生表.性别,

课程表.课程名称,成绩表.考试成绩

FROM班级表INNERJOIN

学生表ON班级表.班级编号=学生表.班级编号INNERJOIN

成绩表ON学生表.学号=成绩表.学号INNERJOIN

课程表ON成绩表.课程号=课程表.课程号

GO图9-5创建与运行存储过程

【例9-2】创建一个带有参数的存储过程“学生个人成绩”，该存储过程根据传入的学生编号，在学生表中查询此学生的成绩信息。程序清单如下：

--创建存储过程

USEcollegeGOCREATEPROCEDURE学生个人成绩

@学号char(8)ASSELECTdbo.学生表.姓名,dbo.学生表.学号,dbo.课程表.课程名称,dbo.成绩表.考试成绩FROMdbo.成绩表INNERJOINdbo.学生表ONdbo.成绩表.学号=dbo.学生表.学号INNERJOINdbo.课程表ONdbo.成绩表.课程号=dbo.课程表.课程号

WHERE学生表.学号=@学号

GO--运行存储过程，查询学号为05209006的成绩。学生个人成绩'05209006'

输入上述语句并执行，将得到如图9-6所示的结果。图9-6创建与运行带输入参数存储过程【例9-3】如图9-7所示，创建一个带有参数的存储过程“计算学生年龄”，该存储过程根据传入的学生编号，在学生表中计算此学生的年龄，并根据程序的执行结果返回不同的值。程序执行成功，返回整数0；如果执行出错，则返回错误号。

--创建存储过程

USEcollegeGOalterPROCEDURE计算学生年龄

@学号char(8),@年龄tinyintOUTPUTAS--定义并初始化局部变量，用于保存返回值

DECLARE@Errint--,@年龄tinyintSET@Err=0--求此学生的年龄SELECT@年龄=YEAR(GETDATE())-YEAR(出生年月)FROM学生表WHERE学生表.学号=@学号--return@年龄--根据程序的执行结果返回不同的值,IF(@@ERROR<>0)beginSET@Err=@@ERRORRETURN@Errendgodeclare@年龄tinyintexec计算学生年龄'05209003',@年龄outputselect@年龄图9-7创建与运行带输出参数存储过程9.2.3使用向导创建存储过程使用创建存储过程向导创建存储过程的步骤如下：

(1)在企业管理器中选中某个SQLServer服务器，这里选中local(代表本地机)，选择要创建存储过程的数据库，这里选中college。选择工具菜单中的“向导”菜单项，系统会弹出“选择向导”对话框，单击向导中“数据库”选项左边的加号，选中“创建存储过程向导”选项，如图9-8所示。

(2)单击“确定”按钮，出现“欢迎使用创建存储过程向导”对话框，如图9-9所示。

(3)单击“下一步”按钮，出现“选择数据库”对话框，如图9-10所示。该对话框用于选择创建存储过程中使用的数据库。图9-8“选择向导”对话框图9-9“欢迎使用创建存储过程向导”对话框图9-10“选择数据库”对话框(4)在图9-10中，选择默认的数据库college，单击“下一步”按钮，出现“选择存储过程”对话框，如图9-11所示。在该对话框中，列出了所有可选择的表，以及可以对表进行的数据库操作，即插入、删除和更新。这里要对班级表进行操作，因此选中班级表对应的插入、删除并更新下面的复选框。

(5)单击“下一步”按钮，出现确认存储过程信息对话框，如图9-12所示。如果需要修改前面的设置，则可在该对话框中单击“上一步”按钮。图9-11“选择存储过程”对话框图9-12“正在完成创建存储过程向导”对话框(6)选中名称为“insert_班级表_1”的存储过程，单击“编辑”按钮，出现“编辑存储过程属性”对话框，如图9-13所示。在该对话框中，可以在“名称”文本框中修改存储过程的名称，在下面的列表框中列出了所选表的所有字段，包括字段名称、数据类型、长度和是否在存储过程中使用。单击“编辑SQL”按钮，将会出现创建存储过程的Transact-SQL语句的编辑界面，如图9-14所示。用户可以在已有Transact-SQL语句的基础上进行编辑修改，然后单击“分析”按钮，执行语法正确性检验。

(7)在图9-14中，选中存储过程insert_班级表_1，查看它们对应的SQL语句。

insert_班级表_1存储过程对应的SQL语句如图9-14所示。图9-13“编辑存储过程属性”对话框图9-14“编辑存储过程SQL”对话框(8)编辑完各个存储过程的属性后，在图9-14中单击“确定”按钮，即可完成存储过程的创建任务。这里同时创建了三个存储过程：insert_班级表_1、update_班级表_1和delete_班级表_1，分别完成对班级表中数据的插入、修改和删除操作。创建成功后，系统会给出如图9-15所示的存储过程创建成功提示信息框。

(9)存储过程创建完成后，选中企业管理器中的college数据库，单击其中的“存储过程”，就可以看到新创建的这三个存储过程。图9-15存储过程创建成功提示信息框9.3执行存储过程存储过程创建成功后，将保存在数据库中。在SQLServer中，可以使用EXECUTE命令直接执行存储过程，其语法形式如下：

[EXEC[UTE]][@return_status=]{procedure_name|@procedure_name_var}[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n][WITHRECOMPILE]

其中，各选项的说明如下：● EXECUTE：执行存储过程的命令关键字。如果此语句是批处理中的第一条语句，则可以省略此关键字。● @return_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在使用前必须先在批处理、存储过程或函数中声明。● procedure__name：指定执行的存储过程的名称。● @procedure_name_var：局部定义变量名，代表存储过程名称。● @parameter：在创建存储过程时定义的过程参数。调用时向存储过程所传递的参数值由value参数或@variable变量提供，或者使用DEFAULT关键字指定该参数的默认值，OUTPUT参数说明指定参数为返回参数。● WITHRECOMPILE：指定在执行存储过程时重新编译执行计划。

执行存储过程时，需要指定要执行的存储过程的名称和参数，使用一个存储过程去执行一组Transact-SQL语句，可以在首次运行时被编译，在编译过程中把Transact-SQL语句从字符形式转化为可执行形式。

【例9-4】执行前面创建的“显示学生信息”存储过程，它是一个无参的存储过程。程序清单如下：

USEcollegeEXEC显示学生信息或直接写存储过程的名称：

USEcollegeGO

显示学生信息

程序的执行结果如下：姓名性别出生年月电话 地址张三男1987-02-0355502103 松花江路892弄43号李四女1986-12-0656565821 共和新路423弄456号唐宝家男1987-05-0155021456 中山路332弄55号顾叶男1985-08-2656897142 沪太路555弄973号李佳诚女1988-04-0454869218 老沪太路8529弄1号王安女1986-09-1855057694 国顺路81弄120号李静女1987-12-1956421308 福州路3489弄68号李朋男1988-10-0357610246 大华新村356弄99号刘兴男1987-07-0154239187 江湾镇4087弄167号

(所影响的行数为9行)

注意：如果省略EXECUTE关键字，则存储过程必须是批处理中的第一条语句，否则会出错。【例9-5】执行存储过程“学生成绩信息”。程序清单如下：

USEcollegeEXEC学生成绩信息程序的执行结果如下：班级名称 学号姓名性别课程名称考试成绩

--------------------------------------------------------------------------------05网络1班 05209001张三男C语言 9005网络1班 05209001张三男高等数学 8805网络1班 05209001张三男邓小平理论6005网络1班 05209002李四女C语言 7005网络1班 05209002李四女高等数学 7805网络1班 05209002李四女邓小平理论8005网络1班 05209003唐宝家男C语言 8505网络1班 05209003唐宝家男高等数学 9005网络1班 05209003唐宝家男邓小平理论9005软件班 05209004顾叶男C语言 7505软件班 05209004顾叶男高等数学 7805软件班 05209004顾叶男邓小平理论 8505软件班 05209005李佳诚女C语言 9005软件班 05209005李佳诚女高等数学 8705软件班 05209005李佳诚女邓小平理论 6005软件班 05209006王安女C语言 6005软件班 05209006王安女高等数学 5905软件班 05209006王安女邓小平理论 8305网络2班 05209007李静女C语言 7205网络2班 05209007李静女高等数学 8005网络2班 05209007李静女邓小平理论 75(所影响的行数为21行)

【例9-6】执行存储过程“学生个人成绩”，该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值。程序清单如下：

USEcollegeGOEXECUTE学生个人成绩'05209006'

或

USEcollegeGOEXECUTE学生个人成绩@学号='05209006'

程序的执行结果如下：姓名学号课程名称考试成绩

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

王安05209006C语言 60

王安05209006高等数学 59

王安05209006邓小平理论 83(所影响的行数为3行)【例9-7】执行例9-3存储过程“计算学生年龄”，该存储过程有一个输入参数“学号”，另外，还有一个输出参数“年龄”。程序清单如下：

declare@年龄tinyintexec计算学生年龄'05209003',@年龄outputselect@年龄程序的执行结果如下：本程序的执行结果：

----209.4查看和修改存储过程9.4.1查看存储过程创建存储过程之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表svscornments中。可以使用企业管理器或系统存储过程查看用户创建的存储过程。

1.使用企业管理器查看用户创建的存储过程

(1)在企业管理器中，打开指定的服务器和数据库项，指定服务器下的college数据库，并单击college中的“存储过程”文件夹，此时在右窗格中就会显示出college数据库中的所有存储过程，如图9-16所示。图9-16存储过程显示窗口(2)右击要查看的存储过程，这里右击存储过程“显示学生信息”，从弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性”对话框。在此对话框中能够看到存储过程的源代码，如图9-17所示。

(3)如果从弹出的快捷菜单中依次选择“所有任务”→“显示相关性”选项，则会弹出“相关性”对话框，显示与所选择的存储过程有依赖关系的其他数据库对象的名称，如图9-18所示。图9-17“存储过程属性”对话框图9-18“相关性”对话框2.使用系统存储过程查看用户创建的存储过程除了使用企业管理器查看用户创建的存储过程外，也可以使用系统存储过程查看。可供使用的系统存储过程及其语法形式如下：●sp_help：用于显示存储过程的参数及其数据类型。

sp_help[[@obj_name=]name]

参数name为要查看的存储过程的名称。●sp_helptext：用于显示存储过程的源代码。

sp_helptext[[@obj_name=]name]

参数name为要查看的存储过程的名称。●sp_depends：用于显示和存储过程相关的数据库对象。

sp_depende[@obj_name=]'object'

参数object为要查看依赖关系的存储过程的名称。●sp_stored_procedures：用于返回当前数据库中的存储过程列表。

sp_stored_procedures[[@sp_name=]'name'][，[@sp_owner=]'owner'][，[@qualifier=]'qualifier']

其中，[@sp_name=]'name'用于指定返回目录信息的过程名；[@sp_owner=]'owner'用于指定过程所有者的名称；[@qualifier=]'qualifier'用于指定过程限定符的名称。【例9-8】使用系统存储过程查看“计算学生年龄”存储过程的参数及其数据类型。程序清单如下：

--查看参数及其数据类型

USEcollegeGOsp_help计算学生年龄

GO

程序的执行结果如图9-19所示。图9-19程序执行结果显示窗口【例9-9】使用系统存储过程查看“学生成绩信息”存储过程的相关数据库对象。程序清单如下：

--查看相关的数据库对象

USEcollegeGOsp_depends学生成绩信息

GO

程序的执行结果如图9-20所示。图9-20程序执行结果显示窗口9.4.2修改存储过程

1.使用企业管理器修改存储过程使用企业管理器可以很方便地修改存储过程的定义。在企业管理器中，展开存储过程，右击要修改的存储过程，从弹出的快捷菜单中选择“属性”选项，将会出现存储过程的“属性”对话框。在该对话框中，可以直接修改定义该存储过程的Transact-SQL语句。单击“检查语法”按钮，可以进行语法检查；单击“权限”按钮，可以修改用户执行该存储过程的权限。2.使用Transact-SQL语句修改存储过程使用Transact-SQL语言中的ALTERPROCEDURE命令可以更改先前通过执行CREATEPROCEDURE语句创建的过程，但不会更改权限，也不影响相关的存储过程。其语法形式如下：

ALTERPROC[EDURE]procedure_name[；number][{@parameterdata-type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}][FORREPLICATION]ASsql_statement[,...n]

修改存储过程时，应该注意以下几点：

(1)如果在CREATEPROCEDURE语句中使用过参数，那么在ALTERPROCEDURE语句中也应该使用这些参数。

(2)每次只能修改一个存储过程。

(3)存储过程的创建者、db_owner和db_ddladmin的成员拥有执行ALTERPROCEDURE语句的许可，其他用户不能使用。

(4)用ALTERPROCEDURE更改的存储过程的权限和启动属性保持不变。

【例9-10】修改前面创建的“显示学生信息”存储过程，使之完成以下功能：根据传入的学号在学生表、课程表和成绩表中查询此学生的班级、姓名、性别、考试课程名称和考试分数。

程序清单如下：

--修改存储过程

USEcollegeGOALTERPROCEDURE显示学生信息

@学号varchar(10)ASSELECT班级编号,姓名,性别,课程表.课程名称,成绩表.总评成绩

FROM学生表,课程表,成绩表

WHERE学生表.学号=@学号

AND学生表.学号=成绩表.学号

AND课程表.课程号=成绩表.课程号

GO

执行修改后的显示学生信息的存储过程：

USEcollegeGO

显示学生信息'05209006'GO

程序的执行结果如下：班级编号姓名性别课程名称总评成绩

-----------------------------------------------------------052005王安女C语言 65052005王安女高等数学 69052005王安女邓小平理论 84(所影响的行数为3行)9.5重命名和删除存储过程9.5.1重命名存储过程

1.使用企业管理器修改存储过程名称方法是：在企业管理器中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”选项，修改该存储过程的名称，最后在弹出的确认重命名对话框中单击“是”按钮即可。

2.使用系统存储过程修改存储过程名称修改存储过程的名称也可以使用系统存储过程sp_rename，其语法形式如下：

sp_rename原存储过程名称，新存储过程名称

【例9-11】使用系统存储过程将“显示学生信息”存储过程的名称修改为“学生信息资料”。程序清单如下：

USEcollegeGOSp_rename显示学生信息,学生信息资料

GO

程序的执行结果如下：

object已重命名为'学生信息资料'。

注意:

更改对象名的任一部分都可能破坏脚本和存储过程。9.5.2删除存储过程

1.使用企业管理器删除存储过程在企业管理器中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”选项，弹出“除去对象”对话框，单击“全部除去”按钮，即可完成删除操作。在删除该对象之前，单击“显示相关性”按钮，可以查看与该存储过程有依赖关系的其他数据库对象名称。

2.使用Transact-SQL语句删除存储过程删除存储过程也可以使用Transact-SQL语言中的DROP命令，DROP命令可以将若干个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：

DROPprocedure{procedure}[，...n]【例9-12】使用DROP命令一次删除update_班级表_1和delete_班级表_1两个存储过程。程序清单如下：

USEcollegeGODROPprocedureupdate_班级表_1，delete_班级表_1GO

程序执行结果如下：命令已成功完成。如果程序返回了上面的执行结果，则表示已成功删除了update_班级表_1和delete_班级表_1这两个存储过程。9.6触发器9.6.1触发器的概念触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要通过事件进行触发而被执行。触发器是一个功能强大的工具，它随时监视数据表，当表中数据发生变化时自动执行。触发器可以用于SQLServer约束、默认值和规则的完整性检查，还可以完成用普通约束难以实现的复杂功能。

当在某一个数据表中插入、修改或者删除记录时，SQLServer就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。在触发器中可以查询其他数据表，甚至复杂的SQL语句。触发器和引起触发器执行的SQL语句被当作一次事务处理，如果这次事务未获得成功，则SQLServer会自动返回该事务执行前的状态。和CHECK约束相比较，触发器可以强制实现更加复杂的数据完整性，而且可以引用其他表中的字段。9.6.2触发器的优点触发器具有以下优点：

(1)触发器是自动执行的。在对表中的数据做了任何修改(比如手工输入或者应用程序采取的操作)之后立即被激活。

(2)触发器可以通过数据库中的相关表进行级联更改。例如，可以在学生表的学号列上写入一个删除触发器，以使成绩表中的各匹配行采取删除操作。该触发器用学号列作为唯一键，在成绩表中对各匹配行进行定位。

(3)触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引用其他表中的列。9.6.3触发器的类型触发器可以分为AFTER触发器和INSTEADOF触发器两种。

(1) AFTER触发器：这种类型的触发器将在数据变动(INSERT、UPDATE和DELETE操作)完成以后才被触发。可以对变动的数据进行检查，如果发现错误，则将拒绝接受或回滚变动的数据。AFTER触发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。

(2) INSTEADOF触发器：INSTEADOF触发器是SQLServer2000中新增的功能。这种类型的触发器将在数据变动以前被触发，即用执行触发器定义的操作取代变动数据的操作(INSERT、UPDATE和DELETE操作)。INSTEADOF触发器可以在表或视图上定义。在表或视图上，每个INSERT、UPDATE和DELETE语句最多可以定义一个INSTEADOF触发器。9.7创 建 触 发 器在SQLServer中，可以使用企业管理器或者Transact-SQL语句创建触发器。在创建触发器时应该注意以下几个问题：

(1) CREATETRIGGER语句必须是批处理中的第一个语句。将该批处理中随后的其他所有语句解释为CREATETRIGGER语句定义的一部分。

(2)创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。

(3)触发器为数据库对象，其名称必须遵循标识符的命名规则。

(4)只能在当前数据库中创建触发器，但触发器可以引用当前数据库以外的对象。(5) TRUNCATETABLE语句不会引发DELETE触发器。

(6) WRITETEXT语句不会引发INSERT或UPDATE触发器。9.7.1使用企业管理器创建触发器使用企业管理器创建触发器的步骤如下：

(1)在企业管理器中展开指定的服务器和数据库，这里展开LOCAL服务器下面的college数据库，右击学生表，从弹出的快捷菜单中依次选择“所有任务”→“管理触发器”选项，将出现“触发器属性”对话框，如图9-21所示。图9-21“触发器属性”对话框(2)在“触发器属性”对话框的“名称”文本框中选择“新建”，然后在文本框中输入创建触发器的文本。在创建一个触发器时必须指定以下几项内容：●触发器的名称；●需要监视数据操作的表；●触发器将由数据的增、删、改中的什么事件触发；●执行触发操作的程序语句。在文本框中输入以下文本：

CREATETRIGGERtr_学生_InsertON学生表

FORINSERTASDECLARE@标示char(30)SET@标示="你插入了一条新记录!"PRINT@标示上面这段程序的功能是创建一个由INSERT事件触发的触发器，当在学生表中插入一条新记录时，该触发器给出“你插入了一条新记录!”的提示信息。

(3)单击“检查语法”按钮，可以检查语法是否正确，显示结果如图9-22所示。

(4)单击“应用”按钮，在名称下拉列表中出现新创建的tr_学生_Insert触发器的名称，单击“确定”按钮，即可关闭该对话框，成功创建触发器。图9-22语法检查结果对话框

【例9-13】创建tr_学生_Insert触发器后，查看向学生表中插入数据时此触发器所完成的功能。在查询分析器中输入以下SQL语句：

USEcollegeGOinsertinto学生表

(学号,姓名,性别,出生年月,班级编号,电话,地址,身份证)values(‘05209030’,‘程晨’,‘女’,‘1986-11-11’,‘052005’,‘65214213’,‘松花江路33弄5号603室',)GO

程序的执行结果如下：你插入了一条新记录!(所影响的行数为1行)9.7.2使用Transact-SQL语句创建触发器使用Transact-SQL语言中的CREATETRIGGER命令也可以创建触发器，其中需要指定触发器所监视的表、触发器执行的事件和触发器的所有指令。创建触发器的过程类似于创建存储过程，其语法形式如下：

CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{(FOR|AFTER|INSTEADOF){[DELETE][，][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：用于指定触发器的名称。触发器的名称必须符合SQLServer标识符规则，并且其名称在当前数据库中必须是唯一的。另外，还可以选择是否指定触发器所有者的名称。● table|view：用于指定在其上执行触发器的表或视图，有时称为触发器表或触发器视图。另外，还可以选择是否指定表或视图的所有者名称。● WITHENCRYPTION：用于加密syscomments表中包含CREATETRIGGER语句文本的条目。使用WITHENCRYPTION可防止将触发器作为SQLServer复制的一部分发布。● AFTER：用于规定此触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定FOR关键字，则AFTER是默认设置。注意，该类型触发器仅能在表上创建，而不能在视图上定义该触发器。● INSTEADOF：用于规定执行的是触发器而不是执行触发SQL语句，从而用触发器替代触发语句的操作。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEADOF触发器。INSTEADOF触发器不能在WITHCHECKOPTION的可更新视图上定义。如果向指定的WITHCHECKOPTION选项的可更新视图上添加INSTEADOF触发器，则SQLServer将产生一个错误。用户必须用ALTERVIEW删除该选项后才能定义INSTEADOF触发器。● {[DELETE][,][INSERT][,][UPDATE]}：用于指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许以任意顺序组合这些关键字。如果指定的选项多于一个，则需用逗号分隔这些选项。● WITHAPPEND：用于指定应该添加现有类型的其他触发器。只有当兼容级别(指某一数据库行为与以前版本的SQLServer的兼容程度)是65或更低时，才需要使用该可选子句。如果兼容级别是70或更高，则不必使用该子句。● NOTFORREPLICATION：表示当复制进程更改触发器所涉及的表时，不应执行该触发器。● AS：触发器要执行的操作。● sql_statement：触发器的条件和操作。触发器条件指定其他准则，以确定DELETE、INSERT或UPDATE语句是否导致执行触发器操作。● IFUPDATE(column)：用于测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作，可以指定多列。因为在ON子句中指定了表名，所以在IFUPDATE子句的列名前不要包含表名。若要测试在多个列上进行的INSERT或UPDATE操作，则应在第一个操作后指定单独的UPDATE(column)子句。在INSERT操作中，IFUPDATE将返回TRUE值，因为这些列插入了显式值或隐性值(NULL)。● IF(COLUMNS_UPDATED()：用于测试是否插入或更新了所涉及的列，仅用于INSERT或者UPDATE触发器。● 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：检查列的整型位掩码，用来检查是否已更新或插入了这些列。注意：当创建触发器时，如果使用了相同名称的触发器，则后面建立的触发器将会覆盖前面建立的触发器。用户不能在系统表上创建用户自定义的触发器。在创建触发器时，可以使用两个特殊的临时表，它们分别是inserted表和deleted表，这两个表都存在于内存中。

inserted表中存储着被INSERT和UPDATE语句影响的新的数据行。执行INSERT或UPDATE语句时，新的数据行被添加到基本表中，同时这些数据行的备份被复制到inserted临时表中。deleted表中存储着被DELETE和UPDATE语句影响的旧的数据行。执行DELETE或UPDATE语句时，指定的数据行从基本表中删除，然后被转移到deleted表中。在基本表和deleted表中一般不存在相同的数据行。一个UPDATE操作实际上是由一个DELETE操作和一个INSERT操作组成的。在执行UPDATE操作时，旧的数据行从基本表中转移到deleted表中，然后将新的数据行同时插入基本表和inserted表中。下面举例说明如何使用Transact-SQL语句创建触发器，并在触发器中使用inserted和deleted临时表。【例9-14】创建一个AFTER触发器，要求实现以下功能：在成绩表上创建一个插入、更新类型的触发器“检查分数触发器”，当在考试成绩字段中插入或修改考试分数后，触发该触发器，检查分数是否在0～100之间。．程序清单如下：

USEcollegeGOCREATETRIGGER检查分数触发器

ON成绩表

FORINSERT,UPDATEASIFUPDATE(考试成绩)PRINT'AFTER触发器开始执行……'BEGINDECLARE@分数intSELECT@分数=(SELECT考试成绩

FROMinserted)IF(@分数NOTbetween0and100)PRINT'输入的分数超出范围，请重新输入的考试分数!'ENDGO

创建了“检查分数触发器”之后，在查询分析器中输入以下SQL语句：

USEcollegeGOPRINT'在成绩表中插入记录时触发器执行结果：'PRINT''INSERTINTO成绩表VALUES('05209030','059001',35,-25,5)INSERTINTO成绩表VALUES('05209030','059002',35,225,100)GOPRINT'在成绩表中修改记录时触发器执行结果：'PRINT''UPDATE成绩表SET考试成绩=115WHERE学号='05209030'and课程号='059001'UPDATE成绩表SET考试成绩=-65WHERE学号='05209030'and课程号='059002'

执行上面的SQL语句，结果如下：在成绩表中插入记录时触发器执行结果：

AFTER触发器开始执行……

输入的分数超出范围，请重新输入的考试分数!(所影响的行数为1行)

在成绩表中修改记录时触发器执行结果如下：

AFTER触发器开始执行……

输入的分数超出范围，请重新输入的考试分数!(所影响的行数为1行)【例9-15】创建一个AFTER触发器，要求实现以下功能：在学生表上创建一个删除类型的触发器tr_学生删除，当在学生表中删除某一条记录后，触发该触发器，在成绩表中删除与此学号对应的记录。

USEcollegeGOCREATETRIGGERtr_学生删除

ON学生表

FORDELETEASPRINT'删除触发器开始执行……'DECLARE@学号char(10)PRINT'把在学生表中删除的记录的学号赋值给局部变量@学号。'SELECT@学号=学号

FROMdeletedPRINT'开始查找并删除成绩表中的相关记录……'DELETEFROM成绩表

WHERE学号=@学号

PRINT'删除了成绩表中的学号为'+RTRIM(@学号)+'的记录。'GO

创建了tr_学生删除触发器之后，在查询分析器中输入以下SQL语句：

USEcollegeGODELETEFROM学生表WHERE学号='05209006'DELETEFROM成绩表WHERE学号='05209006'GO

程序的执行结果如下：删除触发器开始执行……

把在学生表中删除的记录的学号赋值给局部变量@学号。开始查找并删除成绩表中的相关记录……(所影响的行数为3行)(所影响的行数为1行)

删除了成绩表中的学号为05209006的记录。

(所影响的行数为0行)

【例9-16】创建一个INSTEADOF触发器，要求实现以下功能：在教师表上创建一个删除类型的触发器“不可删除”，当在教师表中删除