单元5数据库的存储过程和触发器

1、高等教育出版社目录单元1 SQL SERVER 2008基础单元2 创建数据库和表单元3 数据库的查询和视图单元4 数据库的索引、默认值和规则单元5 数据库的存储过程和触发器单元6 数据库的日常管理与维护单元7 数据库的安全管理单元8 SQL Server 数据库应用程序开发单元9 数据库在电子商务中的应用单元5 数据库的存储过程和触发器 通过本单元的学习，掌握存储过程的创建和执行；存储过程的管理；触发器的创建、修改和删除，并能熟练使用INSERT触发器、UPDATE触发器、DELETE触发器。单元5 数据库的存储过程和触发器 1.理解存储过程、触发器概念和类型。2.掌握存储过程的操作和应用范

2、围。3.掌握触发器的创建和管理。1.了解存储过程的概念和使用存储过程的优点。2.掌握使用对象资源管理器和T-SQL命令管理存储过程的创建、执行、修改、删除。3.掌握参数存储过程的使用。4.掌握使用对象资源管理器和T-SQL命令来管理触发器的创建、删除。5.正确应用触发器。单元5 数据库的存储过程和触发器 任务5.1 创建和管理存储过程任务5.2 创建和管理触发器任务5.1 创建和管理存储过程当开发数据库的时候，会有一些反复使用的功能，为了方便使用，可以将某些功能的语句独立保存起来，创建为存储过程，在需要的时候进行调用。本任务就是创建和管理存储过程。1.了解存储过程的概念、使用存储过程的优点以及

3、存储过程的类型。2.掌握使用对象资源管理器和T-SQL命令进行存储过程的创建、执行、修改和删除。3.掌握带参数存储过程的使用。任务5.1 创建和管理存储过程活动 5.1.1 使用对象资源管理器创建简单存储过程活动 5.1.2 使用T-SQL命令创建带参数存储过程活动 5.1.3 执行存储过程活动 5.1.4 修改存储过程活动 5.1.5 重命名存储过程活动 5.1.6 删除存储过程活动 5.1.1 使用对象资源管理器创建简单存储过程 在SQL Server 2008中创建存储过程有两种方法，一种是在对象资源管理器中使用图形化界面方式创建，另一种是通过T-SQL命令创建。本活动主要讲解用对象资源

4、管理器创建的方法。案例1 在图书信息管理数据库中创建“查询医药类图书”存储过程，从“图书信息”表中查询“医药”类图书的信息。【实现步骤】第1步：在对象资源管理器中依次展开“数据库”“图书信息管理”“可编程性”，右键单击“存储过程”，选择“新建存储过程”命令，这时SQL Server就会打开存储过程的模板，使用者可在此模板的基础上编辑存储过程如下。CREATE PROCEDURE 查询医药类图书 - CREATE PROCEDURE 创建存储过程ASBEGINSELECT * FROM 图书信息 WHERE 类别=医药 SET NOCOUNT ON; -需返回受影响记录的行数END活动 5.1.

5、2 使用T-SQL命令创建带参数存储过程 在前面的案例1中我们学习了使用对象资源管理器创建 “查询医药类图书”的简单存储过程，接下来，我们来学习使用T-SQL语句来创建带参数的存储过程。案例2 在图书信息管理数据库中创建“按类别查询图书”存储过程，在运行存储过程时输入参数，参数为要查询的图书类别，根据参数查询指定类别的图书，该存储过程命名为“按类别查询图书”。【实现步骤】第1步：在对象资源管理器中单击”按钮，输入如下的T-SQL命令：CREATE PROCEDURE 按类别查询图书 图书类别 VARCHAR(10) -声明参数图书类别，类型是VARCHAR(10)ASBEGIN SELECT

6、* FROM 图书信息 WHERE 类别= 图书类别 SET NOCOUNT ON; ENDGO活动 5.1.3 执行存储过程 当存储过程创建好后，要实现其功能，还需要执行存储过程。案例3 在对象资源管理器中执行案例1中建立的简单存储过程“查询医药类图书”。【实现步骤】单击展开“图书信息管理”数据库的“存储过程”，右键单击“查询医药类图书”存储过程，选择 “执行存储过程(E).”命令，如图5.1.2所示。在打开的“执行过程”对话框，单击“确定”按钮，即可执行该存储过程。 案例4 使用T-SQL命令执行案例2建立的、带参数的存储过程“按类别查询图书”【实现步骤】在对象资源管理器单击按钮，输入执行

