第09章-数据库高级应用——存储过程及触发器-第3稿.doc

第九章　数据库的高级使用2——存储过程和触发器
在SQL Server数据库管理系统中，存储过程具有很重要的作用，存储过程是T-SQL语句的集合，它提供了一种高效、便捷和安全的访问数据库的方法，经常被用来查询和更新数据。而触发器就其本质而言也是一种存储过程。它只是在满足一定条件时就可以触发完成预制好的各种动作，可以帮助我们更好地维护数据库中数据的完整性，实现对数据的管理。
在本章我们要介绍存储过程与触发器的概念、特点和作用，介绍创建和管理存储过程与触发器的方法与技巧。
9.1 任务的提出
一天，晓灵又来到了郝老师的办公室。
晓灵：“郝老师，《晓灵学生管理系统》的数据库基本设计完成了。在这个过程中得到了您的大力支持！太感谢您了！”
郝老师：“这没什么，应该做的。”
晓灵：“郝老师，还有个问题得请教一下。就是在使用《晓灵学生管理系统》的过程中，对于那些不太熟悉T-SQL语句的用户，我们应该如何帮助他们使用系统的查询功能。再有针对用户的误操作，我们能不能让SQL Server数据库系统实现自动纠错？”
郝老师：“我明白你的意思。也就是说，对于那些不太会使用T-SQL语句的用户，通过使用某种帮助也可以完成相应的任务。”
晓灵：“太对了，我就是想说这些。那我应该如何解决这个问题呢？”
郝老师：“解决这个问题的实质就是使用存储过程和触发器。当然使用存储过程和触发器并不是仅能完成上述功能。为了能够更好的说明存储过程和触发器的方法及原理，我们来举例说明。”
下面首先看一个存储过程应用的实例。在介绍存储过程之后，再看触发器的应用。
【任务9.1】在《晓灵学生管理系统》中，需要提供对学生成绩进行查询的功能。在查询时，需要提供欲查询的学生姓名和课程名称。我们知道如果掌握了T-SQL语句中的SELECT语句，要实现这一功能并不难。但问题是，并不是所有的人都会使用SELECT语句。要想让每一个系统用户都能实现查询，必须要提出一个新的解决方案。
分析：要想解决这个问题，我们可以把欲执行的T-SQL语句做成一个相对固定的语句组，用户想查询学生的成绩只要执行这个T-SQL语句组就可以了。且如果每一次查询都需要通过网络提交该T-SQL查询语句，那么势必造成一个相同的SQL语句在网络中频繁传输，对网络的压力是非常大的，而且这样直接传输数据也不安全，还会造成应用系统的性能下降。根据SQL Server 2000中所提供的存储过程的特点，建议采用存储过程方式予以解决。
解决办法：在数据库服务器端创建一个带输入输出参数的存储过程，由查询操作提交学生姓名和课程名称，在服务器端调用存储过程去实现查询并返回查询结果。这样就实现了降低了用户的操作难度，同时也减少了网络传输的数据量提高了系统性能。并且在多次查询时，直接调用该存储过程的编译结果去查询速度则更快。
下面我们就介绍存储过程。
9.2 存储过程
存储过程（Stored Procedure ）是一组为了完成特定功能、可以接受和返回用户提供的参数的T-SQL 语句的预编译集合，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程在第一次执行时进行语法检查和编译，执行后他的执行计划就驻留在高速缓存中，用于后续调用。存储过程可以接受和输出参数、返回执行存储过程的状态值，还可以嵌套调用。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。
在SQL Server 2000中存储过程分为四类：
l 系统提供的存储过程
系统存储过程主要存储在master数据库中并以“sp_”为前缀，其主要是从系统表中获取信息为系统管理员管理SQL server提供支持，也为系统管理员和合适的用户（即有权限的用户）提供更新系统表的途径。
l 用户自定义的存储过程
用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需的数据信息）而编写的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。
l 临时存储过程
专用和全局临时存储过程与临时表类似，在该过程名称添加“#”和“##”前缀的方法进行创建。#表示本地临时存储过程，##表示全局临时存储过程。SQL Server关闭后，这些存储过程将不再存在。
l 扩展存储过程
扩展存储过程是对动态链接库（DLL）函数的调用，其名称是以“xp_”为前缀。扩展存储过程直接在SQL Server的地址空间运行，并使用SQL Server开放式数据服务（ODS）API编程。扩展存储过程使得我们可以使用像C这样的编程语言创建自己的外部例程，它的使用只是扩展了SQL Server的功能。对用户来说，扩展存储过程与普通的存储过程一样，执行方法也相同。
存储过程具有如下优点：
（1）执行数度快，改善系统性能。存储过程在服务器端运行，可以利用服务器强大的计算能力和速度，执行速度快。而且存储过程是预编译的，第一次执行后的存储过程会驻留在高速缓存中，以后直接调用，执行速度很快，如果某个操作需要大量的T-SQL语句或重复执行，那么使用存储过程比直接使用T-SQL语句之行得更快。
（2）减少网络流量。用户可以通过发送一条执行存储过程的语句实现一个复杂的操作，而不需要在网络上百条T-SQL语句，这样可以减少在服务器和客户端之间传递语句的数量，减轻了服务器的负担。
（3）增强代码的重用性和共享性。存储过程在被创建后，可以在程序中被多次调用，而不必重新编写。所有的客户端都可以使用相同的存储过程来确保数据访问和修改的一致性。而且存储过程可以独立于应用程序而进行修改，大大提高了程序的可移植性。
（4）提供了安全机制。如果存储过程支持用户需要执行的所有业务功能，SQL Serve可以不授予用户直接访问表、视图的权限，而是授权用户执行该村出过程，这样，可以防止把数据库中表的细节暴露给用户，保证表中数据的安全性。
9.3 创建存储过程
在SQL Server 2000 中，创建存储过程有以下三种方法：
l 使用企业管理器创建存储过程
l 使用创建存储过程向导创建存储过程
l 使用T-SQL命令创建存储过程
在创建存储过程时，需要确定存储过程的三个组成部分：
l 所有的输入参数以及传给调用者的输出参数；
l 被执行的针对数据库的操作语句，包括调用其他存储过程的语句；
l 返回给调用者的状态值，以指明调用是成功还是失败。
下面就开始进行介绍创建存储过程的方法。
1．使用企业管理器创建存储过程
按照下述步骤用企业管理器创建一个能够解决【任务9.1】问题的存储过程：
（l）启动【企业管理器】，登录到要使用的服务器。

