任务八学生选课管理数据库的存储过程、触发器及游标的应用

1、任务八任务八 学生选课管理数据库的存储过程、学生选课管理数据库的存储过程、 触发器及游标的应用触发器及游标的应用【情境描述情境描述】 学院信息中心使用一个应用程序来管理学生选课信息，这个应用程序要求从下拉列表框中选择学生学号，然后检索指定学生的“姓名”、“性别”、所选“课程名”和“成绩”，并在对应的控件中显示出来；当利用应用程序向“专业信息表”中增加记录时，若插入了在“系部信息表”中没有的系部编号，将提示用户“系部编号不存在，无法插入记录”，否则提示“记录成功插入”的信息。为了实现以上两个功能，小王为该数据库创建一个存储过程，在应用程序中调用该存储过程，即可方便地在检索对应学生的选课信息。同时

2、小王在“专业信息表”上创建了一个触发器，当向专业信息表插入记录时，提示相关的信息。【任务分解任务分解】 从上述的情境描述中可见，存储过程和触发器都是一组完成特定功能的T-SQL语句集。本单元主要介绍存储过程、触发器及游标等知识，完成存储过程、触发器及游标在学生选课管理数据库的应用，对该任务进行分解，共包括3个子任务：使用存储过程维护学生选课管理系统的基本信息使用触发器维护学生选课管理系统的业务逻辑使用游标处理学生选课管理系统中的数据任务八任务八 学生选课管理数据库的存储过程、学生选课管理数据库的存储过程、 触发器及游标的应用触发器及游标的应用【知识目标知识目标】了解存储过程的概念、类型掌握创建

3、与执行存储过程的方法了解触发器的概念、类型掌握触发器的创建及使用方法掌握游标的使用方法【能力目标能力目标】能够熟练创建、执行、查看、修改和删除存储过程能够熟练设计、查看、修改和删除触发器能够进行游标的声明、打开、读取、关闭及删除任务八任务八 学生选课管理数据库的存储过程、学生选课管理数据库的存储过程、 触发器及游标的应用触发器及游标的应用 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息子任务子任务8.1 使用存储过程维护学生选课管理系统的基本信息使用存储过程维护学生选课管理系统的基本信息8.1.1 存储过程概述存储过程概述 使用T-S

4、QL语言进行编程有两种方法，一是在本地存储T-SQL程序，执行时将程序中的语句发送到SQL Server,并对数据进行处理；二是将部分T-SQL语言编写的程序作为存储过程存储在SQL Server中，通过调用存储过程来对数据进行处理。在实际应用中，更多的是使用第二种方法，也就是在SQL Server中使用存储过程，而不是在客户机上调用T-SQL编写一段程序。1.认识存储过程认识存储过程 存储过程是一种数据库对象，是一组为了完成特定功能的T-SQL语句的集合，经编译后存储在数据库服务器中，可以接受参数并返回状态值和参数值。存储过程可以由应用程序通过一个调用来执行，它是封装重复性工作的一种有效方法

5、，使用存储过程能够显著提高应用程序的处理能力，并降低编写及维护数据库应用程序的难度。数据库开发人员及管理人员通过编写存储过程来运行经常执行的管理任务，或者应用复杂的业务规则。待删除的文件夹必须为空待删除的文件夹必须为空 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息2.存储过程的优点存储过程的优点 （1）执行速度快。一般的T-SQL语句每次执行时都需要进行编译和优化，而存储过程是经过预编译的，在创建时经过了语法检查和性能优化，在执行的时候就不要重复这些步骤，因此使用存储过程可以提高执行速度。 （2）可以提高数据的安全性。系统管理员可以

6、只给用户授予访问存储过程的权限，而不授予访问存储过程所涉及到的表或视图的权限，这样使用户只能通过存储过程来操作数据库中的数据，而不能直接操作有关的表，从而保证数据库中数据的安全性。 （3）存储过程可以降低网络负载。存储过程中包含大量的T-SQL语句，但是它以一个独立的单元存放在服务器上。调用存储过程时，只需传递执行存储过程的调用命令就能够将执行结果返回调用过程或批处理，减少了网络上数据的传输出。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息3.存储过程的分类存储过程的分类 存储过程可以分为系统存储过程、用户定义存储过程和扩展存储过程