7、存储过程语句如下：EXEC 按类别查询图书 图书类别=医药说明： “图书类别”是变量名称，在执行存储过程中赋值为“医药”，即表示查询“医药”类图书信息。单击工具栏 按钮，执行命令。活动 5.1.4 修改存储过程案例5 使用对象资源管理器修改存储过程“查询医药类图书”，将查询结果只显示“书名,定价,类别”三列的值。【实现步骤】第1步：单击展开“图书信息管理”数据库的“存储过程”节点，在“查询医药类图书”存储过程上单击右键，选择 “修改”命令。第2步：在显示的代码中将“SELECT * FROM 图书信息 WHERE 类别 医药”修改为“SELECT 书名，定价，类别 FROM图书信息 WHERE

8、 类别 医药”。第3步：单击“执行”按钮，完成存储过程的修改。案例6 使用T-SQL命令修改存储过程“按类别查询图书”，将查询结果只显示“书名,定价,类别”三列的值。【实现步骤】单击按钮，打开查询编辑器，输入代码如下，单击“执行”按钮完成存储过程的修改。ALTER PROCEDURE dbo.查询医药类图书 - ALTER PROCEDURE 修改存储过程ASBEGINSELECT 书名,定价,类别 FROM 图书信息 WHERE 类别 医药 SET NOCOUNT ON; -需返回受影响记录的行数END活动 5.1.5 重命名存储过程案例7 使用T-SQL命令重命名存储过程“按类别查询图书”

9、为“按类别查询书籍”【实现步骤】单击 按钮，在查询编辑器中输入以下语句：SP_RENAME 按类别查询图书,按类别查询书籍 单击“执行”按钮，完成存储过程的重命名。活动 5.1.6 删除存储过程案例8 使用T-SQL命令删除存储过程“查询医药类图书”【实现步骤】单击 按钮，在查询编辑器输入以下代码： DROP PROCEDURE 查询医药类图书单击“执行”按钮，将删除“查询医药类图书”存储过程。任务5.1 创建和管理存储过程1.存储过程概念 存储过程是一组完成特定功能的SQL语句集，经过编译后存储在数据库中。也可以把存储过程看成是以数据库对象形式存储在SQL Server服务端的一段程序或函数

10、。存储过程中可以包含SQL语句、程序流和逻辑等，可以接收参数、输出参数、返回单个或多个结果。存储过程可以由应用程序通过一个调用来执行，它是封装重复性工作的一种有效方法，使用存储过程，可以使数据库的管理工作变得更加容易。任务5.1 创建和管理存储过程2.存储过程类型SQL Server存储过程可以分为五类：系统存储过程、用户存储过程、扩展存储过程、临时存储过程和远程存储过程。系统存储过程存储在SQL Server的master数据库中，一般以“sp_”作为前缀。在上一单元中我们使用过SP_BINDEFAULT、SP_UNBINDEFAULT、SP_BINDRULE、SP_UNBINDRULE等都

11、是系统存储过程。本单元重点介绍的是用户存储过程的创建和使用。任务5.1 创建和管理存储过程3.简单存储过程简单存储过程是指不带参数的存储过程。存储过程可以使用对象资源管理或使用T-SQL的CREATE PROCEDURE语句进行创建，并可用ALTER PROCEDURE语句进行修改。存储过程定义包括两个主要组成部分:过程名称及其参数的说明和过程的主体。 4.参数存储过程 参数和变量能够让存储过程动态化。输入参数使得运行过程的用户可以向存储过程传递值，产生特定于请求的结果集。输出参数查询返回的标准结果集上扩展了存储过程的输出，可以通过OUTPUT参数将值返回至调用程序。任务5.1 创建和管理存储