图9.1 企业管理器中显示的存储过程详细信息
（2）选择要创建存储过程的数据库，在右窗口中双击【存储过程】图标，文件夹此时在右窗格中显示该数据库的所有存储过程。如图9.1所示。
（3）右击【存储过程】文件夹，在弹出菜单中选择【新建存储过程（S）…】，此时打开创建存储过程对话框，输入存储过程正文。如图9.2所示。

19

图9.2 创建存储过程对话框
（4）可以单击【检查语法】按钮，检查欲创建的存储过程的语法是否正确。
（5）最后单击【确定】，即可创建成功。
2. 使用创建存储过程向导创建存储过程
使用向导创建存储过程具体操作步骤如下：
（1）展开一个服务器组，再展开要在其中创建视图的服务器。
（2）展开【数据库】文件夹。
（3）在【工具】菜单上单击【向导】命令。
（4）双击【创建存储过程向导】命令，在出现的创建存储过程向导欢迎界面中，点击【下一步】。在随后出现如图9.3所示的界面中选择相应的数据库myDB之后，点击【下一步】。

图9.3 选择保存存储过程的数据库

图9.4 为创建的存储过程选择操作

图9.5 创建存储过程成功界面

（6）根据存储过程的操作特性，在如图9.4所示的界面中为将要创建的存储过程选择一个或多个操作。在此可以创建用于插入、删除和更新行的存储过程。选择完毕后，点击【下一步】。在如图9.5所示的界面中，可以编辑存储过程的内容。确认无误后，点击【完成】即可创建三个存储过程。

