第九章 存储过程和触发器

1、数据库技术及应用 -SQL Server 第第9 9章章 存储过程和触发器存储过程和触发器 教学提示教学提示：在数据库实际应用中，存在有带变量数据处理需求，如某：在数据库实际应用中，存在有带变量数据处理需求，如某 班学生信息表、某老师带过的学生、某班某门课不及格学生等。班学生信息表、某老师带过的学生、某班某门课不及格学生等。 自定义数、存储过程、触发器是由一系列的自定义数、存储过程、触发器是由一系列的T-SQL T-SQL 语句组成的子语句组成的子 程序，用来满足更高的应用需求，可以说是程序，用来满足更高的应用需求，可以说是SQLSQL程序设计的灵魂，程序设计的灵魂， 掌握和使用好它们对数据库

2、的开发与应用非常重要。掌握和使用好它们对数据库的开发与应用非常重要。 教学要求教学要求： 存储过程、触发器的概念、用途、创建方法。存储过程、触发器的概念、用途、创建方法。 编写简单存储过程、触发器。编写简单存储过程、触发器。 数据库技术及应用 -SQL Server 第第9 9章章 存储过程和触发器存储过程和触发器 存储过程的概念存储过程的概念 创建存储过程创建存储过程 查看存储过程信息查看存储过程信息 执行存储过程执行存储过程 修改存储过程修改存储过程 删除存储过程删除存储过程 常用系统存储过程常用系统存储过程 触发器的概念触发器的概念 创建触发器创建触发器 触发器使用限制触发器使用限制 修

3、改触发器修改触发器 删除触发器删除触发器 使用触发器的优点使用触发器的优点 9.1 存储过程 9.2 触发器 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 1 1 存储过程存储过程 存储过程是存储过程是SQL Server应用最广泛、最灵活的应用最广泛、最灵活的 技术技术,它能提高它能提高SQL Server服务器的性能和工作服务器的性能和工作 率。本节将介绍率。本节将介绍 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 存储过程是已经存储在SQL Serv

4、er服务器中的一组预 编译过的Transact-SQL语句 ；是一段需要多次调用 的、实现某个特定任务的程序代码。存储过程可以接 受参数，用户通过指定存储过程的名字并给出参数 （如果该存储过程带有参数）来执行它。 存储过程既不能在被调用的位置上返回数据，也不能 被引用在语句当中。 存储过程在创建时就被编译和优化，调用一次后相关 信息就保存在内存中，下次调用时可直接执行。 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 系统存储过程和用户定义存储过程。系统存储系统存储 过程过程主要存储在master 数据库中并以sp_为前 缀，

5、在任何数据库中都可以调用，在调用时不 必在存储过程前加上数据库名。用户自定义存用户自定义存 储过程储过程是由用户创建的，是用来完成某项任务 的存储过程。存储在创建时的数据库中。 按存储过程所处的位置，又可分为本地存储过本地存储过 程程和远程存储过程远程存储过程。 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 当创建存储过程时，需要确定存储过程的三个组成部当创建存储过程时，需要确定存储过程的三个组成部 分：分： 所有的输入参数及执行的输出结果；所有的输入参数及执行的输出结果； 被执行的针对数据库的操作语句，包括调用其他存储被执

6、行的针对数据库的操作语句，包括调用其他存储 过程的语句；过程的语句； 返回给调用者的状态值，以指明调用是否成功返回给调用者的状态值，以指明调用是否成功. . 数据库技术及应用 -SQL Server (1)(1)展开展开【服务器组服务器组】、展开、展开 服务器服务器 。 (2)(2)展开展开【数据库数据库】，展开数据库，单击，展开数据库，单击【存储过程存储过程】， 在详细信息窗口中显示已有的存储过程。如图在详细信息窗口中显示已有的存储过程。如图9.9.1 1所示。所示。 其中，类型列显示其中，类型列显示 系统系统 为系统存储过程，显示为系统存储过程，显示 用户用户 为为 用户创建的存储过程。用

7、户创建的存储过程。 (3)(3)右击右击【存储过程存储过程】，在快捷菜单中选择，在快捷菜单中选择【新建存储新建存储 过程过程】，弹出，弹出【存储过程属性存储过程属性新建存储过程新建存储过程】对话框，对话框， 并给出一个通用模板。并给出一个通用模板。如图如图9.9.2 2所示。所示。 (4)(4)在在“文本文本”框中输入存储过程的正文。框中输入存储过程的正文。 (5)(5)单击单击【检查语法检查语法】，检查语法。，检查语法。 (6)(6)单击单击【确定确定】完成。完成。 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 数据库技术及应用 -SQL Server 第第9 9