12、过程【体验活动】1.打开“学生成绩管理”数据库，创建“查询优秀成绩”和“查询不及格成绩”两个存储过程,分别从“学生成绩”表中查询成绩优秀（大于85分）和不及格成绩（小于60分）学生的学号、课程号、成绩。2. 打开“学生成绩管理”数据库，创建名为“按专业查询”的带参数存储过程，参数名为“所学专业”，“所学专业”参数指定专业，例如“计算机应用”，则存储过程根据参数，查询“学生信息”表中“计算机应用”专业学生的信息。任务5.2 创建和管理触发器本任务主要学习SQL Server触发器的相关知识，具体包括了触发器的概念、触发器的类型、以及使用对象资源管理器和T-SQL命令管理触发器。1.认识触发器对象

13、类型。2.掌握使用对象资源管理器管理触发器。3.掌握使用T-SQL命令管理触发器。任务5.2 创建和管理触发器活动 5.2.1 在对象资源管理器中创建INSERT触发器活动 5.2.2 利用T-SQL命令创建DELETE触发器活动 5.2.3 创建UPDATE触发器活动 5.2.4 对象资源管理器修改、禁用、启用、删除触发器活动 5.2.5 利用T-SQL命令修改、禁用、启用、删除触发器活动 5.2.1 在对象资源管理器中创建INSERT触发器 INSERT触发器就是指当在表中插入记录时，该触发器就会自动触发。我们可以利用INSERT触发器自动执行这个特点，将需要在插入记录时执行的命令写在触发

14、器中。案例1 利用对象资源管理器在图书信息管理数据库的“出版社”表中创建“添加新出版社”触发器，当为“出版社”表插入一条新记录时候触发，触发器提示“插入新出版社的信息！”活动 5.2.1 在对象资源管理器中创建INSERT触发器 【实现步骤】第1步：在对象资源管理器中依次展开“数据库”“图书信息管理”“表”“出版社”，右键单击 “触发器”，选择“新建触发器”，打开触发器编辑模板进行如下代码的编写：USE 图书信息管理GOCREATE TRIGGER 添加新出版社 ON 出版社 AFTER INSERT -AFTER INSERT为插入记录后触发AS BEGINPRINT(插入新出版社的信息！)

15、; -输出提示信息ENDGO活动 5.2.1 在对象资源管理器中创建INSERT触发器第2步：单击按钮。这样就给“出版社”表创建了INSERT触发器。当向“出版社”表插入数据时将激发触发器，并给出提示信息，同时数据插入表中。第3步：验证该案例。在查询编辑器中运行如下语句：INSERT INTO 出版社 VALUES(汕头大学出版社,8885454,汕头市)说明：由于出版社号为标识列，其值由系统自动生成，故在VALUES值列中不出现该字段的值。执行上述操作后将返回提示信息“插入新出版社的信息！”，同时将新记录插入“出版社”表中。可通过以下代码查看，发现记录已插入表中。活动 5.2.2 利用T-S

16、QL命令创建DELETE触发器 除了在对象资源管理器中创建触发器，还可以利用T-SQL命令来创建触发器。下面利用T-SQL命令创建DELETE触发器。案例案例2 利用T-SQL命令在图书信息管理数据库的“出版社”表中创建“删除出版社”触发器，当删除“出版社”表中的记录时，触发器将保护数据，中止删除“出版社”操作并提示。【实现步骤】（1）单击 打开查询编辑器，编写代码如下： 脚本中 “INSTEAD OF DELETE”表示当删除记录时触发器触发,并取消删除操作； “PRINT(不允许对出版社进行删除！操作取消。)”显示“不允许对出版社进行删除！操作取消。”的提示信息。USE 图书信息管理GOC

17、REATE TRIGGER 删除出版社 ON 出版社 INSTEAD OF DELETE -INSTEAD OF DELETE 不执行删除操作AS PRINT(不允许对出版社进行删除！操作取消。)GO活动 5.2.3 创建UPDATE触发器 在带有UPDATE触发器的表上执行UPDATE语句时，将触发UPDATE触发器。案例案例3 利用T-SQL命令在图书信息管理数据库的“出版社”表中创建“更新出版社”触发器，当对表执行“更新”操作时候触发，不允许用户对“出版社”记录进行修改，并提示。【实现步骤】打开查询编辑器编写代码如下： 执行该代码后，将在“出版社”表创建了一个UPDATE触发器，当对“出