7、等。 （1）系统存储过程。由系统自动创建，存储在master数据库中，前缀为sp_。系统存储过程完成的功能主要是从系统表中获取信息，系统管理员可以通过系统存储过程完成复杂的SQL Server的管理工作。 （2）用户定义存储过程。用户定义的存储过程是用户根据需要，为完成特定功能，在自己的应用数据库中自行创建的存储过程。 （3）扩展存储过程。扩展存储过程的前缀为xp_，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库（DLL）文件所包含的函数中实现逻辑，扩展了T-SQL功能，而且能够像调用T-SQL过程一样从T-SQL语句调用这些函数。 子任务子任务8.1 使用存储过程维护

8、学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息8.1.2 创建存储过程创建存储过程存储过程是封装重复性工作的一种有效方法，使用存储过程可以使数据库的管理工作变得更加容易，可以通过对象资源管理器及T-SQL语句两种方法进行创建。1.使用对象资源管理器创建存储过程使用对象资源管理器创建存储过程的步骤如下：第一步：打开对象资源管理器。第二步：展开相应的服务器展开“数据库” 展开要建立存储过程的数据库展开“开编程性” 右击“存储过程”，在弹出的快捷菜单中单击“新建存储过程”，如图8-1所示。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选

9、课管理系统的基本信息。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-1 单击“新建存储过程” 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息第三步：在“新建存储过程”对话框的“文本”框中输入创建存储过程的T-SQL语句。第四步：单击工具栏上的“分析”按钮，以检查存储过程是否存在语法问题。如果语法完全正确，单击“执行(X）”按钮后，即完成存储过程的创建,如图8-2所示。图8-2 单击“分析”按钮2.使用使用CREATE PROCEDURE语句创建存储过程语句创建存储过程

10、语法格式如下：CREATE PROCEDURE 存储过程名输入参数1 数据类型,输入参数2 数据类型,输出参数1 数据类型 OUTPUT,输出参数2 数据类型 OUTPUT, WITH ENCRYPTION ASSQL语句参数说明：参数说明：OUTPUT：表示该参数是返回参数，使用“OUTPUT”可以将值返回给过程的调用方。ENCRYTPION：将存储过程进行加密。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息（1）创建简单存储过程）创建简单存储过程【例例8-1】使用使用CREATE PROCEDURE语句创建存储过程语句创建存储过

11、程“sel_学生选课学生选课”，该存储过程从，该存储过程从“学生信息表学生信息表”、“课程课程信息表信息表”、“选课信息表选课信息表”中检索所有学生的中检索所有学生的“姓名姓名”、“性别性别”、所选、所选“课程名课程名”和和“成绩成绩”。USE 学生选课管理-如果存在“sel_学生选课”存储过程，则删除IF EXISTS(SELECT name FROM sysobjects WHERE name=sel_学生选课 AND type=P)DROP PROCEDURE sel_学生选课GO 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息

12、-建立存储过程“sel_学生选课”CREATE PROCEDURE sel_学生选课AS SELECT 姓名,性别,课程名,成绩 FROM 学生信息表 A INNER JOIN 选课信息表 B ON A.学号=B.学号 INNER JOIN 课程信息表 C ON B.课程编号=C.课程编号GO-执行存储过程“sel_学生选课”EXEC sel_学生选课 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息 带简单参数的存储过程带简单参数的存储过程 【例例8-2】使用使用CREATE PROCEDURE语句创建存储过程语句创建存储过程“SEL

13、_指定学生选课指定学生选课”，该存储过程从，该存储过程从“学生信息表学生信息表”、“课课程信息表程信息表”、“选课信息表选课信息表”中检索指定学生的中检索指定学生的“姓名姓名”、“性别性别”、所选、所选“课程名课程名”和和“成绩成绩”，要求将学号通过一个输，要求将学号通过一个输入参数传递给存储过程。入参数传递给存储过程。USE 学生选课管理学生选课管理-如果存在“sel_指定学生选课”存储过程，则删除IF EXISTS(SELECT name FROM sysobjects WHERE name=sel_指定学生选课 AND type=P)DROP PROCEDURE sel_指定学生选课GO

14、-建立存储过程“sel_指定学生选课”CREATE PROCEDURE sel_指定学生选课 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息xh char(8)AS SELECT 姓名,性别,课程名,成绩 FROM 学生信息表 A INNER JOIN 选课信息表 B ON A.学号=B.学号 INNER JOIN 课程信息表 C ON B.课程编号=C.课程编号 WHERE A.学号=xhGO-执行存储过程“sel_指定学生选课”EXEC sel_指定学生选课 0301001 子任务子任务8.1 使用存储过程维护学生使用存储过程维护

15、学生 选课管理系统的基本信息选课管理系统的基本信息 使用输出参数使用输出参数 输出参数用于把返回值赋予变量并传给调用它的存储过程或应用程序。声明输出参数时需要在声明参数的后面加上OUTPUT，以表明此参数为输出参数。 【例例8-3】创建存储过程创建存储过程“course_select_count”，过程将返回某，过程将返回某课程的选课学生数量。课程的选课学生数量。USE 学生选课管理-如果存在“course_select_count”存储过程，则删除IF EXISTS(SELECT name FROM sysobjects WHERE name=course_select_count AND

16、type=P)DROP PROCEDURE course_select_countGO 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息CREATE PROCEDURE course_select_countcourse_id char(10),course_nums SMALLINT OUTPUTASSET course_nums=(SELECT count(*) FROM 选课信息表WHERE 课程编号=course_id)PRINT course_numsGO-执行存储过程，返回课程编号为“130201”课程的选课学生数量DECL

17、ARE cid char(10),cnums SMALLINTSET cid=130201EXEC course_select_count cid, cnums 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息运行结果如图8-3所示。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-3 执行带输出参数的存储过程 使用带有通配符参数的存储过程使用带有通配符参数的存储过程 【例例8-4】使用使用CREATE PROCEDURE语句创建存储过程语句创建存储过程“SEL_选课选课”

18、，该存储过程从，该存储过程从 “课程信息表课程信息表”、“选课信息选课信息表表”中检索被选课程名中包含中检索被选课程名中包含“程序程序”的的“课程名称课程名称”，要求，要求将含有将含有“程序程序”的课程名，通过参数传递给存储过程。的课程名，通过参数传递给存储过程。USE 学生选课管理-如果存在“sel_学生选课”存储过程，则删除IF EXISTS(SELECT name FROM sysobjects WHERE name=SEL_选课 AND type=P)DROP PROCEDURE SEL_选课GO 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课

19、管理系统的基本信息-建立存储过程“SEL_选课”CREATE PROCEDURE SEL_选课course_name varchar(20)=%程序%AS SELECT DISTINCT 课程名 FROM 课程信息表 A INNER JOIN 选课信息表 B ON A.课程编号=B.课程编号 WHERE 课程名 like course_nameGO-执行存储过程“SEL_选课”EXEC SEL_选课【练一练】使用CREATE PROCEDURE语句创建存储过程“SEL_班级学生”，该存储过程根据“班级名称”从“学生信息表”、“班级信息表”中检索指定班级的学生“学号”、“姓名”和“性别”信息，要

20、求将“班级名称”通过参数传递给存储过程。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息8.1.3 执行存储过程执行存储过程存储过程创建完成后，可以使用EXECUTE语句来执行存储过程。语法格式如下：EXECUTE 存储过程名 参数值,【说明】若EXECUTE语句是批的第一条语句时，可以省略EXECUTE。【例例8-5】执行例执行例8-1所创的存储过程所创的存储过程“sel_学生选课学生选课”，结果如图，结果如图8-4所示所示。USE 学生选课管理GOEXEC sel_学生选课 子任务子任务8.1 使用存储过程维护学生使用存储过程维护

21、学生 选课管理系统的基本信息选课管理系统的基本信息 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-4 执行存储过程sel_学生选课 【例例8-6】使用使用EXECUTE命令传递参数，执行例命令传递参数，执行例8-2创建的存储过创建的存储过程程“SEL_指定学生选课指定学生选课”。USE 学生选课管理GOEXEC sel_指定学生选课 0602199【练一练】执行存储过程SEL_班级学生，并观察结果是否正确。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息8.1.4 管理

22、存储过程管理存储过程 存储过程被创建以后，可以对其进行管理和维护，包括查看存储过程、修改存储过程、重命名存储过程和删除存储过程。 1.修改存储过程 （1）使用对象资源管理器修改存储过程 第一步：使用对象资源管理器修改存储过程的步骤主要是：打开资源管理器依次展开存储过程所在的数据库可编程性存储过程。 第二步：在要修改的存储过程处单击鼠标右键，在快捷菜单中单击“修改”命令。 第三步：在“修改存储过程”对话框的“文本”框中修改存储过程源程序，如图8-5所示。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息 子任务子任务8.1 使用存储过程维

23、护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-5 使用对象资源管理器修改存储过程（2）使用）使用ALTER PROCEDURE语句修改存储过程语句修改存储过程语法格式如下：ALTER PROCEDURE 存储过程名输入参数1 数据类型,输入参数2 数据类型,输出参数1 数据类型 OUTPUT,输出参数2 数据类型 OUTPUT, WITH ENCRYPTION ASSQL语句相关参数的含义参见CREATE PROCEDURE语句。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息【例例8-7】修改修改【例

24、例8-1】建立的存储过程建立的存储过程“sel_学生选课学生选课”，要求对此存储过，要求对此存储过程进行加密，其他要求不变。程进行加密，其他要求不变。USE 学生选课管理GOALTER PROCEDURE sel_学生选课WITH ENCRYPTIONAS SELECT 姓名,性别,课程名,成绩 FROM 学生信息表 A INNER JOIN 选课信息表 B ON A.学号=B.学号 INNER JOIN 课程信息表 C ON B.课程编号=C.课程编号GO-执行存储过程EXEC sel_学生选课【练一练】修改存储过程course_select_count，过程将返回某学生的选课数量。执行所建

25、立的存储过程，并观察结果。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息2.查看存储过程查看存储过程 （1）使用对象资源管理器查看存储过程的相关信息 使用对象资源管理器查看存储过程相关信息的主要操作步骤： 第一步：启动SQL Server Management Studio，在“对象资源管理器”窗口中，依次展开存储过程所在的数据库可编程性存储过程，右键单击需要查看的存储过程，在弹出的快捷菜单中单击“属性”，如图8-6所示。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息 子

26、任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-6 存储过程属性 第二步：查看存储过程属性，如图8-7所示。“选择页”项中的“常规”、“权限”和“扩展属性”选项功能如下：在“常规”选项中，可以查看该存储过程的创建日期、属于哪个数据库和数据库用户等信息。在“权限”选项中，可以查看该存储过程的名称，并且可以为该存储过程添加用户，授予其权限。在“扩展属性”选项中，可以向数据库对象添加自定义属性。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息 子任务子任务8.1 使用存储过程维护

27、学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-7 查看存储过程属性（2）使用系统存储过程查看存储过程信息）使用系统存储过程查看存储过程信息查看存储过程的定义使用sp_helptext查看存储过程的定义，语法格式如下：sp_helptext 存储过程名查看存储过程的一般信息使用sp_help查看存储过程的一般信息，主要包括存储过程的名称、拥有者、类型和创建时间等，语法格式如下：sp_help 存储过程名查看存储过程的相关性使用sp_depends查看存储过程的相关性，语法格式如下：sp_depends存储过程名 子任务子任务8.1 使用存储过程维护学生使用存储过程维

28、护学生 选课管理系统的基本信息选课管理系统的基本信息【例例8-8】使用使用sp_helptext查看学生选课管理数据库中存储过程查看学生选课管理数据库中存储过程“sel_学生选课学生选课”的定义，结果如图的定义，结果如图8-8所示。所示。USE 学生选课管理GOEXEC sp_helptext sel_学生选课 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-8 使用sp_helptext查看存储过程的定义【例例8-9】使用使用sp_help查看学生选课管理数据库中存储过程查看学生选课管理数据库中存储过程“sel_学生选学生选课课

29、”的一般信息，结果如图的一般信息，结果如图8-9所示。所示。USE 学生选课管理GOEXEC sp_help sel_学生选课 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-9 使用sp_help查看存储过程的一般信息【例例8-10】使用使用sp_depends查看学生选课管理数据库中存储过程查看学生选课管理数据库中存储过程“sel_学生选课学生选课”的相关性，结果如图的相关性，结果如图8-10所示。所示。USE 学生选课管理GOEXEC sp_depends sel_学生选课 子任务子任务8.1 使用存储过程维护学生使用存储过

30、程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-10 使用sp_depends查看存储过程的相关性3.重命名存储过程重命名存储过程 （1）使用对象资源管理器重命名存储过程 使用对象资源管理器重命名存储过程的操作，与在Windows中修改文件名的操作相似，主要步骤如下： 第一步：启动SQL Server Management Studio，在“对象资源管理器”窗口中，依次展开存储过程所在的数据库可编程性存储过程，右键单击需要重命名的存储过程，在弹出的快捷菜单中单击“重命名”，如图8-11所示。 第二步：输入新的存储过程名。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学

31、生 选课管理系统的基本信息选课管理系统的基本信息 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-11 在弹出的快捷菜单中单击“重命名”（2）使用系统存储过程重命名存储过程）使用系统存储过程重命名存储过程语法格式如下：sp_rename 存储过程原名，存储过程新名【例8-11】将存储过程“course_select_count”更改为“csc”。USE 学生选课管理GOsp_rename course_select_count,csc 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统

32、的基本信息4.删除存储过程删除存储过程 （1）使用对象资源管理器删除存储过程 使用对象资源管理器删除存储过程的主要操作步骤： 第一步：启动SQL Server Management Studio，在“对象资源管理器”窗口中，依次展开存储过程所在的数据库可编程性存储过程，右键单击需要删除的存储过程，在弹出的快捷菜单中单击“删除”，如图8-12所示。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-12 在弹出的快捷菜单中单击“

33、删除” 第二步：在弹出的“删除对象”对话框中，单击“确定”按钮，该存储过程被删除，如图8-13所示。 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息图8-13 删除对象 子任务子任务8.1 使用存储过程维护学生使用存储过程维护学生 选课管理系统的基本信息选课管理系统的基本信息（2）使用）使用DROP PROCEDURE语句删除存储过程语句删除存储过程语法格式如下：DROP PROC | PROCEDURE 存储过程名 ,.n 【例8-12】使用T-SQL语句删除学生选课管理数据库中的存储过程“course_select_count”。

34、USE 学生选课管理GODROP PROCEDURE course_select_count子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑 8.2.1 触发器概述触发器概述 触发器是一种特殊类型的存储过程，是SQL Server为保证数据完整性、确保系统正常工作而设置的一种技术。当触发器所保护的数据发生变化时，触发器就会自动运行，以保证数据的完整性与正确性。例如，有时需要对表中的数据进行修改的操作包括INSERT、DELETE、UPDATE操作，如果对某个表的INSERT、DELETE、UPDATE操作定义了触发器，则对该表执行这些操作时，

35、相应的触发器会被自动执行。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑 1.触发器的类型触发器的类型 SQL Server包括两大类触发器：DML触发器和DDL触发器。 DML触发器是当数据库服务器中对表或视图发出INSERT、UPDATE或DELETE语句等数据操作语言(DML)事件时要执行的操作。该种类型触发器用于在数据被修改时强制执行业务规则，以及扩展Microsoft SQL Server 2008约束、默认值和规则的完整性检查逻辑。 DDL 触发器同常规触发器一样，它将激发存储过程以响应事件。与DML触发器不同的是，DDL触发器

36、不会为响应针对表或视图的INSERT、UPDATE或DELETE语句而激发，只是为响应以CREATE、ALTER和DROP 开头的数据定义语言(DDL)语句而激发。DDL触发器能够用于管理任务，例如审核和控制数据库操作。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑2.触发器的优点触发器的优点（1）DML触发器可通过数据库中的相关表实现级联更改。（2）DML触发器能够强制比CHECK定义的约束更为复杂的约束。（3）DML触发器能够引用其他表中的列，而CHECK约束却不能。（4）DML触发器能够评估数据修改前与修改后的表状态，并根据其差异采取

37、对策。（5）一个表中的多个同类 DML 触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的操作来响应同一个修改语句。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑8.2.2 创建触发器创建触发器 在创建触发器前，需要了解两个与触发器密切相关的专用临时表：Inserted表和Deleted表。系统为每个触发器创建专用临时表，其表结构与触发器作用的表结构相同。专用临时表被放在内存中，由系统维护，用户可以对其查询，不能对其修改。触发器执行完成后，与该触发器相关的临时表被删除。 当向表中插入数据时，若该表存在INSERT触发器

38、，触发器将被触发而自动执行。此时，系统将自动创建一个与触发器表具有相同结构的Insert临时表，新的记录被添加到触发器表和Inserted表中。Inserted表中保存了所插入记录的副本，方便用户查找当前的插入数据。 当删除表中数据时，若该表存在DELETE触发器，触发器将被触发而自动执行。此时，系统将自动创建一个与触发器表具有相同表结构的Deleted临时表，用来保存触发器表中被删除的记录，方便用户查找当前的删除的数据。 当修改表中的数据时，若该表存在UPDATE触发器，触发器将被触发而自动执行。相当于删除一条旧的记录，增加一条新的记录，其中被删除的记录（触发器表中的原记录）放在Delete

39、d表中，同时增加的新记录（修改后的记录）放在Inserted表中。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑1.使用对象资源管理器创建触发器 在对象资源管理器中，创建触发器的步骤如下： 第一步：启动SQL Server Management Studio，在“对象资源管理器”窗口中，依次展开数据库触发器所在的数据库（如学生选课管理）表展开需要建立触发器的表（如班级信息表），右键单击触发器，在弹出的快捷菜单中选择“新建触发器”命令，如图8-14所示。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统

40、的业务逻辑子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑图8-14 单击“新建触发器” 第二步：打开了“创建触发器模板”，在模板中输入创建触发器的文本后，单击工具栏上的“执行”按钮，完成触发器的创建。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑2.使用使用CREATE TRIGGER语句创建触发器语句创建触发器语法格式如下：CREATE TRIGGER 触发器名ON 表名|视图名 WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INS

41、ERT , UPDATE AS sql语句 参数说明：WITH ENCRYPTION：表示对CREATE TRIGGER语句的文本进行加密。AFTER：指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被激发。若仅指定FOR关键字，则AFTER为默认值。INSTEAD OF ：指定DML触发器是“代替”SQL语句执行的，所以其优先级高于触发语句的操作。 DELETE , INSERT , UPDATE ：指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑3.触发器的实现

42、触发器的实现 （1）AFTER触发器 如果为表的INSERT、DELETE、UPADATE操作定义了AFETER触发器，则在完成INSERT、DELETE、UPADATE操作后，相应的AFTER触发器被击活，这种类型的触发器只能定义在表上，每个表可以创建多个AFTER触发器。 INSERT触发器 当向触发器表插入数据时，INSERT触发器被激活；新数据行被插入到触发器表和临时表Inserted中；执行触发器中定义的语句。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑【例例8-13】在学生选课管理数据库的在学生选课管理数据库的“专业信息表专业

43、信息表”中建立一个名为中建立一个名为insert_department的触发器，当向的触发器，当向“专业信息表专业信息表”中增加记录时，若插中增加记录时，若插入了在入了在“系部信息表系部信息表”中没有的系部编号，将提示用户中没有的系部编号，将提示用户“系部编号不存系部编号不存在，无法插入记录在，无法插入记录”，否则提示，否则提示“记录成功插入记录成功插入”的信息。的信息。USE 学生选课管理GOIF EXISTS(SELECT name FROM sysobjects WHERE name=insert_department AND type=TR) DROP TRIGGER insert_d

44、epartmentGOCREATE TRIGGER insert_department ON 专业信息表FOR insertASDECLARE dept char(2)SELECT dept=系部信息表.系部编号子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑FROM 系部信息表,insertedWHERE 系部信息表.系部编号=inserted.系部编号IF dept PRINT 记录成功插入ELSE BEGIN PRINT 系部编号不存在，无法插入记录 ROLLBACK TRANSACTION ENDGO【练一练】在学生选课管理数据库的“

45、班级信息表”中建立一个名为insert_class的触发器，当向“班级信息表”中增加记录时，若插入了在“专业信息表”中没有的专业编号，将提示用户“专业编号不存在，无法插入记录”，否则提示“记录成功插入”的信息。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑DELETE触发器触发器 对触发器表执行DELETE操作时，DELETE触发器被激活；删除触发器表中的记录，并将被删除的记录放入临时表Deleted表中；执行触发器中定义的语句。 【例例8-14】在学生选课管理数据库的在学生选课管理数据库的“专业信息表专业信息表”中建立中建立一个名为一个名

46、为delete_spec的触发器，当删除的触发器，当删除“专业信息表专业信息表”中的中的记录时，若记录时，若“班级信息表班级信息表”中引用了该记录的系部编号，中引用了该记录的系部编号，将提示用户将提示用户“该专业被班级信息表引用，不能删除该条记该专业被班级信息表引用，不能删除该条记录录”，否则提示，否则提示“记录成功删除记录成功删除”的信息。的信息。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑USE 学生选课管理GOIF EXISTS(SELECT name FROM sysobjects WHERE name=delete_spec A

47、ND type=TR) DROP TRIGGER delete_specGOCREATE TRIGGER delete_spec ON 专业信息表FOR deleteASIF(SELECT COUNT(*) FROM 班级信息表 a INNER JOIN deleted b ON a.专业编号=b.专业编号)0 BEGIN PRINT 该专业被班级信息表引用，不能删除该条记录 ROLLBACK TRANSACTION END ELSE PRINT 记录成功删除GO子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑UPDATE触发器触发器 对触发

48、器表执行UPDATE操作时，UPDATE触发器被激活；更新触发器表中的记录，并将原始记录放入临时表Deleted中，将更新后的记录放入临时表Inserted表中；执行触发器中定义的语句。 【例例8-15】在学生选课管理数据库的在学生选课管理数据库的“选课信息表选课信息表”中建立一个名为中建立一个名为update_trig的触发器，当更新的触发器，当更新“选课信息表选课信息表”中的中的“成绩成绩”字段时，自字段时，自动依据动依据“课程信息表课程信息表”为学分赋值或清零。为学分赋值或清零。USE 学生选课管理GOCREATE TRIGGER update_trigON 选课信息表FOR UPDAT

49、EASDECLARE score int,sno char(7),courseno char(10)子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑IF UPDATE(成绩)BEGIN SELECT score=inserted.成绩,sno=inserted.学号,courseno=inserted.课程编号 FROM inserted,选课信息表 WHERE 选课信息表.学号=inserted.学号 AND 选课信息表.课程编号=inserted.课程编号 IF(score=60) UPDATE 选课信息表 SET 学分=(SELECT

50、学分 FROM 课程信息表 WHERE 课程编号=courseno) WHERE 课程编号=courseno AND 学号=sno ELSE UPDATE 选课信息表 SET 学分=0 WHERE 课程编号=courseno AND 学号=snoEND子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑（2）INSTEAD OF触发器触发器 当为表或视图的INSERT、DELETE、UPDATE操作定义了INSTEAD OF触发器，则当执行INSERT、DELETE、UPDATE语句时，相应的INSTEAD OF触发器被激活，且触发器定义的操作会

51、取代激活触发器的INSERT、DELETE、UPDATE等操作，即激活触发器的操作并不会被执行。 【例例8-16】在学生选课管理数据库的在学生选课管理数据库的“选课信息表选课信息表”中建立一个名为中建立一个名为ins_sel_cou_trg的触发器，当向的触发器，当向“选课信息表选课信息表”中插入记录时，中插入记录时，“学号学号”、“课程编号课程编号”、“教师编号教师编号”、“专业编号专业编号”字段不能为空值，字段不能为空值，“成绩成绩”、“学学分分”字段必须为空值。若不满足以上条件，则不允许向字段必须为空值。若不满足以上条件，则不允许向“选课信息表选课信息表”中插入中插入记录。记录。CREA

52、TE TRIGGER ins_sel_cou_trg ON 选课信息表 INSTEAD OF INSERTAS IF(SELECT 成绩 FROM inserted) IS NOT NULL OR (SELECT 学分 FROM inserted) IS NOT NULL)子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑 OR (SELECT 学号 FROM inserted) IS NULL OR (SELECT 课程编号 FROM inserted) IS NULL OR (SELECT 教师编号 FROM inserted) IS NU