8、章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 主要语法格式： CREATE PROCDURE CREATE PROCDURE 存储过程名存储过程名 参数参数 参数的数据类型参数的数据类型 OUTPUT ,.n OUTPUT ,.n AS AS 任意数量的任意数量的TransctTransct-SQL -SQL 语句语句 OUTPUTOUTPUT选项可将参数的值返回给调用语句。 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 例例9.2 9.2 （p p。163163） 创建一个创建一个“优秀学生优秀学生”的用

9、户存储过程的用户存储过程 ，将学生成绩为，将学生成绩为9090分以上的学生检索出来分以上的学生检索出来 解：解： 在查询分析器中输入下面的命令即可：在查询分析器中输入下面的命令即可： Create Proc Create Proc 优秀学生优秀学生 as select as select 学生学生. .学号，学生学号，学生. .姓名，姓名， 学生学生. .班级编号，课程班级编号，课程. .课程名称，成绩课程名称，成绩. .成绩成绩 from from 学生学生 inner join inner join 成绩成绩 on on 学生学生. .学号学号=成绩成绩. .学号学号 inner join

10、 inner join 课程课程 on on 成绩成绩. .课程编号课程编号=课程课程. .课程编号课程编号 where where 成绩成绩. .成绩成绩9090 GO GO 数据库技术及应用 -SQL Server 展开展开【服务器组服务器组】，展开服务器。，展开服务器。 展开展开【数据库数据库】，展开数据库，单击，展开数据库，单击【存储过程存储过程】，在详，在详 细信息窗口中显示已有的存储过程。细信息窗口中显示已有的存储过程。 右击查看、修改存储过程，单击右击查看、修改存储过程，单击【属性属性】按钮，此时弹出按钮，此时弹出 【存储过程属性存储过程属性】对话框，便可查看或编辑存储过程的代码

11、。对话框，便可查看或编辑存储过程的代码。 打开打开【查询分析器查询分析器】，登录到要使用的服务器。，登录到要使用的服务器。 展开展开 所要的数据库所要的数据库 ，展开，展开【存储过程存储过程】文件夹。文件夹。 右击要查看、修改的右击要查看、修改的 用户存储过程用户存储过程 ，单击，单击【编辑编辑】菜单。菜单。 在弹出的编辑窗口可查看和编辑用户存储过程的代码等。在弹出的编辑窗口可查看和编辑用户存储过程的代码等。 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 数据库技术及应用 -SQL Server 语法： executeexecute 存储过程名存储过程名 参数参数1

12、 1， ，参数参数n n 例9.3 （p。163） 运行已有的“优秀学生” 用户存储过程。 解： 可在查询分析器中输入命令 exec 优秀学生 并直接执行 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 对存储过程的修改，可以用【企业管理器】或T-SQL 语句 进行。其中：使用【企业管理器】修改存储过程与查看存储 过程等同，而用T-SQL 语句修改存储过程的语法格式类同 CREATE PROC，即CREATE换成ALTER。 例9.4 修改已建立的“优秀

13、学生”用户过程，查看优秀 学生所在的班级。 解：在查询分析器中键入如下命令并执行 Alter Proc 优秀学生 As select 学生.学号，学生.姓名，学生.班级编号，课 程.课程名称，成绩.成绩 from 学生 inner join 成绩 on 学生.学号=成绩.学号 inner join 课程 on 成绩.课程编号=课程.课程编号 where 成绩.成绩90 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 1 1 用用DROP PROC DROP PROC 语句删除存储过程语句删除存储过程 DROP PROCEDUR

14、E DROP PROCEDURE 所有者所有者.存储过程存储过程 ,.,. 2 2 用用【企业管理器企业管理器】删除存储过程删除存储过程 (1)(1)展开展开【服务器组服务器组】、展开、展开 服务器服务器 。 (2)(2)展开展开【数据库数据库】，展开数据库，单击，展开数据库，单击【存储过程存储过程】，在，在 详细信息窗口中显示已有的存储过程。详细信息窗口中显示已有的存储过程。 (3)(3)右击右击 要删除的用户存储过程要删除的用户存储过程 单击单击【删除删除】弹出弹出【去除去除 对象对象】对话框，单击对话框，单击【全部去除全部去除】完成。完成。 例例9.9.5(p. 165) 5(p. 16

15、5) 删除以建立的优秀成绩用户存储过删除以建立的优秀成绩用户存储过 程。程。 数据库技术及应用 -SQL Server 模块化的程序设计。模块化的程序设计。 执行速度快。执行速度快。 减少网络通信量。减少网络通信量。 保证系统的安全性。保证系统的安全性。 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 2 2 触发器触发器 当表或视图中的某些重要数据发生变化当表或视图中的某些重要数据发生变化 时，为了保时，为了保 持数据的一致性和完整性，可以自动执行某段