18、版社”表中的数据进行修改时将激发UPDATE触发器，不允许对数据进行修改。USE 图书信息管理GOCREATE TRIGGER 更新出版社 ON 出版社 INSTEAD OF UPDATE -INSTEAD OF UPDATE 不执行更新操作AS PRINT(出版社信息不允许修改！操作被取消。)GO活动 5.2.3 创建UPDATE触发器1.触发器的概念触发器（Trigger）是一种特殊类型的存储过程，由若干条T-SQL语句组成，可以实现一定的功能。与存储过程不同的是，触发器的执行不需要通过调用来完成，而是当用户对数据库、数据库对象、表中的数据执行某种操作后自动触发的。一般触发器的常用触发事件

19、包括记录更新UPDATE、添加记录INSERT、删除记录DELETE，对应的触发器会在事件发生时执行，从而确保数据的处理符合所要求的规则。触发器是一种功能强大的工具，可以扩展SQL Server约束、默认值和规则的完整性逻辑检查，实现更为复杂的数据完整性约束。活动 5.2.3 创建UPDATE触发器2.触发器的优点：(1) 触发器可以实现更为复杂的数据完整性约束。(2)触发器可以实现数据库中多个表的级联修改。(3)触发器可以比较数据库修改前后的数据状态，并根据其差异采取相应的措施。(4)触发器可以创建自定义的提示信息；自定义的提示信息可以指示一个动作的状态，这在应用程序的开发中具有重要的实用价

20、值。通过使用触发器，用户可以捕获破坏数据完整性的操作，并返回预先自定义的错误信息。活动 5.2.3 创建UPDATE触发器3. 触发器的类型触发器可以分为AFTER触发器和 INSTEAD OF触发器两种。（1）AFTER触发器这种类型的触发器在数据变动（INSERT、DELETE、UPDATE操作）完成以后才被触发。AFTER触发器只能在表上定义，每个表可以创建多个AFTER触发器。（2）INSTEAD OF触发器这种类型的触发器在数据变动之前被触发，并取代变动数据的操作（INSERT、DELETE、UPDATE操作），即变动数据的操作并不执行，而是执行触发器定义的操作。INSTEAD OF

21、触发器可以在表或视图上定义，每个表或视图的每个INSERT、DELETE、UPDATE操作只能定义一个INSTEAD OF触发器。活动 5.2.3 创建UPDATE触发器4.触发器的创建创建触发器的语句格式如下：CREATE TRIGGER 触发器名 ON 表名|视图名 FOR|AFTER|INSTEAD OF INSERT|UPDATE|DELETEAS SQL语句块GO活动 5.2.3 创建UPDATE触发器(1)FOR | AFTERAFTER 指定触发器仅在触发 SQL 语句中指定的所有操作都已成功执行时才被触发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。如果仅指定

22、 FOR 关键字，则 AFTER 为默认值。也就是说，FOR = AFTER。(2)INSTEAD OF INSERT| UPDATE|DELETE指定执行触发器的SQL语句块而不执行SQL的UPDATE、DELETE、INSERT操作，因此其优先级高于触发语句的操作。活动 5.2.4 在对象资源管理器中修改、禁用、启用、删除触发器 触发器创建好后，我们还可以对其进行操作管理，主要包括对触发器的修改、禁用、启用、删除等工作。 案例4 使用对象资源管理器修改、禁用、启用、删除“添加出版社”触发器【实现步骤】 在对象资源管理器中, 依次展开 “数据库”“图书信息管理”“表”“出版社”“触发器”，右键单击“添加出版社”触发器，如图5.2.3所示。分别选择“修改”、“禁用”、“删除”命令，可以对触发器进行修改、禁用和删除处理。对已禁用的触发器，可在右键菜单中选择“启用”命令则回复到默认的启用状态。活动 5.2.5 利用T-SQL命令修改、禁用、启用、删除触发器案例5 使用T-SQL命令修改、禁用、启用、删除“删除出版社”触发器【实现步骤】1.修改“删除出版社”触发器，在提示输出语句中加入“注意：”字样。打开查询编辑器输入如下代码： 单击