3.使用T-SQL创建存储过程
可以使用T-SQL语句CREATE PROCEDURE创建存储过程。在创建存储过程之前，应该考虑到以下几个方面：
l 不能将CREATE PROCEDURE语句与其它SQL语句组合到单个批处理中。
l 数据库所有者具有默认的创建存储过程的权限，它可将该权限传递给其他的用户。
l 存储过程是一种数据库对象，其名称必须遵守标识符规则。
l 只能在当前数据库中创建属于当前数据库的存储过程。
CREATE PROCEDURE语句的语法格式如下：
CREATE PROC [EDURE] procedure_name [ ; number ]
[ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
各参数的解释说明如下：
Ø procedure_name：新建存储过程的名称。要创建局部临时过程，可以在procedure_name前面加一个编号符(#procedure_name)，要创建全局临时过程，可以在procedure_name前面加两个编号符(##procedure_name)。完整的名称（包括#或##）不能超过128个字符。
Ø ;number：是可选的整数，用来对同名的过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起除去。
Ø @parameter：存储过程的参数。在CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有1024个参数。若参数的形式以＠parameter=value出现，则参数的次序可以不同，否则用户给出的参数值必须与参数列表中参数的顺序保持一致。若某一参数以@parameter=value形式给出，那么其他参数也必须以该形式给出。
Ø data_type：参数的数据类型。
Ø VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。
Ø default：参数的默认值。
Ø OUTPUT：表明参数是返回参数。
Ø n：是表示此过程可以包含多条T-SQL 语句的占位符。
Ø {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}：RECOMPILE表明SQL Server不会缓存该存储过程的执行计划，该过程将在运行时重新编译。
Ø ENCRYPTION表明SQL Server 加密了syscomments 表，该表的text字段是包含有create Procedure 语句的存储过程文本，使用该关键字无法通过查看sysoomments表来查看存储过程内容。
Ø FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。.使用FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE选项一起使用。
Ø AS：指定过程要执行的操作。
Ø sql_statement：过程中要包含的任意数目和类型的 T-SQL语句。
【任务9.2】使用存储过程解决【任务9.1】中的问题。
解决【任务9.1】中的问题，可以在查询分析器中执行下列SQL语句：
USE myDB –-打开数据库
--检查欲创建的存储是否存在，如果存在则删除
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'proc_Q_stuGrade' AND type = 'P')
DROP PROCEDURE proc_Q_stuGrade
GO
--创建存储过程proc_Q_stuGrade
CREATE PROC proc_Q_stuGrade
@stuName char(20),
@kcname char(20)
AS
if (@stuName is null)
print '请输入学生姓名！'
else
if (@kcname is null)
print '请输入课程名称！'
else
select gradeNum
from grade
where sID=(select sID from student where sName=@stuName) and kcID=(select kcID from course where kcName=@kcname)
go
9.4 执行存储过程
当需要执行存储过程时，需要使用T-SQL语句中的EXECUTE语句。如果存储过程是批处理中的第一条语句，那么不使用EXECUTE关键字也可以执行该存储过程。
其语法格式如下：
[ [ EXEC [ UTE ] ]
{ [ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var }
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ] [ ,...n ]
[ WITH RECOMPILE ] }
各参数说明如下：
@return_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在用于 EXECUTE 语句前，必须在批处理、存储过程或函数中声明过。在用于唤醒调用标量值用户定义函数时，@return_status 变量可以是任何标量数据类型。
procedure_name：是拟执行（或调用）的存储过程的名称。
@procedure_name_var：是局部定义变量名，代表存储过程名称。
其它参数的说明，请参考CREATE PROCEDURE语句的语法说明。
【任务9.3】请通过调用存储过程proc_Q_stuGrade来查询“刘常福”同学的《商务网站建设》这门课的成绩。
use myDB
go
--执行存储过程，查询“刘常福”同学的《商务网站建设》这门课的成绩
exec proc_Q_stuGrade '刘常福','商务网站建设'
上述T-SQL语句执行结果如图9.6所示。

图9.6 执行存储过程的结果
9.5 管理存储过程
9.5.1 修改存储过程
如果需要修改存储过程中的语句或参数，可以采用删除该存储过程然后再重新创建的方法，也可以采用直接修改存储过程的定义文本和过程代码的方法。需要注意的是，如果是先删除再重新创建存储过程，那么所有与该存储过程相关联的权限设置都将丢失。而直接修改存储过程的定义文本和过程代码，为该存储过程定义的权限信息将会被保留。
1．使用企业管理器修改存储过程
在企业管理器中，首先找到要修改的存储过程。然后用鼠标右健单击所要修改的存储过程，在弹出菜单中选择【属性】命令，可以查看并修改该存储过程的定义文本和过程代码。
2. 使用存储过程修改存储过程
修改存储过程可以使用ALTER PROCEDURE语句实现。在进行修改前，首先要确保拥有相应的权限。ALTER PROCEDURE权限默认授予sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员以及存储过程的所有者，并且该权限不可转让。
ALTER PROCEDURE语句的语法格式如下：
ALTER PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS
sql_statement [ ...n ]
各参数的说明，请参考CREATE PROCEDURE语句的语法说明。
【任务9.4】由于【任务9.3】中的存储过程文本可以使用存储过程sp_helptext进行查看，出于对代码安全性的考虑，请将【任务9.3】中创建的存储过程进行加密处理。
分析：只要将该存储过程加密即或解决这个问题。
在查询分析器中执行下列T-SQL语句：
USE myDB --打开数据库
GO
--修改存储过程proc_Q_stuGrade
ALTER PROC proc_Q_stuGrade
@stuName char(20),
@kcname char(20)
with encryption
AS
if (@stuName is null)
print '请输入学生姓名！'
else
if (@kcname is null)
print '请输入课程名称！'
else
select gradeNum
from grade
where sID=(select sID from student where sName=@stuName) and kcID=(select kcID from course where kcName=@kcname)
go
9.5.2 重命名存储过程
存储过程也可以被重新命名。新的名称必须遵守标识符规则。要重命名的存储过程必须位于当前数据库中，并且要拥有相应的权限。
1．使用企业管理器重命名存储过程
在企业管理器中，首先找到要修改的存储过程。然后用鼠标右健单击所要重命名的存储过程，在弹出菜单中选择“重命名”命令，就可以重新命名该存储过程。
需要注意的是，重命名存储过程不会更改该存储过程在定义文本中指定的名称。
2. 使用存储过程重命名存储过程
使用系统存储过程sp_rename可以重命名存储过程。其语法格式如下：
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
各参数说明如下：
[@objname =] 'object_name'：是存储过程或触发器的当前名称。
[@newname =] 'new_name'：是指定存储过程或触发器的新名称。
[@objtype =] 'object_type'：是要重命名的对象的类型。对象类型为存储过程或触发器时，其值为OBJECT。
需要注意的是：只能修改当前数据库中的存储过程或触发器。
【任务9.5】请使用存储过程将【任务9.3】中创建的存储过程proc_Q_stuGrade重新命名为proc_Q_stuGrade_new。
请在查询分析器中执行下列T-SQL语句：
use myDB
go
exec sp_rename 'proc_Q_stuGrade','proc_Q_stuGrade_new','OBJECT'
9.5.3 删除存储过程
1．使用企业管理器删除存储过程
删除存储过程的操作步骤如下：
（1）用鼠标右键单击待删除存储过程，在弹出菜单中选择【删除】命令，或单击要删除的存储过程，接着按下delete键。
（2）当出现“除去对象”对话框时，可以单击【显示相关性】按钮查看删除该存储过程对数据库有什么影响。
（3）单击【全部删去】按钮，即完成删除存储过程。
2. 使用T-SQL语句删除存储过程
使用T-SQL语句中的drop命令可以将一个或多个存储过程或存储过程组从当前数据库中删除，其语法格式如下：
DROP PROCEDURE { procedure } [ ,...n ]
参数说明如下：
procedure：是要删除的存储过程或存储过程组的名称。
【任务9.6】请使用存储过程将存储过程【任务9.5】中重命名的存储过程从数据库中删除。解决【任务9.6】中的任务，可以在查询分析器中执行下列T-SQL语句：
use myDB
go
DROP PROCEDURE proc_Q_stuGrade_new
9.6 查看存储过程
查看存储过程通常包括以下几个方面：
l 查看用于创建存储过程的T-SQL语句。
l 获得有关存储过程的信息（如存储过程的所有者、创建时间及其参数）。
l 列出指定存储过程所使用的对象及使用指定存储过程的过程。
1．使用企业管理器查看存储过程
在企业管理器中，首先找到要修改的存储过程。然后用鼠标右键单击所要查看的存储过程，在弹出菜单中选择“属性”命令，可以查看该存储过程的定义文本。
要想查看存储过程的相关性可用鼠标右键单击所要查看相关性的存储过程，在弹出菜单中选择“所有任务”命令，然后选择“显示相关性”，则可以在相关性对话框中查看到依附于该存储过程的对象和该存储过程依附的对象。
2. 使用存储过程查看存储过程
（1）查看存储过程的定义
使用系统存储过程sp_helptext，可以查看未加密的存储过程的文本。其语法格式如下：
sp_helptext [ @objname = ] 'name'
参数说明如下：[@objname =] 'name'：存储过程的名称，将显示该存储过程的定义文本。该存储过程必须在当前数据库中。
【任务9.7】请使用存储过程显示存储过程proc_Q_stuGrade的定义文本。
解决【任务9.7】中的任务，可以在查询分析器中执行下列T-SQL语句：
USE myDB
go
EXEC sp_helptext 'proc_Q_stuGrade'
（2）查看有关存储过程的信息
使用系统存储过程sp_help可以查看当前数据库中存储过程的信息（如存储过程的所有者、创建时间及其参数），其语法格式如下：
sp_help [ [ @objname = ] name ]
参数说明如下：
[@objname =] name：是sysobjects中的存储过程的名称。
当没有指定name时，sp_help列出当前数据库中所有对象的名称、所有者和对象类型。
【任务9.8】请使用存储过程显示存储过程'proc_Q_stuGrade'的信息。
解决【任务9.8】中的任务，可以在查询分析器中执行下列T-SQL语句：
USE myDB
go
EXEC sp_help 'proc_Q_stuGrade'
（3）查看存储过程的相关性
使用系统存储过程sp_depends，可以显示有关数据库对象相关性的信息（例如，依赖表或视图的视图和过程，以及视图或过程所依赖的表和视图），但不包括对当前数据库以外对象的引用。其语法如下：
sp_depends [ @objname = ] 'object'
参数说明如下：
[@objname =] 'object'：被检查相关性的数据库对象。对象可以是表、视图、存储过程或触发器。
【任务9.9】请使用存储过程显示存储过程proc_Q_stuGrade的相关性的信息。
解决【任务9.9】中的任务，可以在查询分析器中执行下列T-SQL语句：
USE myDB
go
EXEC sp_depends 'proc_Q_stuGrade'
9.7触发器
【任务9.10】在《晓灵学生管理系统》中，当某位学生受到了处分时，需要在奖惩记录表（jiangchen）中插入一条记录。为了更好地教育管理学生，在插入记录时如果该学生已存有处分记录时需要提示“该学生已有几条处分记录了，请加强对该学生地教育与管理。”
分析：要想实现上述功能，使用存储过程已不可能了。因为存储过程的执行是被动的，只有当需要时去调用存储过程，存储过程才能被执行。这样显然不能满足上述要求。根据SQL Server 2000中触发器的特点，考虑使用触发器解决这一问题。即在jiangchen表创建一个插入型触发器，在插入学生奖惩记录时系统可以自动统计该学生有几次处分记录并进行系统提示。
下面就开始介绍触发器的概念、作用、工作原理和管理方法。
9.7.1 触发器的概念及作用
触发器（Trigger）是一个能由系统自动执行对数据库修改的语句。通常由三部分组成：
（1）事件。事件是指对数据库的插入、删除和修改等操作，触发器在这些事件发生时，将开始工作。
（2）条件。触发器将测试条件是否成立。如果条件成立，就执行相应的动作，否则什么也不做。
（3）动作。如果触发器测试满足预设的条件，那么就由DBMS执行这些动作（即对数据库的操作）。这些动作能使触发器事件不发生，即撤消事件，如删除刚插入的元组等。这些动作也可以是一系列对数据库的操作，甚至可以是与触发事件本身无关的其它操作。
触发器就本质来说，是一种特殊类型的存储过程。它在指定表中的数据发生变化时自动生效。在表中的数据进行更新时，可以随时调用触发器以响应INSERT、UPDATE或DELETE语句。触发器还可以查询其它表，并可以包含复杂的T-SQL语句，它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程则是在需要的时候通过存储过程名直接调用的。
触发器的主要作用就是其能够实现由主键约束、外键约束所不能实现的复杂约束。除此之外，触发器还有以下功能：
l 强化约束：触发器能够实现比CHECK语句更为复杂的约束。
l 跟踪变化：触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化。
l 级联运行：触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器中包含有对另外一个表的数据操作（如删除、更新和插入），而该操作又导致该表上的触发器被触发。
l 存储过程的调用：为了响应数据库更新，触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS本身之外进行操作。
由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的处理。此外一个表的同一类型（INSERT、UPDATE、DELETE）的多个触发器能够对同一种数据操作采取多种不同的处理。
总体而言，触发器性能通常比较低。当运行触发器时，系统处理的大部分时间花费在参照其他表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中，可见触发器所参照的其他表的位置决定了操作要花费的时间长短。
9.7.2 触发器的种类
SQL Server 2000支持两种类型的触发器：AFTER触发器和INSTEAD OF触发器。
AFTER触发器即为SQL Server 2000版本以前所介绍的触发器。该类型触发器要求只有执行某一操作（INSERT、UPDATE、DELETE）之后，触发器才能被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。对于AFTER触发器，可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程sp_settriggerorder来完成此任务。
INSTEAD OF触发器表示拒绝执行其所定义的操作（INSERT、UPDATE和DELETE）而仅是执行触发器本身。既可在表上定义INSTEAD OF触发器，也可以在视图上定义INSTEAD OF触发器，但对同一操作只能定义一个INSTEAD OF触发器。
9.7.3 触发器的工作原理
在触发器执行时，将生成两个临时表（逻辑表），即inserted表和deleted表。这两个临时表由系统进行管理，存储在内存中，并且不允许用户直接对其修改。这两个表的结构总是与被触发器作用的表的结构相同，它们可以被用于触发器条件的测试。在执行INSERT语句时，插入到表中的新记录也同时插入到表inserted表中。在执行UPDATE语句时，系统首先删除原有记录，并将原有记录插入到deleted表中，而新插入的记录也同时插入到inserted表中。在执行DELETE语句时，删除表中数据的同时，也将该数据插入到deleted表中。也就是说，触发器会自动记录所要更新数据的新值和原值，根据对新值和原值的测试来决定是否执行触发器中预设的动作。当触发器工作完成时，临时性的inserted表和deleted数据表会自动消失。
9.8 创建触发器
在SQL Server 2000中可以使用以下两种方法创建触发器：
l 使用企业管理器创建
l 使用T-SQL语句创建触发器
下面就分别进行介绍。
1．使用企业管理器创建触发器
【任务9.11】请使用企业管理器创建一个简单的触发器，实现在学生信息表（student）中当学生姓名被修改时，需要返回一个提示信息。
具体操作步骤如下：
（1）启动SQL SERVER企业管理器。在控制台树中，依次打开MICROSOFT SQL SERVER、SQL SERVER组和用户服务器。
（2）展开【数据库】文件夹，展开要创建触发器的数据库（myDB），然后展开该数据库的【表】节点，用鼠标右键单击触发器所属的表（student），选择【所有任务】菜单下的【管理触发器】命令，打开“触发器属性”对话框，如图9.7所示。

图9.7 “触发器属性”对话框
（3）在“触发器属性”对话框中，输入创建触发器的T-SQL语句。
（4）单击【检查语法】按扭，检查输入的T-SQL语句是否有语法错误。如果没有语法错误，将会弹出“语法检查成功”对话框，如果有语法错误，则进行修改，直到没有语法错误为止。也可以单击【另存为模板】按扭，将所创建的触发器脚本保存为模板。
（5）单击【确定】按扭，保存触发器的定义，关闭属性对话框，即可创建成功。
当创建成功后，可以在查询分析器中执行下列T-SQL语句进行测试：
use myDB
go
update student
set sName='王亚力_新'
where sID='60402'
2．用T-SQL语句创建触发器
可以使用T-SQL语句中的CREATE TRIGGER命令来创建触发器，其语法格式如下：
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{ { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ 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 ] } }
各参数的说明如下：
trigger_name：是触发器的名称。
Table | view：是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。
WITH ENCRYPTION：加密syscomments表中包含CREATE TRIGGER语句文本的条目。
AFTER：表示只有在执行了指定的操作（INSERT 、DELETE 或UPDATE ）之后触发器才被激活，执行触发器中的SQL语句。若使用关键字FOR，则表示为AFTER触发器，且该类型触发器仅能在表上创建。
INSTEAD OF：当为表或视图定义了针对某一操作（INSERT 、DELETE 或UPDATE ）的困STEAD OF 类型触发器且执行了相应的操作时，尽管触发器被触发，但相应的操作并不被执行，运行的仅是触发器SQL 语句本身。
指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEAD OF触发器。然而，可以在每个具有INSTEAD OF触发器的视图上定义视图。
INSTEAD OF触发器不能在WITH CHECK OPTION的可更新视图上定义。
{ [DELETE] [,] [INSERT] [,] [UPDATE] }：是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字，必须至少指定一个选项。
WITH APPEND：指定应该添加现有类型的其它触发器。WITH APPEND不能与INSTEAD OF触发器一起使用。
NOT FOR REPLICATION：表示当复制进程更改触发器所涉及的表时，不执行该触发器。
AS：是触发器要执行的操作。
sql_statement：是触发器的条件和操作。
n：是表示触发器中可以包含多条T-SQL语句的占位符。
IF UPDATE (column)：测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作。可以指定多列。因为在ON子句中指定了表名，所以在IF UPDATE子句中的列名前不要包含表名。
column：是要测试INSERT或UPDATE操作的列名。该列可以是SQL Server 支持的任何数据类型。但是，计算列不能用于该环境中。
IF (COLUMNS_UPDATED())：测试是否插入或更新了提及的列，仅用于INSERT或UPDATE触发器中。
COLUMNS_UPDATED函数以从左到右的顺序返回位，最左边的为最不重要的位。
bitwise_operator：是用于比较运算的位运算符。
updated_bitmask：是整型位掩码，表示实际更新或插入的列。
comparison_operator：是比较运算符。使用等号(=)检查updated_bitmask中指定的所有列是否都实际进行了更新。使用大于号(>)检查updated_bitmask中指定的任一列或某些列是否已更新。
column_bitmask：是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。
【任务9.12】请使用触发器解决【任务9.10】的问题，实现系统自动统计相应学生的处分记录并进行系统提示。
解决【任务9.12】中的任务，可以在查询分析器中执行下列T-SQL语句：
CREATE TRIGGER [TRI_jiangchen_ins] ON [dbo].[jiangchen]
FOR INSERT
AS
declare @countNum int,@jcRen char(6),@sName varchar(20)
--从inserted表中取出所要添加的奖惩记录中学生的学号
select @jcRen=jcRen from inserted
--从student表中取出该学生的