16、程序保持数据的一致性和完整性，可以自动执行某段程序保 证相关联其他数据也跟着进行相应的变化。能完成这证相关联其他数据也跟着进行相应的变化。能完成这 种功能的程序就是触发器。种功能的程序就是触发器。 数据库技术及应用 -SQL Server 触发器触发器是特殊类型的存储过程，它能在任何试图改变表或是特殊类型的存储过程，它能在任何试图改变表或 视图中由触发器保护的数据时执行。触发器主要通过视图中由触发器保护的数据时执行。触发器主要通过操操 作事件作事件(INSERT(INSERT、UPDATEUPDATE、DELETE)DELETE)进行触发而被进行触发而被自动执自动执 行行，不能直接调用执行，也

17、不能被传送和接受参数。，不能直接调用执行，也不能被传送和接受参数。 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 分类：分类： 据触发语句分为据触发语句分为:INSERT:INSERT、UPDATEUPDATE和和DELETEDELETE触发器；触发器； 据触发时刻分为据触发时刻分为:AFTER(:AFTER(之后之后) )触发器和触发器和INSTEADINSTEAD( (替代替代) )触发触发 器。器。 触发器与表或视图触发器与表或视图是不能分开的，触发器定义在一个表或是不能分开的，触发器定义在一个表或 视图中，当在表或视图中执行插入（视图中，当在表或视图中执行插

18、入（INSERTINSERT）、修改）、修改 （UPDATEUPDATE）、删除（）、删除（DELETEDELETE）操作时触发器被触发自动）操作时触发器被触发自动 执行。当表或视图被删除时与它关联的触发器也一同被执行。当表或视图被删除时与它关联的触发器也一同被 删除。删除。 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 AFTER(AFTER(之后之后) )触发器和触发器和INSTEADINSTEAD( (替代替代) )触发器触发器： AFTERAFTER触发器触发器是在执行触发操作（是在执行触发操作（INSERTINSE

19、RT、UPDATE UPDATE 或或 DELETEDELETE） 和处理完约束之后激发，和处理完约束之后激发， INSTEADINSTEAD触发器触发器是由触发器的程序代替代替是由触发器的程序代替代替INSERTINSERT、UPDATE UPDATE 或或 DELETEDELETE语句执行，在处理约束之前激发。语句执行，在处理约束之前激发。 所以，若执行所以，若执行INSERTINSERT、UPDATE UPDATE 或或 DELETEDELETE语句违犯约束条件时，语句违犯约束条件时， 将不执行将不执行A AF FTERTER触发器；而在定义触发器；而在定义INSTEAD OFINSTE

20、AD OF触发器的表或视触发器的表或视 图上执行图上执行INSERTINSERT、UPDATE UPDATE 或或 DELETEDELETE语句时，会激发触发器语句时，会激发触发器 而不执行这些数据操作语句本身。而不执行这些数据操作语句本身。 数据库技术及应用 -SQL Server CREATE TRIGGER CREATE TRIGGER 触发器名触发器名 ON ON 表名或视图名表名或视图名 FOR FOR | AFTER | INSTEAD OF | AFTER | INSTEAD OF INSERT , UPDATE , DELETE INSERT , UPDATE , DELETE