53、LL OR (SELECT 专业编号 FROM inserted) IS NULL) PRINT 出错了，不能向选课信息表中插入记录！因为成绩、学分必须为空值，“学号”、“课程编号”、“教师编号”、“专业编号”字段不能为空值 ELSE INSERT INTO 选课信息表 SELECT * FROM insertedGO-测试ins_sel_cou_trg触发器PRINT 插入一个选课信息INSERT INTO 选课信息表(学号,课程编号,成绩,教师编号,专业编号) VALUES(0602101,060317,80,060301,620405)PRINT 再插入一个选课信息INSERT INTO

54、 选课信息表(学号,课程编号,教师编号,专业编号) VALUES(0602101,060317,060301,620405)子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑【例例8-17】在学生选课管理数据库的在学生选课管理数据库的“课程信息表课程信息表”中建立一个中建立一个名为名为del_cou_trg的触发器，当删除的触发器，当删除“课程信息表课程信息表”中的某个记录中的某个记录时，如果时，如果“选课信息表选课信息表”中存在与删除的课程编号相同的记录时中存在与删除的课程编号相同的记录时，则不允许删除该记录。，则不允许删除该记录。USE 学

55、生选课管理GOALTER TRIGGER del_cou_trg ON 课程信息表 INSTEAD OF DELETEAS declare course_no char(10) SELECT course_no=课程编号 FROM deleted子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑IF(SELECT DISTINCT 课程编号 FROM 选课信息表 WHERE 课程编号=course_no) IS NULL DELETE 课程信息表 WHERE 课程编号 IN(SELECT 课程编号 FROM deleted) ELSE PRIN

