1、 通过本章学习，你能够学会： 存储过程与触发器是SQL Server中的两类数据库对象。它们都是由T-SQL语句编写而成的过程，所不同的是存储过程是由用户根据需要调用执行的，而触发器则是由某个动作(如删除或修改一条记录)引发执行的。另外，存储过程可以不依附于表而单独存在，而触发器则必须依附于一个特定的表。它们与函数也不同，函数可以直接通过函数名返回数值，其返回值可以直接在表达式中使用，而存储过程与触发器则不能直接通过其名称带回返回值，也不能直接在表达式中使用。 实例实例9.1 9.1 了解存储过程的执行过程，如图9.1所示。 归纳分析：归纳分析： 在SQL Server中定义的过程被称为存储过
2、程。存储过程是一组预先编译好的T-SQL代码，作为一个整体用于执行特定的操作。存储过程属于数据库对象，它们存放在数据库中，需要时用户可以调用。 调用存储过程（输入参数）存储过程 输出参数 执行动作 返回状态值 ： 1. 存储过程将一系列复杂的T-SQL代码封装在一起作为数据库对象存放在数据库 中，用户使用时不必接触T-SQL而仅需直接调用即可得到所需结果，简化了用户的操作。 2. 存储过程已经被编译，执行时省去了编译与优化的时间。另外，第一次从磁盘调用存储过程后，它将驻留在内存中，下一次使用时可以直接从内存中调用，因此对于需要多次调用的存储过程而言，速度明显加快。 3.在分布式查询中，调用存储
3、过程的语句将比直接使用T-SQL的语句少得多，这将大大减少网络流量。 4.通过适当的权限设置，可以使系统的安全性得到更有效的保障。 实例实例9.2 9.2 查看系统存储过程。 操作步骤：操作步骤： 归纳分析：归纳分析： 系统存储过程是SQL Server内置的存储过程，可以直接用于执行一些操作，用于进行系统管理、登录管理、权限设置、数据库对象管理、查看数据库信息、数据库复制等操作。系统存储过程存放在master数据库中，以sp_作为前缀。在任何数据库中无需用master限定就直接执行。 临时存储过程与临时表类似。是对用户定义的存储过程。以#、或#开头的存储过程。无论用户在哪个数据库中创建，它们
4、都存放在临时数据库tempdb中。以#开头的存储过程是局部临时存储过程，它仅能由其创建者本人在创建完后立即调用，一旦该创建者断开与数据库的连接，局部临时存储过程立即被删除。以#开头的存储过程是全局临时存储过程，它可以由创建者本人和其他用户在创建完后共同调用，一旦创建者断开与数据库的连接，则不再允许新的用户使用，而且等其他正在使用该存储过程的用户使用完毕后，全局临时存储过程也被删除。 是利用高级语言(如C语言)编写的存储过程，是SQL Server可以动态装载并执行的动态链接库(DLL)。扩展存储过程只能添加到master数据库中。本章不介绍扩展存储过程有关内容，读者可以参考其他书籍有关内容。
5、实例实例9.3 9.3 在企业管理器中建立名为pro_new的存储过程，并执行。 操作步骤：操作步骤： 归纳分析：归纳分析： 如果是查看或修改已有存储过程的定义，则右击要查看或修改的存储过程，在弹出的快捷菜单中，选择“属性”命令，则调出“存储过程属性”窗口，在这里可以查看或修改存储过程的定义。 实例实例9.4 9.4 在“学生”数据库中建立一个存储过程pro_new1，用于查询所有学生的学号和数学期中成绩、计算机期中成绩，要求数学期中成绩和计算机期中成绩分别列出。已知数学期中成绩的课程号是1002、计算机期中成绩的课程号是2005。 操作步骤：操作步骤： 归纳分析：归纳分析： 如果是查看或修改
6、已有存储过程的定义，则右击要查看或修改的存储过程，在弹出的快捷菜单中，选择“属性”命令，则调出“存储过程属性”窗口，在这里可以查看或修改存储过程的定义。 实例实例9.4 9.4 在“学生”数据库中建立一个存储过程pro_new1，用于查询所有学生的学号和数学期中成绩、计算机期中成绩，要求数学期中成绩和计算机期中成绩分别列出。已知数学期中成绩的课程号是1002、计算机期中成绩的课程号是2005。 操作步骤：操作步骤： 归纳分析：归纳分析： 在SQL Server中，使用CREATE PROCEDURE语句创建一个永久或临时存储过程。 命令格式：命令格式： WITH ENCRYPTION WITH
7、 RECOMPILE ： （1）WITH ENCRYPTION：对存储过程进行加密。 （2）WITH RECOMPILE：对存储过程重新编译。 实例实例9.5 9.5 分别执行实例9.3、实例9.4建立存储过程。 操作步骤：操作步骤： 归纳分析：归纳分析：执行存储过程时，区分大小写。 存储过程一旦编写好后，就可以调用执行。执行存储过程的语法如下。命令格式：命令格式： 字符串变量|NSQL语句字符串+n 实例实例9.6 9.6 将实例9.3所创建的名为pro_new存储过程，修改成名为pro_存储过程。 操作步骤：操作步骤： （1） 启动“查询分析器”，输入如下SQL语句： （2）按“F5”键或
8、单击工具栏“执行查询”图标。 归纳分析：归纳分析： 只有数据库所有者和存储过程的所有者才有权修改存储过程名称。不宜轻易修改存储过程名称，因为系统中许多应用程序可能都要调用存储过程，如果修改了存储过程的名称，则这些应用程序都要随之改动。命令格式：命令格式： 修改存储过程名称的方法与修改表名称的方法类同，读者可以参照执行。 使用企业管理器修改存储过程名称的方法也类似于修改表名称的方法，即展开指定的数据库节点，单击存储过程，在右边的窗口中选中一个需要修改名称的存储过程右击之，在弹出的快捷菜单中选择“重命名”。 实例实例9.79.7 使用企业管理器修改存储过程实例9.4，增加显示课程号的列。 操作步骤
9、：操作步骤： 实例实例9.8 9.8 在实例9.4创建的存储过程，修改查询记录按名次排列。 操作步骤：操作步骤： 归纳分析：归纳分析： 修改已经创建的存储过程，可以不更改其用户的使用权限，也不更改其名称，因此不会破坏其他相关程序的引用关系。 命令格式：命令格式： WITH ENCRYPTION WITH RECOMPILE ： 其中各项语法含义与创建存储过程类似。 实例实例9.9 9.9 使用企业管理器删除存储过程。 操作步骤：操作步骤： 存储过程与触发器是SQL Server中的两类数据库对象。它们都是由T-SQL语句编写而成的过程，所不同的是存储过程是由用户根据需要调用执行的，而触发器则是
10、由某个动作(如删除或修改一条记录)引发执行的。另外，存储过程可以不依附于表而单独存在，而触发器则必须依附于一个特定的表。它们与函数也不同，函数可以直接通过函数名返回数值，其返回值可以直接在表达式中使用，而存储过程与触发器则不能直接通过其名称带回返回值，也不能直接在表达式中使用。 归纳分析：归纳分析： 系统存储过程是SQL Server内置的存储过程，可以直接用于执行一些操作，用于进行系统管理、登录管理、权限设置、数据库对象管理、查看数据库信息、数据库复制等操作。系统存储过程存放在master数据库中，以sp_作为前缀。在任何数据库中无需用master限定就直接执行。 归纳分析：归纳分析： 如果
11、是查看或修改已有存储过程的定义，则右击要查看或修改的存储过程，在弹出的快捷菜单中，选择“属性”命令，则调出“存储过程属性”窗口，在这里可以查看或修改存储过程的定义。 归纳分析：归纳分析： 在SQL Server中，使用CREATE PROCEDURE语句创建一个永久或临时存储过程。 命令格式：命令格式： WITH ENCRYPTION WITH RECOMPILE ： （1）WITH ENCRYPTION：对存储过程进行加密。 （2）WITH RECOMPILE：对存储过程重新编译。 归纳分析：归纳分析： 只有数据库所有者和存储过程的所有者才有权修改存储过程名称。不宜轻易修改存储过程名称，因为
12、系统中许多应用程序可能都要调用存储过程，如果修改了存储过程的名称，则这些应用程序都要随之改动。命令格式：命令格式： 修改存储过程名称的方法与修改表名称的方法类同，读者可以参照执行。 使用企业管理器修改存储过程名称的方法也类似于修改表名称的方法，即展开指定的数据库节点，单击存储过程，在右边的窗口中选中一个需要修改名称的存储过程右击之，在弹出的快捷菜单中选择“重命名”。 实例实例9.9 9.9 使用企业管理器删除存储过程。 操作步骤：操作步骤： 实例实例9.10 9.10 删除Pro_new存储过程。 操作步骤：操作步骤：（1） 启动“查询分析器”，输入如下SQL语句： DROP PROCEDUR
13、E Pro_newDROP PROCEDURE Pro_new（2）按“F5”键或单击工具栏“执行查询”图标。归纳分析：归纳分析： 不再需要存储过程时，可以利用企业管理器或T-SQL语言删除。 命令格式：命令格式： ：删除存储过程组时不必写出下标，即可将组中所有成员全部删除。 实例实例9.11 9.11 使用企业管理器查看存储过程的信息。 操作步骤：操作步骤： 在图9.4所示属性对话框中可以查询存储过程的名称、所有者、建立时间和文本信息。 在图9.3所示窗口的右部选中一个存储过程并右击，在弹出的级联菜单中依次选择“所有任务”、“显示相关性”，系统会弹出“显示相关性”对话框，该对话框显示一个存储
14、过程所引用的表及其列信息。 实例实例9.12 9.12 利用系统存储过程查询 pro_存储过程的信息。 操作步骤：操作步骤： 归纳分析：归纳分析： (1)查看定义存储过程的文本命令格式：命令格式： (2)查看存储过程的名称、所有者、建立时间命令格式：命令格式： (3)查看存储过程所引用的表(或查看一个表被哪些存储过程所引用) 命令格式：命令格式： (4)查看一个存储过程引用了哪些表及其列： 归纳分析：归纳分析： (1)查看定义存储过程的文本命令格式：命令格式： (2)查看存储过程的名称、所有者、建立时间命令格式：命令格式： (3)查看存储过程所引用的表(或查看一个表被哪些存储过程所引用) 命令
15、格式：命令格式： (4)查看一个存储过程引用了哪些表及其列： 命令格式：命令格式： WITHEN ENCRYPTION WITHRE RECOMPILE ： “参数名”和定义局部变量一样，必须以符号为前缀，要指定数据类型，多个参数定义要用逗号“，”隔开。在执行存储过程时该参数将由指定的参数值来代替，如果执行时未提供该参数的参数值，则如果这里定义了默认值则使用该值作为执行时的参数值，默认值可以是常量或空(NULL)。 SQL Server 2000提供了两种主要机制用于维护数据的完整性：一种是第8章介绍的约束，另一种就是本章将要介绍的触发器。触发器虽然是一种特殊的存储过程，但是它与表却是紧密联系
16、的，离开了表它将不复存在(这点与约束十分类似)。触发器又不像存储过程那样需要调用才能执行，而是在对表中数据进行增、删、改(INSERT、UPDATE、 DELETE)操作时被触发而执行。触发器可以包含复杂的T-SQL语句。在触发器内设置回滚，可以将触发它的语句所在的事务全部回滚，这样检测到错误时可以回滚事务以撤销错误。 实例实例9.16 9.16 建立一个触发器，防止用户在要删除表学生信息中的一条数据时，由于没有使用限制条件(误用SELECT FROM 学生信息语句)而导致删除全部数据的情况发生。 操作步骤：操作步骤： (4) INSERT，UPDATE ， DELETE：指定在表或视图上执行
17、哪些数据修改语句时将激活触发器。必须至少指定一个选项，如果指定的选项多于一个，需用逗号分隔。(5) IF UPDATE(列名)(AND|OR) UPDATE(列名)n：判定某一列或几列，是否被修改。如果希望仅当某一列(或几列)的数据发生变化才导致触发器动作，而其他列的数据变化不会引发触发器执行，则可以采用本选项。此时可以称之为列级触发器。归纳分析：归纳分析： SQL Server 2000中可以建立两类触发器，一类是在触发后将执行一组新的T-SQL语句用以代替引起触发器执行的语句(原有语句没有执行，仅执行新语句)，称之为INSTEAD OF触发器：另一类是在引起触发器执行的语句执行完毕后，再执
18、行触发器中的语句，称之为AFTER类触发器。(1)INSTEAD OF触发器既可以在表上定义，也可以在视图上定义。该类触发器依据其触发动作(UPDATE、DELETE和INSERT)的不同分为INSTEAD OF INSERT、INSTEAD OF UPDATE和INSTEAD OF DELETE三种，被引发执行时将分别代替表或视图的INSERT、UPDATE、DELETE操作，亦即表中的增删改操作没有进行。每个触发动作仅可以定义一个INSTEAD OF触发器。 实例实例9.21 9.21 在企业管理器中修改实例9.17将删除表改为添加记录。 操作步骤：操作步骤：（3）单击检查语法按钮，进行语
19、法检查，成功后，可插入数据检验触发器的效果。 4. 应熟练掌握使用企业管理器创建、修改、删除和查看存储过程的操作。5. 应熟练掌握使用T-SQL语句创建、修改、删除和查看存储过程，及如何执行存储过程的操作。6. 应掌握使用T-SQL语句带参数和变量创建和修改存储过程，及如何执行存储过程。7. 了解触发器在对表中数据进行增、删、改(INSERT、UPDATE、 DELETE)操作时被触发而执行。触发器可以包含复杂的T-SQL语句。在触发器内设置回滚，可以将触发它的语句所在的事务全部回滚，这样检测到错误时可以回滚事务以撤销错误。8. 触发器的优点： (1)触发器可以实现外键约束的功能，实现对表的级
20、连修改。 (1) 熟练掌握用T-SQL语句存储过程与触发器所需的记录。 (2) 熟练掌握用T-SQL语句编写的操作过程。 (3) 熟练掌握存储过程与触发器的表示方法。 (4) 熟练掌握使用企业管理器创建、修改、删除和查看存储过程与与触发器的操作。 (5) 熟练掌握使用T-SQL语句创建、修改、删除和查看存储过程，及如何执行存储过程与与触发器的操作。 SQL Server 2000的运行、管理环境。 实例实例9.13 9.13 创建的存储过程，修改成当输入学生学号时可以查询某个学生的成绩：不输入学号时，则查询全部学生的成绩。 操作步骤：操作步骤： （3）本例定义了一个输入参数stuID用于传送学
21、号，其默认值为空。执行存储过程时，如果指定了该输入参数的值，则按照该参数所给的学号查询指定学生的成绩，如果参数stuID的值为空，则查询所有学生成绩。输入如图9.8所示上部分程序，查看运行结果，如图9.8所示下部分。 归纳分析：归纳分析： 向存储过程指定输入、输出参数的主要目的是通过参数向存储过程输入和输出信息来扩展存储过程的功能。通过使用参数，可以多次使用同一存储过程并按用户要求查找所需要的结果。如实训9.13所示，没有参数就缺少灵活性。 一个存储过程可以带一个或多个输入参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。 命令
22、格式：命令格式： WITHEN ENCRYPTION WITHRE RECOMPILE ： “参数名”和定义局部变量一样，必须以符号为前缀，要指定数据类型，多个参数定义要用逗号“，”隔开。在执行存储过程时该参数将由指定的参数值来代替，如果执行时未提供该参数的参数值，则如果这里定义了默认值则使用该值作为执行时的参数值，默认值可以是常量或空(NULL)。 在执行存储过程的语句中，有两种方式来传递参数值，分别是使用参数名传递参数值和按参数位置传递参数值。 使用参数名传递参数值，是通过语句“参数名：参数值”给参数传递值。当存储过程含有多个输入参数时，参数值可以按任意顺序制定，对于允许空值和具有默认值的
23、输入参数可以不给出参数的传递值。 执行使用参数名传递参数值的存储过程的命令格式：命令格式： 按参数位置传递参数值，不显式地给出“参数名”，而是按照参数定义的顺序给出参数值。按位置传递参数时，也可以忽略允许空值和具有默认值的参数，但不能因此破坏输入参数的指定顺序。必要时，使用关键字“DEFAULT”作为默认值的占位。 实例实例9.14 9.14 在学生成绩表基础上建立一个存储过程pro_new3，要求：未输入学生学号时打印一个字符串“请输入学号”，并返回数字1；输入的学号查询不到时打印一个字符串“没有您输入的学号，请重新输入”，并返回数字2：输入学生学号正确时，查询该学号对应的期中成绩(课程号为
24、1002)考试成绩。 操作步骤：操作步骤： 归纳分析：归纳分析： 用户可以通过RETUEN语句返回状态值，RETURN语句只能返回整数，在存储过程中RETURN不能返回空值，默认返回值是0。也可以利用它返回整数输出参数值。 SQL Server 2000提供了两种主要机制用于维护数据的完整性：一种是第8章介绍的约束，另一种就是本章将要介绍的触发器。触发器虽然是一种特殊的存储过程，但是它与表却是紧密联系的，离开了表它将不复存在(这点与约束十分类似)。触发器又不像存储过程那样需要调用才能执行，而是在对表中数据进行增、删、改(INSERT、UPDATE、 DELETE)操作时被触发而执行。触发器可以
25、包含复杂的T-SQL语句。在触发器内设置回滚，可以将触发它的语句所在的事务全部回滚，这样检测到错误时可以回滚事务以撤销错误。 实例实例9.15 9.15 在表学生成绩中建立一个触发器，当向表中插入一条记录时，检查该学生的学号是否存在于表学生信息中，如果存在，则插入；否则，打印“学号不存在”，并回滚事务，使插入数据无效。 操作步骤：操作步骤： 归纳分析：归纳分析：创建触发器之前要注意如下几点： (1)创建触发器所使用的语句CREATET RIGGER必须是批处理中的第一个语句。(2)只有表的所有者、sysadmin固定服务器角色成员以及db_owner和曲_ddladmin固定数据库角色成员有权
26、在本表上创建触发器，且不能将该权限授予其他用户。(3)触发器为数据库对象，其命名规则必须与标识符命名规则一致。(4)只能在当前数据库中创建触发器，但是触发器可以引用其他数据库的对象。(5)不能在临时表或系统表上创建触发器。触发器可以引用临时表，但不能引用系统表。(6)尽管TRUNCATE TABLE语句用于删除表中所有记录，但由于它不写入日志，故不能引发DELETE触发器。 (7) WRITETEXT语句不会引发INSERT或UPDATE触发器。 (8) 如果一个触发器中含有回滚事务语句RULLBACK TRANSACTION，且引发触发器的语句位于一个事务中，则该触发器触发时将回滚事务。 (
27、9) 在触发器内不能使用的命令有：CREATE、ALTER、GRANT、REVOKE、TRUNCATE TABLE、DROP等。 (10) 如果一个触发器中含有回滚事务语句RULLBACK TRANSACTION，且引发该触发器的语句位于一个批中，则一旦触发器执行，该批中引发触发器执行的语句之后的所有语句将不会执行。 实例实例9.16 9.16 建立一个触发器，防止用户在要删除表学生信息中的一条数据时，由于没有使用限制条件(误用SELECT FROM 学生信息语句)而导致删除全部数据的情况发生。 操作步骤：操作步骤： （3）本例建立了一个AFIER触发器(仅给出FOR表示默认为AFTER型触发
28、器)用于DELETE操作，首先声明一个局部变量用以存放Deleted表中数据行数。当对学生信息表执行删除操作时，检查删除的行数(Deleted表中数据行数)，如果行数1，则认为操作错误，打印提示信息并回滚事务，使删除无效。但是如果使用TRUNCATE TABLE学生信息清除表中所有数据，则触发器不会触发，可以成功删除全部数据。 归纳分析：归纳分析：使用T-SQL语句创建触发器基本语法如下。命令格式：命令格式： WITH ENCRYPTION FOR |AFTER |INSTEAD OFINSERT，UPDATE ， DELETE NOT FOR REPLICATION IF UPDATE(列名
29、)(AND|OR) UPDATE(列名)n ： (1)触发器的名称，必须符合标识符规则，并且必须在数据库中惟一。 (2)WITH ENCRYPTION用于加密CREATE TRIGGER语句文本的条目。 (3)FOR：用以指定触发器的类型，该关键字可以省略。后面为AFTER时表示触发器为AFTER类型，后面为INSTEAD OF时表示触发器为INSTEAD OF类型。如果仅指定FOR关键字，则默认为AFTER触发器。 (4) INSERT，UPDATE ， DELETE：指定在表或视图上执行哪些数据修改语句时将激活触发器。必须至少指定一个选项，如果指定的选项多于一个，需用逗号分隔。(5) IF
30、 UPDATE(列名)(AND|OR) UPDATE(列名)n：判定某一列或几列，是否被修改。如果希望仅当某一列(或几列)的数据发生变化才导致触发器动作，而其他列的数据变化不会引发触发器执行，则可以采用本选项。此时可以称之为列级触发器。(4) INSERT，UPDATE ， DELETE：指定在表或视图上执行哪些数据修改语句时将激活触发器。必须至少指定一个选项，如果指定的选项多于一个，需用逗号分隔。(5) IF UPDATE(列名)(AND|OR) UPDATE(列名)n：判定某一列或几列，是否被修改。如果希望仅当某一列(或几列)的数据发生变化才导致触发器动作，而其他列的数据变化不会引发触发器
31、执行，则可以采用本选项。此时可以称之为列级触发器。 UPDATE操作相当于先执行DELETE操作，然后再执行INSERT操作，因此要用到Deleted与Inserted两个表。 归纳分析：归纳分析： SQL Server 2000中可以建立两类触发器，一类是在触发后将执行一组新的T-SQL语句用以代替引起触发器执行的语句(原有语句没有执行，仅执行新语句)，称之为INSTEAD OF触发器：另一类是在引起触发器执行的语句执行完毕后，再执行触发器中的语句，称之为AFTER类触发器。(1)INSTEAD OF触发器既可以在表上定义，也可以在视图上定义。该类触发器依据其触发动作(UPDATE、DELE
32、TE和INSERT)的不同分为INSTEAD OF INSERT、INSTEAD OF UPDATE和INSTEAD OF DELETE三种，被引发执行时将分别代替表或视图的INSERT、UPDATE、DELETE操作，亦即表中的增删改操作没有进行。每个触发动作仅可以定义一个INSTEAD OF触发器。 实例实例9.21 9.21 在企业管理器中修改实例9.17将删除表改为添加记录。 操作步骤：操作步骤：（3）单击检查语法按钮，进行语法检查，成功后，可插入数据检验触发器的效果。 实例实例9.23 9.23 删除不用的触发器名。操作步骤：操作步骤： 归纳分析：归纳分析： 触发器创建以后，可以使用
33、系统存储过程浏览触发器的有关信息。(1)显示触发器对象的类型。创建时间、所有者信息。 (2)显示触发器的定义文本。如果触发器使用了加密(WITH ENCRYPTION选项)，则无法看到触发器的代码文本。 4. 应熟练掌握使用企业管理器创建、修改、删除和查看存储过程的操作。5. 应熟练掌握使用T-SQL语句创建、修改、删除和查看存储过程，及如何执行存储过程的操作。6. 应掌握使用T-SQL语句带参数和变量创建和修改存储过程，及如何执行存储过程。7. 了解触发器在对表中数据进行增、删、改(INSERT、UPDATE、 DELETE)操作时被触发而执行。触发器可以包含复杂的T-SQL语句。在触发器内
34、设置回滚，可以将触发它的语句所在的事务全部回滚，这样检测到错误时可以回滚事务以撤销错误。8. 触发器的优点： (1)触发器可以实现外键约束的功能，实现对表的级连修改。 (1) 熟练掌握用T-SQL语句存储过程与触发器所需的记录。 (2) 熟练掌握用T-SQL语句编写的操作过程。 (3) 熟练掌握存储过程与触发器的表示方法。 (4) 熟练掌握使用企业管理器创建、修改、删除和查看存储过程与与触发器的操作。 (5) 熟练掌握使用T-SQL语句创建、修改、删除和查看存储过程，及如何执行存储过程与与触发器的操作。 SQL Server 2000的运行、管理环境。 ： (1)触发器可以实现外键约束的功能，
35、实现对表的级连修改。 (2)触发器可以实现核查约束的功能，而且其功能更为强大。它可以使用另一个表的内容来约束触发器所在表。 (3)如果对表中数据进行了增、删、改的操作，使用触发器可以根据修改前后的差异，采取相应的对策。 (4)允许在一个表中针对不同的增删改操作设置多个触发器，以完成各类不同任务。 存储过程与触发器是SQL Server中的两类数据库对象。它们都是由T-SQL语句编写而成的过程，所不同的是存储过程是由用户根据需要调用执行的，而触发器则是由某个动作(如删除或修改一条记录)引发执行的。另外，存储过程可以不依附于表而单独存在，而触发器则必须依附于一个特定的表。它们与函数也不同，函数可以
36、直接通过函数名返回数值，其返回值可以直接在表达式中使用，而存储过程与触发器则不能直接通过其名称带回返回值，也不能直接在表达式中使用。 归纳分析：归纳分析： 系统存储过程是SQL Server内置的存储过程，可以直接用于执行一些操作，用于进行系统管理、登录管理、权限设置、数据库对象管理、查看数据库信息、数据库复制等操作。系统存储过程存放在master数据库中，以sp_作为前缀。在任何数据库中无需用master限定就直接执行。 归纳分析：归纳分析： 如果是查看或修改已有存储过程的定义，则右击要查看或修改的存储过程，在弹出的快捷菜单中，选择“属性”命令，则调出“存储过程属性”窗口，在这里可以查看或修
37、改存储过程的定义。 归纳分析：归纳分析： 在SQL Server中，使用CREATE PROCEDURE语句创建一个永久或临时存储过程。 命令格式：命令格式： WITH ENCRYPTION WITH RECOMPILE ： （1）WITH ENCRYPTION：对存储过程进行加密。 （2）WITH RECOMPILE：对存储过程重新编译。 归纳分析：归纳分析： 只有数据库所有者和存储过程的所有者才有权修改存储过程名称。不宜轻易修改存储过程名称，因为系统中许多应用程序可能都要调用存储过程，如果修改了存储过程的名称，则这些应用程序都要随之改动。命令格式：命令格式： 修改存储过程名称的方法与修改表
38、名称的方法类同，读者可以参照执行。 使用企业管理器修改存储过程名称的方法也类似于修改表名称的方法，即展开指定的数据库节点，单击存储过程，在右边的窗口中选中一个需要修改名称的存储过程右击之，在弹出的快捷菜单中选择“重命名”。 实例实例9.9 9.9 使用企业管理器删除存储过程。 操作步骤：操作步骤： 归纳分析：归纳分析： 在SQL Server中，使用CREATE PROCEDURE语句创建一个永久或临时存储过程。 命令格式：命令格式： WITH ENCRYPTION WITH RECOMPILE ： （1）WITH ENCRYPTION：对存储过程进行加密。 （2）WITH RECOMPILE
39、：对存储过程重新编译。 实例实例9.11 9.11 使用企业管理器查看存储过程的信息。 操作步骤：操作步骤： 在图9.4所示属性对话框中可以查询存储过程的名称、所有者、建立时间和文本信息。 在图9.3所示窗口的右部选中一个存储过程并右击，在弹出的级联菜单中依次选择“所有任务”、“显示相关性”，系统会弹出“显示相关性”对话框，该对话框显示一个存储过程所引用的表及其列信息。 命令格式：命令格式： WITHEN ENCRYPTION WITHRE RECOMPILE ： “参数名”和定义局部变量一样，必须以符号为前缀，要指定数据类型，多个参数定义要用逗号“，”隔开。在执行存储过程时该参数将由指定的参
40、数值来代替，如果执行时未提供该参数的参数值，则如果这里定义了默认值则使用该值作为执行时的参数值，默认值可以是常量或空(NULL)。 归纳分析：归纳分析： SQL Server 2000中可以建立两类触发器，一类是在触发后将执行一组新的T-SQL语句用以代替引起触发器执行的语句(原有语句没有执行，仅执行新语句)，称之为INSTEAD OF触发器：另一类是在引起触发器执行的语句执行完毕后，再执行触发器中的语句，称之为AFTER类触发器。(1)INSTEAD OF触发器既可以在表上定义，也可以在视图上定义。该类触发器依据其触发动作(UPDATE、DELETE和INSERT)的不同分为INSTEAD
41、OF INSERT、INSTEAD OF UPDATE和INSTEAD OF DELETE三种，被引发执行时将分别代替表或视图的INSERT、UPDATE、DELETE操作，亦即表中的增删改操作没有进行。每个触发动作仅可以定义一个INSTEAD OF触发器。 实例实例9.13 9.13 创建的存储过程，修改成当输入学生学号时可以查询某个学生的成绩：不输入学号时，则查询全部学生的成绩。 操作步骤：操作步骤： 在执行存储过程的语句中，有两种方式来传递参数值，分别是使用参数名传递参数值和按参数位置传递参数值。 使用参数名传递参数值，是通过语句“参数名：参数值”给参数传递值。当存储过程含有多个输入参数
42、时，参数值可以按任意顺序制定，对于允许空值和具有默认值的输入参数可以不给出参数的传递值。 执行使用参数名传递参数值的存储过程的命令格式：命令格式： 按参数位置传递参数值，不显式地给出“参数名”，而是按照参数定义的顺序给出参数值。按位置传递参数时，也可以忽略允许空值和具有默认值的参数，但不能因此破坏输入参数的指定顺序。必要时，使用关键字“DEFAULT”作为默认值的占位。 实例实例9.15 9.15 在表学生成绩中建立一个触发器，当向表中插入一条记录时，检查该学生的学号是否存在于表学生信息中，如果存在，则插入；否则，打印“学号不存在”，并回滚事务，使插入数据无效。 操作步骤：操作步骤： （3）本
43、例建立了一个AFIER触发器(仅给出FOR表示默认为AFTER型触发器)用于DELETE操作，首先声明一个局部变量用以存放Deleted表中数据行数。当对学生信息表执行删除操作时，检查删除的行数(Deleted表中数据行数)，如果行数1，则认为操作错误，打印提示信息并回滚事务，使删除无效。但是如果使用TRUNCATE TABLE学生信息清除表中所有数据，则触发器不会触发，可以成功删除全部数据。 (4) INSERT，UPDATE ， DELETE：指定在表或视图上执行哪些数据修改语句时将激活触发器。必须至少指定一个选项，如果指定的选项多于一个，需用逗号分隔。(5) IF UPDATE(列名)(
44、AND|OR) UPDATE(列名)n：判定某一列或几列，是否被修改。如果希望仅当某一列(或几列)的数据发生变化才导致触发器动作，而其他列的数据变化不会引发触发器执行，则可以采用本选项。此时可以称之为列级触发器。 实例实例9.23 9.23 删除不用的触发器名。操作步骤：操作步骤： ： (1)触发器可以实现外键约束的功能，实现对表的级连修改。 (2)触发器可以实现核查约束的功能，而且其功能更为强大。它可以使用另一个表的内容来约束触发器所在表。 (3)如果对表中数据进行了增、删、改的操作，使用触发器可以根据修改前后的差异，采取相应的对策。 (4)允许在一个表中针对不同的增删改操作设置多个触发器，
45、以完成各类不同任务。 实例实例9.17 9.17 触发器执行过程中的两个表操作过程。 操作步骤：操作步骤： 触发器是在表上建立的，我们将触发器所依附的表称为触发器表。当修改表的数据而引发了触发器时，触发器将执行一系列T-SQL命令，在执行这些动作之前系统首先自动建立两个表，Deleted表与Inserted表。这两个表临时驻留在内存当中，其结构与触发器表完全相同，一旦触发器执行完毕，两个表也随之消失。可以使用Deleted表与Inserted表测试某些数据修改的效果以及设置触发器操作的条件，但不能直接修改这两个表中的数据。 Deleted表存放由于执行DELETE或UPDATE操作时从触发器表
46、(被删除数据的表)中删除的数据行，即触发器表中需要删除的数据将被移到Deleted表中。因此这两个表没有相同的数据行，如图9.15所示。 Inserted表用于存放执行INSERT或UPDATE操作时向触发器表(被插入数据的表)中 插入的数据行，即新的数据行被同时插入到两个表触发器表和Inserted表中，因此这两个表有相同的数据行，如图9.16所示。 UPDATE操作相当于先执行DELETE操作，然后再执行INSERT操作，因此要用到Deleted与Inserted两个表。 实例实例9.18 9.18 建立一个触发器，防止用户在要删除表学生信息中的一条数据时，由于没有使用限制条件(误用SEL
47、ECT * FROM 学生信息 语句)而导致删除全部数据的情况发生。 操作步骤：操作步骤： （3）本例建立了一个AFIER触发器(仅给出FOR表示默认为AFTER型触发器)用于DELETE操作，首先声明一个局部变量用以存放Deleted表中数据行数。当对学生信息表执行删除操作时，检查删除的行数(Deleted表中数据行数)，如果行数1，则认为操作错误，打印提示信息并回滚事务，使删除无效。但是如果使用TRUNCATE TABLE 学生信息清除表中所有数据，则触发器不会触发，可以成功删除全部数据。 实例实例9.199.19在学生成绩表上建立一个列级触发器trigger_学生成绩，仅当修改班级时，触
48、发器执行，其他情况触发器不执行。操作步骤：操作步骤： 归纳分析：归纳分析： SQL Server 2000中可以建立两类触发器，一类是在触发后将执行一组新的T-SQL语句用以代替引起触发器执行的语句(原有语句没有执行，仅执行新语句)，称之为INSTEAD OF触发器：另一类是在引起触发器执行的语句执行完毕后，再执行触发器中的语句，称之为AFTER类触发器。(1)INSTEAD OF触发器既可以在表上定义，也可以在视图上定义。该类触发器依据其触发动作(UPDATE、DELETE和INSERT)的不同分为INSTEAD OF INSERT、INSTEAD OF UPDATE和INSTEAD OF
49、DELETE三种，被引发执行时将分别代替表或视图的INSERT、UPDATE、DELETE操作，亦即表中的增删改操作没有进行。每个触发动作仅可以定义一个INSTEAD OF触发器。 (2) AFTER触发器只能在表上定义。该类触发器分为AFTER INSERTAFTER INSERT、AFTER UPDATEAFTER UPDATE和和AFTER AFTER DELETEDELETE三种，被引发时将分别在表的INSERT、UPDATE、DELETE操作之后执行，也就是说只有在表中数据的增删改操作、外键约束和核查约束均成功完成后，才能执行AFTER触发器。每个触发动作可以定义多个AFTER触发器
50、，并可以指定这些触发器的执行顺序。 触发器建立后可以对其进行修改，既可以修改它的名称，也可以修改它的定义。 实例实例9.20 9.20 将触发器trigger_student改为tri_学生。 操作步骤：操作步骤： （1）启动“查询分析器”，输入如下SQL语句： sp_rename trigger_studentsp_rename trigger_student，tri_tri_学生学生（2）按“F5”键或单击工具栏“执行查询”图标。 归纳分析：归纳分析： ： 实例实例9.21 9.21 在企业管理器中修改实例9.17将删除表改为添加记录。 操作步骤：操作步骤：（3）单击检查语法按钮，进行语法