21、 AS AS IF UPDATE( IF UPDATE(列名列名1 1) AND|OR UPDATE() AND|OR UPDATE(列名列名2 2) . .n n SQLSQL语句语句 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 触发器名称触发器名称：触发器名。：触发器名。 何处触发何处触发：表名或视图名。：表名或视图名。 何时激发何时激发：FOR|AFTERFOR|AFTER指定为指定为AFTERAFTER触发器，触发器，INSTEAD OFINSTEAD OF指指 定为定为INSTEADINSTEAD触发器。触发器。 数据库技术及应用 -SQL Server

22、 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 何种数据修改语句触发何种数据修改语句触发：INSERTINSERT指定为指定为INSERTINSERT触发器；触发器； UPDATE UPDATE 指定为指定为UPDATEUPDATE触发器；触发器；DELETEDELETE指定为指定为DELETEDELETE触触 发器。发器。 何列数据修改时触发何列数据修改时触发：可选项：可选项IF UPDATE(IF UPDATE(列名列名1) 1) AND|OR UPDATE(AND|OR UPDATE(列名列名2) .n 2) .n 用于指定如果测用于指定如果测 试到在试到在

23、列名列名11且或且或 列名列名22上进行的上进行的 INSERT INSERT 或或 UPDATE UPDATE 操作时触发。不能用于操作时触发。不能用于 DELETE DELETE 语句触发器。语句触发器。 如何触发如何触发：SQLSQL语句指定触发器触发时所作的操作。语句指定触发器触发时所作的操作。 数据库技术及应用 -SQL Server create trigger Trigger_学生_性别 on 学生 for insert as if exists （select * from 学生 where 性别 not in （男，女） begin raiserror（请输入合法的性别！，1

24、6，1） rollback transaction end 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 可以在查询分析器中直接使用可以在查询分析器中直接使用SQLSQL命令创建触发器完成以上命令创建触发器完成以上 需求。需求。 触发器代码如下触发器代码如下 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 触发器运行时触发器运行时SQL ServerSQL Server会在内存中自动创建和会在内存中自动创建和 管理管理deleted deleted 表表和和 inserted inserted 表表

25、，用于在触发，用于在触发 器内部测试某些数据修改的效果及设置触发器器内部测试某些数据修改的效果及设置触发器 操作的条件，用户不能直接对表中的数据进行操作的条件，用户不能直接对表中的数据进行 更改。更改。DELETEDELETE触发器触发器会将删除旧行的内容保存会将删除旧行的内容保存 在在deleteddeleted表中，表中，INSERTINSERT触发器触发器会将添加新行会将添加新行 的内容保存在的内容保存在insertedinserted表中，而表中，而UPDATEUPDATE触发器触发器 将替换旧行的内容保存在将替换旧行的内容保存在deleteddeleted表中、替换表中、替换 的新行

26、内容保存的新行内容保存insertedinserted表中表中 语句中。语句中。 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 (1)(1)展开展开【服务器组服务器组】、展开要操作、展开要操作 服务器服务器 。 (2)(2)展开展开【数据库数据库】，展开创建触发器的表所在的数据库，展开创建触发器的表所在的数据库， 单击单击【表表】，在详细信息窗口中显示已有的数据表。，在详细信息窗口中显示已有的数据表。 (3)(3)右击右击 创建触发器的表创建触发器的表 选择选择【所有任务所有任务】、【管理触发管理触发 器器】菜单，弹出菜单，

27、弹出【触发器属性触发器属性】对话框。对话框。 (4)(4)在在【触发器属性触发器属性】对话框对话框【名称名称】下拉列表框中选择下拉列表框中选择 新建新建 ，在，在【文本文本】编辑框中把编辑框中把OWNER.PROCEDURE OWNER.PROCEDURE NAMENAME改为所有者与要创建的触发器名，在改为所有者与要创建的触发器名，在ASAS之后输入之后输入 触发器正文。触发器正文。 (5)(5)单击单击【检查语法检查语法】，检查语法是否正确。，检查语法是否正确。 (6)(6)单击单击【确定确定】保存。保存。 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触

28、发器自定义函数、存储过程和触发器 例9.6(P. 167) 使用企业管理器创建一个触发器,为“学生” 表 创建一个 “Trigger_学生_性别”触发器，用来控制“学生” 表中“性别”列的输入，当该列输入的数据不是“男”或 “女”时，触发触发器事件，提示“请输入合法的性别”。 数据库技术及应用 -SQL Server 触发器的区别 INSTEAD OF 触发器用来代替通常的触发动作， 即当对表进行INSERT、UPDATE 或 DELETE 操 作时，系统不是直接对表执行这些操作，而是 把操作内容交给触发器，让触发器检查所进行 的操作是否正确。如正确才进行相应的操作。 因此，INSTEAD O

29、F 触发器的动作要早于表的 约束处理。 INSTEAD OF 触发器的操作有点类似于完整性 约束。在对数据库的操纵时，有些情况下使用 约束可以达到更好的效果，而如果采用触发器， 则能定义比完整性约束更加复杂的约束。有关 触发器与约束的比较，请参见联机丛书。 INSTEAD OF 触发器不仅可在表上定义，还可 在带有一个或多个基表的视图上定义，但在作 为级联引用完整性约束目标的表上限制应用。 数据库技术及应用 -SQL Server 触发器的区别 AFTER 触发器定义了对表执行了 INSERT、UPDATE 或 DELETE 语句操作 之后再执行的操作。比如对某个表中的 数据进行了更新操作后，

30、要求立即对相 关的表进行指定的操作，这时就可以采 用 AFTER 触发器。AFTER 触发器只能 在表上指定，且动作晚于约束处理。 每一个表上只能创建一个 INSTEAD OF 触发器，但可以创建多个 AFTER 触发器。 数据库技术及应用 -SQL Server create trigger create trigger 语句只能作为批处理的第一条语句。语句只能作为批处理的第一条语句。 在表中如果既有约束又有触发器，则在执行中约束优在表中如果既有约束又有触发器，则在执行中约束优 先于触发器。而且如果在操作中触发器与约束发生冲先于触发器。而且如果在操作中触发器与约束发生冲 突，触发器将不执行。

31、突，触发器将不执行。 触发器中不允许包含以下触发器中不允许包含以下SQLSQL语句：语句：ALTER DATABASEALTER DATABASE、 CREATE DATABASCREATE DATABAS、DROP DATABASEDROP DATABASE、RESTORE RESTORE DATABASEDATABASE、RESTORE LOGRESTORE LOG等。等。 不能在视图或临时表上建立触发器，但是在触发器定不能在视图或临时表上建立触发器，但是在触发器定 义中可以引用视图或临时表。当触发器引用视图或临义中可以引用视图或临时表。当触发器引用视图或临 时表，并产生两个特殊的表：时表

32、，并产生两个特殊的表：deleteddeleted表和表和 inserted inserted 表。这两个表由系统进行创建和管理，用户不能直接表。这两个表由系统进行创建和管理，用户不能直接 修改其中的内容，其结构与触发表相同，可以用于触修改其中的内容，其结构与触发表相同，可以用于触 发器的条件测试。发器的条件测试。 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 数据库技术及应用 -SQL Server 用【企业管理器】修改触发器正文的操作步骤与创建触发器相 似，只需在名称框中选择要修改的触发器即可。 格式为： sp_rename 旧的触发器名 新的触发器名 ALTE

33、R TRIGGERALTER TRIGGER的语法格式类与CREATE TRIGGERCREATE TRIGGER，只需将 CREATE换成ALTER。 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 数据库技术及应用 -SQL Server 例9.7 修改一个已有的“Trigger_学生”触发器，用来控 制用户使其不能更新“学生”表中“姓名”列，触发触发 事件，提示“您不能修改表中学生的姓名” 解：在查询分析器中，输入并执行如下命令： Alter Trigger Trigger_学生 On 学生 for update as If update （姓名） begin

34、raiserror(您不能修改表中学生的姓名，16，1） rollback trsaction end 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 数据库技术及应用 -SQL Server 用户可以删除不再需要的触发器，此时原来的触发表以及 表中的数据不受影响。如果删除表，则表中所有的触发器 将被自动删除。 使用使用DROP TRIGGER DROP TRIGGER DROP TRIGGER DROP TRIGGER 触发器名触发器名 见 例9.8 （p。 170） 使用使用【企业管理器企业管理器】 用【企业管理器】删除触发器的操作步骤与修改触 发器相似，在名称框

35、中选择要删除的触发器后，点 击删除按钮，即可删除该触发器。 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 数据库技术及应用 -SQL Server 第第9 9章章 自定义函数、存储过程和触发器自定义函数、存储过程和触发器 F 引用完整性引用完整性( (外键外键) )的级联更新、级联删除用来的级联更新、级联删除用来 实现主键与引用键之间的级联，而触发器实现主键与引用键之间的级联，而触发器可实可实 现数据库中的表间记录数据的级联更改和级联现数据库中的表间记录数据的级联更改和级联 删除删除。如。如【例例9.129.12】所示。所示。 F 触发器触发器可以强制比引用完整性可以强制比引用完整性( (外键外键) )、CHECKCHECK 约束更为复杂的约束。约束更为复杂的约束。如如【例例9.139.13】所示。所示。 F 触发器也触发器也可以评估数据修改前后的表状态，并可以评估数据修改前后的表状态，并 根据其差异采取对策。根据其差异采取对策。 数据库技术及应用 -SQL Server 实验目的实验目的 练习用企业管理器和查询分析器创建存储过程、触 发器，并掌握创建和调用它们的方法。 实验内容实验内容 通过【企业管理器】或【查询分析器】创