56、T 选课信息表中存在该课程信息，请先删除选课信息表中的数据后，再进行本操作GO-测试del_cou_trg触发器DELETE 课程信息表 WHERE 课程编号=130201子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑WHERE a.学号=stu_noGROUP BY a.学号)GO-调用内联表值函数fn_name_chengjiSELECT * FROM fn_name_chengji(0301002) 子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑8.2.3 管理触发器管理触发器 触

57、发器被创建后，可以对其进行管理和维护，包括修改触发器、查看触发器、重命名触发器及删除触发器。 1.修改触发器 （1）使用对象资源管理器修改触发器的定义 在对象资源管理器中，修改触发器的步骤如下： 第一步：启动SQL Server Management Studio，在“对象资源管理器”窗口中，依次展开数据库触发器所在的数据库（如学生选课管理） 表展开触发器所在的表（如专业信息表） 触发器，右键单击需要修改的触发器，在弹出的快捷菜单中选择“修改”命令，如图8-15所示。 第二步：根据需要修改触发器。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻

58、辑子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑图8-15 单击“修改”（2）使用）使用ALTER TRIGGER语句修改触发器的定义语句修改触发器的定义 使用ALTER TRIGGER语句修改已创建的触发器的语法类似于创建触发器的语法，只要把CREATE改为ALTER即可。但若触发器是用WITH ENCRYPTION创建的，而要使该选项保持启用，必须在ALTER TRIGGER语句中再次指定。语法格式如下：ALTER TRIGGER 触发器名ON 表名|视图名 WITH ENCRYPTION FOR | AFTER | INSTEAD O

59、F INSERT , UPDATE , DELETE AS sql语句 【说明】ALTER TRIGGER的有关参数说明同CREATE TRIGGER。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑2.查看触发器查看触发器 （1）使用对象资源管理器查看触发器信息 第一步：启动SQL Server Management Studio，在“对象资源管理器”窗口中，依次展开存储过程所在的数据库表要查看触发器的表（课程信息表）触发器，右键单击要查看的触发器（del_cou_trg），在弹出的快捷菜单中单击“修改”命令。 第二步：在“修改触发器”对话

60、框的“文本”框中会显示触发器的定义文本，如图8-16所示。子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑子任务子任务8.2 使用触发器维护学生选课管理系统的业务逻辑使用触发器维护学生选课管理系统的业务逻辑图8-16 查看触发器的定义信息（2）使用对象资源管理器查看触发器的依赖关系）使用对象资源管理器查看触发器的依赖关系 启动SQL Server Management Studio，在“对象资源管理器”窗口中，依次展开存储过程所在的数据库表要查看触发器的表（课程信息表）触发器，右键单击要查看的触发器（del_cou_trg），在弹出的快捷菜单