第6章存储过程与触发器

1、中南大学信息科学与工程学院数据库技术与应用数据库技术与应用数据库技术与应用教材编写组数据库技术与应用教材编写组数据库技术与应用本章内容重点难点第六章第六章 存储过程与触发器存储过程与触发器存储过程的基本概念存储过程的基本概念存储过程的特点与作用存储过程的特点与作用触发器的基本概念触发器的基本概念触发器的特点与作用触发器的特点与作用存储过程创建、执行以及参数应用的方法存储过程创建、执行以及参数应用的方法触发器的创建及使用方法触发器的创建及使用方法存储过程的参数应用方法存储过程的参数应用方法2数据库技术与应用问题提出问题提出为什么需要存储过程？存储过程是什么？为什么需要存储过程？存储过程是什么？为

2、什么要触发器？触发器是什么？为什么要触发器？触发器是什么？3数据库技术与应用6.1 6.1 存储过程概述存储过程概述存储过程的特点和类型存储过程的特点和类型存储过程的创建和执行存储过程的创建和执行存储过程参数和执行状态存储过程参数和执行状态存储过程的查看和修改存储过程的查看和修改存储过程的删除存储过程的删除4数据库技术与应用6.1.1 6.1.1 存储过程的特点和类型存储过程的特点和类型存储过程的特点存储过程的特点存储过程是在服务器端运行，执行速度快，方便用户查询存储过程是在服务器端运行，执行速度快，方便用户查询，能有效提高数据使用效率。，能有效提高数据使用效率。封装复杂操作封装复杂操作加快系

3、统运行速度加快系统运行速度实现代码重用实现代码重用增强安全性增强安全性减少网络流量减少网络流量调用方便调用方便5数据库技术与应用6.1.1 6.1.1 存储过程的特点和类型存储过程的特点和类型存储过程的类型存储过程的类型 SQL Server 2008 SQL Server 2008中常用的存储过程类型有中常用的存储过程类型有3种：种：系统存储过程（系统存储过程（sp_sp_）: :由数据库系统自身创建，存储在由数据库系统自身创建，存储在mastermaster数据库中，以数据库中，以“sp_sp_” ” 前缀标识前缀标识用户定义存储过程（本地存储过程）用户定义存储过程（本地存储过程）: :

4、在用户数据库中由用户创建。在用户数据库中由用户创建。 临时存储过程：临时存储过程：可以是局部的，名称以可以是局部的，名称以“# #”开头；也可以是全局的，名开头；也可以是全局的，名称以称以“#”#”开头。开头。扩展存储过程扩展存储过程: :以动态链接库（以动态链接库（DLLDLL）的形式实现。以）的形式实现。以“xp_xp_”为前缀，只能添加到为前缀，只能添加到mastermaster数据库中，使用方法与系统存储过程一样。数据库中，使用方法与系统存储过程一样。6数据库技术与应用6.1.2 6.1.2 存储过程的创建和执行存储过程的创建和执行7数据库技术与应用6.1.2 6.1.2 存储过程的创

5、建和执行存储过程的创建和执行创建存储过程实际是对存储过程进行定义的过程，创建存储过程实际是对存储过程进行定义的过程，主要包含：主要包含：存储过程名称及其参数的说明和存储过程的主体存储过程名称及其参数的说明和存储过程的主体（包含执行过程操作的（包含执行过程操作的 T-SQL T-SQL 语句）两部分。语句）两部分。可以使用可以使用3 3种方法创建存储过程：种方法创建存储过程： 使用图形工具使用图形工具使用向导使用向导使用使用Transact-SQLTransact-SQL语言中的语言中的CREATE PROCEDURECREATE PROCEDURE语句语句 8数据库技术与应用6.1.2 6.1

6、.2 存储过程的创建和执行存储过程的创建和执行使用图形工具创建存储过程使用图形工具创建存储过程9数据库技术与应用6.1.2 6.1.2 存储过程的创建和执行存储过程的创建和执行使用使用CREATE PROCEDURECREATE PROCEDURE语句创建存储过程语句创建存储过程 语法格式如下：语法格式如下： CREATE PROCEDURE schema_name. procedure_name ; number parameter schema_name.data_type VARYING=default OUTPUT WITH RECOMPILE | ENCRYPTION | RECOM

7、PILE, ENCRYPTION FOR REPLICATION AS sql_statement .n10 注意事项：注意事项：只能在本地数据库中创建存储过程只能在本地数据库中创建存储过程 ；可以引用在同一存储过程中创建的对象，只要引用时已经创建了；可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可该对象即可 可以在存储过程内引用临时表，如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在可以在存储过程内引用临时表，如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在；退出该存储过程后，临时表将消失；退出该存储过程后，临时表将消失 根据可用内存的不同，存储过程

8、最大可达根据可用内存的不同，存储过程最大可达128MB 数据库技术与应用6.1.2 6.1.2 存储过程的创建和执行存储过程的创建和执行【例例6.1】在在Student_db数据库中创建一个名为数据库中创建一个名为p_Stu的存的存储过程，它将从表中返回所有学生的姓名、性别、班级、电储过程，它将从表中返回所有学生的姓名、性别、班级、电话。话。 存储过程只能建立在当前数据库上，故需先用存储过程只能建立在当前数据库上，故需先用USE语句来指定数据库语句来指定数据库 USE Student_db Go 存储过程的内容如下：存储过程的内容如下： CREATE PROCEDURE p_Stu AS SE

9、LECT St_ID,St_Sex,Cl_Name,Telephone FROM St_Info 以存储过程是从单个表中提取数据，最终返回了学生的简明信息。以存储过程是从单个表中提取数据，最终返回了学生的简明信息。11数据库技术与应用6.1.2 6.1.2 存储过程的创建和执行存储过程的创建和执行【例例6.2】创建一个带创建一个带SELECT查询语句的名为查询语句的名为“Average_Score”的存储的存储过程。从学生表、课程表、选课表返回每位修课学生的课程平均分。过程。从学生表、课程表、选课表返回每位修课学生的课程平均分。 分析：分析：学生表与选课表通过学生表与选课表通过“St_ID”关

10、联，关联，课程表与选课表通过课程表与选课表通过“C_No”关联，关联，要查到每个学生的修课平均分，需要通过聚集函数要查到每个学生的修课平均分，需要通过聚集函数AVG计算，计算，因为引用了聚集函数，因为引用了聚集函数，SELECT查询中必须使用查询中必须使用GROUP BY分组选项。分组选项。 12 USE Student_db GO CREATE PROCEDURE Average_Score AS SELECT St_Info.St_Name, AVG(S_C_Info.Score) AS AvgScore FROM St_Info, S_C_Info, C_Info WHERE St_In

11、fo.St_ID = S_C_Info.St_ID AND S_C_Info.C_No =C_Info.C_NoGROUP BY St_Info.St_Name GO数据库技术与应用6.1.2 6.1.2 存储过程的创建和执行存储过程的创建和执行使用使用EXECUTEEXECUTE（或（或EXECEXEC）命令执行存储过程）命令执行存储过程 语法格式如下：语法格式如下： EXECUTE return_status= procedure_name ;number|procedure_name_var parameter=value|variable OUTPUT|DEFAULT ,.n WITH

12、 RECOMPILE 注意事项：注意事项：执行存储过程必须具有执行该过程的权限许可执行存储过程必须具有执行该过程的权限许可如果存储过程是批处理中的第一条语句，如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略命令可以省略存储过程的最大大小这存储过程的最大大小这128MB13数据库技术与应用6.1.2 6.1.2 存储过程的执行存储过程的执行例例6.3执行例执行例【例例6.2】所创建的存储过程所创建的存储过程Average_Score。 新建查询，在新建查询，在“查询设计器查询设计器”中输入并运行以下语句：中输入并运行以下语句： USE Student_db GO EXECUTE A

13、verage_Score 输出结果如右图所示。输出结果如右图所示。 14数据库技术与应用6.1.2 6.1.2 存储过程的执行存储过程的执行使用对象资源管理器中执行存储过程，操作方法如下：使用对象资源管理器中执行存储过程，操作方法如下： 15数据库技术与应用6.1.3 6.1.3 存储过程参数和执行状态存储过程参数和执行状态存储过程参数存储过程参数类型类型有：有：“输入输入”和和“输出输出”参数参数（1 1）输入参数）输入参数定义存储过程时，可指定输入参数，以定义存储过程时，可指定输入参数，以 作为参数名称的作为参数名称的前置字符，声明若干个参数变量及其数据类型，一个存储前置字符，声明若干个参

14、数变量及其数据类型，一个存储过程最多指定过程最多指定10241024个参数。个参数。（2 2）输出参数）输出参数如果要在存储过程中传回值给调用者，可在参数名称后使如果要在存储过程中传回值给调用者，可在参数名称后使用用OUTPUTOUTPUT 关键词。关键词。同时，为了使用输出参数，必须在创建和执行存储过程时同时，为了使用输出参数，必须在创建和执行存储过程时都使用都使用OUTPUTOUTPUT关键词。关键词。16数据库技术与应用6.1.3 6.1.3 存储过程参数和执行状态存储过程参数和执行状态【例例6.4】创建一个带两个参数的存储过程，从创建一个带两个参数的存储过程，从St_Info、C_In

15、fo、S_C_Info表的相关联接中返回输入参数的学生姓名和课程类别、该学生表的相关联接中返回输入参数的学生姓名和课程类别、该学生选课的课程名称和成绩。选课的课程名称和成绩。 CREATE PROCEDURE ScoreInfo stname varchar(20), ctype char(4) AS SELECT St_Info.St_Name, C_Info.C_Type, C_Info.C_Name, S_C_Info.Score FROM St_Info, S_C_Info, C_Info WHERE St_Info.St_ID = S_C_Info.St_ID AND S_C_Inf

16、o.C_No = C_Info.C_No AND St_Info.St_Name = stname AND C_Info.C_Type = ctype在在“新建查询新建查询”窗格中输入并运行如下命令：窗格中输入并运行如下命令： EXEC ScoreInfo 吴中华吴中华,必修必修 输出结果如右图所示。输出结果如右图所示。17数据库技术与应用6.1.3 6.1.3 存储过程参数和执行状态存储过程参数和执行状态两种传递参数的方式：两种传递参数的方式：位置标识位置标识和和名字标识名字标识。位置标识传递参数位置标识传递参数只按顺序提供值只按顺序提供值参数值必须以参数的定义顺序列出参数值必须以参数的定义

17、顺序列出可以忽略有默认值的参数，但不能中断次序可以忽略有默认值的参数，但不能中断次序例：例：EXEC ScoreInfo 吴中华吴中华,必修必修 名字标识传递值名字标识传递值在调用语句中以在调用语句中以“参数名参数名=值值”的格式指定参数的格式指定参数当通过参数名传递值时，可以以任何顺序指定参数值，并且可省略当通过参数名传递值时，可以以任何顺序指定参数值，并且可省略允许空值或具有默认值允许空值或具有默认值 的参数的参数若在若在“新建查询新建查询”窗格中输入并运行如下命令：窗格中输入并运行如下命令： EXEC ScoreInfo ctype=必修必修, stname=吴中华吴中华 EXEC Sc

18、oreInfo ctype = 必修必修,stname = 杨平娟杨平娟18数据库技术与应用6.1.3 6.1.3 存储过程参数和执行状态存储过程参数和执行状态- -举例举例【例例6.5】创建带一个输入参数和一个输出参数的存储过程，通过输入参数创建带一个输入参数和一个输出参数的存储过程，通过输入参数在在 St_Info表中查询指定学号的学生，以输出参数的形式返回学生所在的表中查询指定学号的学生，以输出参数的形式返回学生所在的班级名称（班级名称（Cl_Name字段）。字段）。 创建此存储过程的语句如下：创建此存储过程的语句如下： CREATE PROCEDURE StClass stid cha

19、r(10), class_name char(20) OUTPUT ASSELECT class_name = cl_name FROM St_Info WHERE St_Info.St_ID = stid 执行该存储过程的语句如下：执行该存储过程的语句如下： DECLARE get_clname char(20) EXEC StClass 0603060109, get_clname OUTPUT SELECT get_clname注意：变量被声明，其值会先被设为注意：变量被声明，其值会先被设为NULL。19数据库技术与应用6.1.3 6.1.3 存储过程参数和执行状态存储过程参数和执行状态

20、返回存储过程状态返回存储过程状态为了增强存储过程的效率，应使用错误信息向用户传达事为了增强存储过程的效率，应使用错误信息向用户传达事务状态（成功或失败）。务状态（成功或失败）。RETURNRETURN语句语句从查询或存储过程无条件返回，同时可以返回一个整数状从查询或存储过程无条件返回，同时可以返回一个整数状态值（返回码）态值（返回码）返回码为返回码为0 0表示执行成功，表示执行成功，返回返回-1-1-99-99之间的整数，表示之间的整数，表示执行失败。执行失败。20数据库技术与应用6.1.3 6.1.3 存储过程参数和执行状态存储过程参数和执行状态【例【例6.6】修改修改【例例6.5】示例中的

21、存储过程，分示例中的存储过程，分3种情况返回不同的执种情况返回不同的执行状态：如果输入空的学号参数值，则返回执行状态行状态：如果输入空的学号参数值，则返回执行状态“-1”；如果在；如果在St_Info表中不存在指定学号的学生，则返回执行状态表中不存在指定学号的学生，则返回执行状态“-2”；除前两种；除前两种情况之外（即找到了指定学号的学生），则返回执行状态情况之外（即找到了指定学号的学生），则返回执行状态“0”表示执行表示执行正常正常。修改此存储过程的语句如下：修改此存储过程的语句如下： CREATE CREATE PROCEDURE PROCEDURE StClass_newStClass_

22、new stidstid char(10) = NULL , char(10) = NULL , class_nameclass_name char(20) char(20) OUTPUTOUTPUT AS AS IF IF stidstid IS NULL IS NULL RETURN -1 RETURN -1 -IF-IF或或ELSEELSE条件只能影响一个条件只能影响一个SQLSQL语句（语句（p39p39） SELECT SELECT class_nameclass_name = = cl_namecl_name FROM FROM St_InfoSt_Info WHERE WHERE

23、 St_Info.St_IDSt_Info.St_ID = = stidstid IF IF class_nameclass_name IS IS NULLNULL RETURN -2RETURN -2 RETURN 0 RETURN 021数据库技术与应用6.1.3 6.1.3 存储过程参数和执行状态存储过程参数和执行状态正确接收返回的正确接收返回的状态的存储过程执行语句状态的存储过程执行语句形式：形式： EXEC EXEC status_varstatus_var = = 过程名称过程名称调用带输出参数的存储过程，根据返回码进行输出调用带输出参数的存储过程，根据返回码进行输出【例【例6.6

24、6.6】的执行过程：的执行过程： DECLARE DECLARE status_returnstatus_return intint DECLARE DECLARE get_clnameget_clname char(20 char(20) ) EXEC EXEC status_returnstatus_return = = StClass_newStClass_new 0603060109 0603060109, , get_clnameget_clname OUTPUTOUTPUT IF IF status_returnstatus_return = -1 = -1 PRINT PRINT

25、 没有输入学号没有输入学号 ELSE ELSE IF IF status_returnstatus_return = -2 = -2PRINT PRINT 找不到这个学号的学生找不到这个学号的学生 ELSE ELSEPRINT PRINT get_clnameget_clname 22数据库技术与应用6.1.4 6.1.4 存储过程的查看和修改存储过程的查看和修改使用对象资源管理器查看或修改存储过程使用对象资源管理器查看或修改存储过程23数据库技术与应用6.1.4 6.1.4 存储过程的查看和修改存储过程的查看和修改使用系统存储过程查看存储过程使用系统存储过程查看存储过程24系统存储过程系统存

26、储过程作作 用用使用语法使用语法sp_helptextsp_helptext查看存储过程的文本信息查看存储过程的文本信息sp_helptext objname= sp_helptext objname= 存储过程名存储过程名sp_dependssp_depends查看存储过程的相关性查看存储过程的相关性sp_depends objname= sp_depends objname= 存储过程名存储过程名sp_helpsp_help查看存储过程的一般信息查看存储过程的一般信息sp_helpsp_help objnameobjname= = 存储过程名存储过程名数据库技术与应用6.1.4 6.1.4

27、 存储过程的查看和修改存储过程的查看和修改使用使用ALTER PROCEDUREALTER PROCEDURE语句修改存储过程语句修改存储过程语法格式：语法格式：ALTER PROCEDUREALTER PROCEDURE schema_nameschema_name. . procedure_nameprocedure_name;number;numberparameter parameter data_typedata_type VARYING=defaultOUTPUT,.nVARYING=defaultOUTPUT,.nWITH RECOMPILE|ENCRYPTION|RECOMPI

28、LE,ENCRYPTIONWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONFOR REPLICATIONAS AS sql_statementsql_statement ,.n ,.n 参数参数和保留字的含义说明与和保留字的含义说明与CREATE PROCEDURE语句一致语句一致。25数据库技术与应用6.1.4 6.1.4 存储过程的查看和修改存储过程的查看和修改重命名存储过程重命名存储过程可可使用系统存储过程使用系统存储过程sp_renamesp_rename，语法格式：，语法格式： sp_renamesp_rena

29、me stored procedure stored procedure object_nameobject_name, , stored procedure stored procedure new_namenew_name 【例例6.86.8】 将将【例例6.16.1】创建的存储过程创建的存储过程p_Stup_Stu更名为更名为Student_procStudent_proc。 完成操作的完成操作的语句：语句： sp_renamesp_rename p_Stup_Stu, , Student_procStudent_proc 注意：通过对象资源管理器也可以修改存储过程的名称注意：通过对象资

30、源管理器也可以修改存储过程的名称26存储过程老名称存储过程老名称存储过程新名称存储过程新名称数据库技术与应用6.1.5 6.1.5 存储过程的删除存储过程的删除删除删除存储过程存储过程使用使用DROP PROCEDUREDROP PROCEDURE语句从当前数据库中移除用户定义语句从当前数据库中移除用户定义存储过程存储过程语法格式：语法格式：DROP DROP PROCEDURE PROCEDURE procedure_nameprocedure_name ,.n ,.n 删除存储过程的注意事项删除存储过程的注意事项在删除存储过程之前，执行系统存储过程在删除存储过程之前，执行系统存储过程sp_

31、dependssp_depends检查检查是否有对象依赖此存储过程。是否有对象依赖此存储过程。27数据库技术与应用6.1.5 6.1.5 存储过程的删除存储过程的删除【例例6.106.10】删除例删除例6.26.2所创建的存储过程所创建的存储过程Average_ScoreAverage_Score。完成操作的语句：完成操作的语句：USE student_dbUSE student_dbGO GO IF EXISTS ( IF EXISTS ( SELECT name FROM SELECT name FROM sysobjectssysobjects WHERE name=Average_Sc

32、ore WHERE name=Average_Score) )DROP DROP PROCEDURE PROCEDURE Average_ScoreAverage_Score注意：注意：不论是重命名存储过程名称还是删除了存储过程，都会影响到引用该不论是重命名存储过程名称还是删除了存储过程，都会影响到引用该存储过程的其他数据库对象。存储过程的其他数据库对象。28sysobjects sysobjects 系统对象表。系统对象表。 保保存当前数据库的对象，如约存当前数据库的对象，如约束、默认值、日志、规则、束、默认值、日志、规则、存储过程等存储过程等数据库技术与应用6.2 6.2 触发器概述触发器

33、概述触发器的特点和类型触发器的特点和类型触发器的创建触发器的创建触发器的查看和修改触发器的查看和修改触发器的删除触发器的删除29数据库技术与应用6.2.1 6.2.1 触发器的特点和类型触发器的特点和类型触发器（触发器（triggertrigger）是是SQL ServerSQL Server数据库中一种数据库中一种特殊特殊类型的类型的存储过程存储过程，不能不能由由用户直接调用用户直接调用，而且可以包含复杂的，而且可以包含复杂的T-SQLT-SQL语句。它是一语句。它是一个在修改指定表中的数据时执行的存储过程。用户可以用个在修改指定表中的数据时执行的存储过程。用户可以用它来强制实施复杂的业务规

34、则，以此确保数据的完整性。它来强制实施复杂的业务规则，以此确保数据的完整性。30数据库技术与应用6.2.1 6.2.1 触发器的特点和类型触发器的特点和类型触发器的特点触发器的特点触发器与触发器与表紧密相连表紧密相连，可以看作表定义的一部分。，可以看作表定义的一部分。触发器是基于一个表创建的，但是可以针对多个表进行操作，实现触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。数据库中相关表的级联更改。触发器触发器不能通过名称被直接调用不能通过名称被直接调用，更不允许带参数，而是当用户对，更不允许带参数，而是当用户对表中的数据进行修改这样的事件发生时，自动执行的行

35、为。表中的数据进行修改这样的事件发生时，自动执行的行为。触发器可以触发器可以用于用于SQL ServerSQL Server约束、默认值和规则的完整性检查约束、默认值和规则的完整性检查，实，实施更为复杂的数据完整性约束。施更为复杂的数据完整性约束。触发器可以评估数据修改前后的表状态，并根据其差异采取对策。触发器可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中可以存在多个同类触发器一个表中可以存在多个同类触发器（INSERTINSERT、UPDATEUPDATE或或DELETEDELETE），对于同一个修改语句可以有多个不同的对策用以响应。，对于同一个修改语句可以有多个不同的对策用以

36、响应。31数据库技术与应用6.2.1 6.2.1 触发器的特点和类型触发器的特点和类型触发器的类型触发器的类型按按触发事件触发事件不同分为不同分为2 2类类（1 1）DDLDDL（数据定义语言）触发器（数据定义语言）触发器是指当服务器或数据库中发生是指当服务器或数据库中发生DDLDDL事件时将启用。事件时将启用。DDLDDL事事件即指在表或索引中的件即指在表或索引中的createcreate、alteralter、dropdrop语句。语句。（2 2）DMLDML（ 数据操纵语言数据操纵语言 ）触发器）触发器是指触发器在数据库中发生是指触发器在数据库中发生DMLDML事件时将启用。事件时将启用

37、。DMLDML事件事件即指在表或视图中修改数据的即指在表或视图中修改数据的insertinsert、updateupdate、deletedelete语句。语句。因此因此DMLDML触发器也可分为触发器也可分为3 3种类型：种类型：INSERT触发器、触发器、UPDATE触发器、触发器、DELETE触发器。触发器。32数据库技术与应用6.2.1 6.2.1 触发器的特点和类型触发器的特点和类型触发器的类型触发器的类型按触发器被按触发器被激活的时机激活的时机可以分为以下两种可以分为以下两种类型：类型：（1 1）AFTERAFTER触发器（后触发器）触发器（后触发器）是是在触发动作之后再触动，可视

38、为控制触发器激活时间的机制。在引起在触发动作之后再触动，可视为控制触发器激活时间的机制。在引起触发器执行的更新语句成功完成之后执行。如果更新语句因错误（如违触发器执行的更新语句成功完成之后执行。如果更新语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。反约束或语法错误）而失败，触发器将不会执行。此类触发器只能定义在表上，不能创建在视图上。可以为每个触发操作此类触发器只能定义在表上，不能创建在视图上。可以为每个触发操作（如（如INSERTINSERT、UPDATEUPDATE或或DELETEDELETE）创建多个）创建多个AFTERAFTER触发器触发器。（2 2）INSTEAD OF

39、INSTEAD OF触发器（替代触发器）触发器（替代触发器）将将在数据变动以前被触发，该类触发器代替触发操作被执行。在数据变动以前被触发，该类触发器代替触发操作被执行。该类触发器既可在表上定义，也可在视图上定义。对于每个触发操作（该类触发器既可在表上定义，也可在视图上定义。对于每个触发操作（INSERTINSERT、UPDATEUPDATE和和DELETEDELETE）只能）只能定义定义1 1个个INSTEAD OFINSTEAD OF触发器。触发器。33数据库技术与应用6.2.2 6.2.2 触发器的创建触发器的创建与触发器相关的虚拟表与触发器相关的虚拟表在触发器执行的时候，在触发器执行的时

40、候，系统产生系统产生两个临时表：两个临时表：inserted inserted 表和表和deleted deleted 表。表。（1 1）insertedinserted表表存储着被存储着被INSERTINSERT和和UPDATEUPDATE语句语句影响的新的数据记录影响的新的数据记录。当用户执行。当用户执行INSERTINSERT和和UPDATEUPDATE语句时，新数据记录的备份被复制到语句时，新数据记录的备份被复制到insertedinserted临时表中。临时表中。（2 2）deleteddeleted表表存储着被存储着被DELETEDELETE和和UPDATEUPDATE语句语句影响

41、的旧数据记录影响的旧数据记录。在执行。在执行DELETEDELETE和和UPDATEUPDATE语句过程中，指定的旧数据记录被用户从基本表中删除，然后转语句过程中，指定的旧数据记录被用户从基本表中删除，然后转移到移到deletedelete表中。表中。34数据库技术与应用6.2.2 6.2.2 触发器的创建触发器的创建创建创建触发器主要有触发器主要有T-SQLT-SQL语句和对象资源管理器等方式。语句和对象资源管理器等方式。1 1使用使用CREATE TRIGGERCREATE TRIGGER语句创建语句创建触发器触发器 语法格式：语法格式： CREATE TRIGGER schema_nam

42、e. trigger_name ON table_name|view_name WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE,INSERT,UPDATE AS sql_statement ,.n 例：例：CREATE TRIGGET trig_stu ON Student AFTER INSERT, DELETE, UPDATE AS SELECT * FROM student35创建触发器必须指定的选项：创建触发器必须指定的选项：名称；名称；在其上定义触发器的表；在其上定义触发器的表；触发器将何时激发；触发器将何时激发；激活触发器的数据修改语

43、句；激活触发器的数据修改语句；执行触发操作的编程语句；执行触发操作的编程语句；对对CREATE TRIGGER CREATE TRIGGER 语句的文语句的文本加密。本加密。数据库技术与应用6.2.2 6.2.2 触发器的创建触发器的创建创建触发器注意事项：创建触发器注意事项：（1 1）CREATE TRIGGERCREATE TRIGGER语句语句必须必须是批处理中的是批处理中的第一条第一条语句。语句。（2 2）只能在）只能在当前数据库当前数据库中创建触发器，一个触发器只能中创建触发器，一个触发器只能对对应一个表。应一个表。（3 3）表的所有者具有创建触发器的默认权限，不能将该权）表的所有者

44、具有创建触发器的默认权限，不能将该权限转给其他用户。限转给其他用户。（4 4）不能在）不能在视图、临时表、系统表视图、临时表、系统表上创建触发器，但是触上创建触发器，但是触发器可以引用视图、临时表，但是不能引用系统表。发器可以引用视图、临时表，但是不能引用系统表。（5 5）尽管）尽管TRUNCATE TABLETRUNCATE TABLE语句类似于没有语句类似于没有WHEREWHERE子句的子句的DELETEDELETE语句，但由于该语句不被记入日志，所以它不会引语句，但由于该语句不被记入日志，所以它不会引发发DELETEDELETE触发器。触发器。36数据库技术与应用6.2.2 6.2.2

45、触发器的创建触发器的创建【例例6.126.12】创建创建DelCourseDelCourse触发器的语句如下：触发器的语句如下：CREATE TRIGGER CREATE TRIGGER DelCourseDelCourse ON ON C_InfoC_InfoFOR FOR DELETE DELETE ASASDELETE DELETE S_C_InfoS_C_Info WHERE WHERE C_NoC_No IN ( IN (SELECT SELECT C_NoC_No FROM FROM deleted)deleted)GOGO在在“新建查询新建查询”窗格中输入以下语句并执行：窗格中输

46、入以下语句并执行：DELETE DELETE FROM FROM C_InfoC_Info WHERE WHERE C_NoC_No=29000011=29000011注意注意：该语句从：该语句从C_Info表中删除课程编号为表中删除课程编号为“29000011”的数据行的数据行，触发，触发DelCourse触发器，触发器，产生信息产生信息：37数据库技术与应用6.2.2 6.2.2 触发器的创建触发器的创建2 2使用图形界面方式创建触发器使用图形界面方式创建触发器 在表在表St_InfoSt_Info上创建触发器，操作步骤：上创建触发器，操作步骤：38在学生表在学生表St_InfoSt_In

47、fo中的中的INSERTINSERT操作上创建操作上创建了一个名称为了一个名称为“st_Insertst_Insert”的触发器。的触发器。当在该表上执行任何有效的插入操作（当在该表上执行任何有效的插入操作（不论是否实际插入了记录）时，都会激不论是否实际插入了记录）时，都会激活该触发器，将变量活该触发器，将变量 strstr的值设为的值设为“TRIGGER IS WORKING”TRIGGER IS WORKING”。PRINTPRINT命令的作用命令的作用是向客户端返回用是向客户端返回用户定义的消息。户定义的消息。数据库技术与应用6.2.3 6.2.3 触发器的查看和修改触发器的查看和修改1

48、 1使用对象资源管理器查看触发器信息使用对象资源管理器查看触发器信息39数据库技术与应用6.2.3 6.2.3 触发器的查看和修改触发器的查看和修改2 2使用系统存储过程查看触发器信息使用系统存储过程查看触发器信息在在SQL ServerSQL Server中，根据不同需要，可以使用中，根据不同需要，可以使用sp_helptextsp_helptext、sp_dependssp_depends、sp_helpsp_help等系统存储过程来查看触发器的不同等系统存储过程来查看触发器的不同信息。信息。例：例：40数据库技术与应用6.2.3 6.2.3 触发器的查看和修改触发器的查看和修改专门查看触

49、发器属性信息的系统存储过程：专门查看触发器属性信息的系统存储过程：sp_helptriggersp_helptrigger，语法格式：，语法格式：sp_helptrigger tabname = table sp_helptrigger tabname = table , triggertype = type , triggertype = type 【例例6.146.14】查看查看S_C_InfoS_C_Info表上存在的触发器的属性信息。表上存在的触发器的属性信息。操作的语句：操作的语句： EXEC EXEC sp_helptrigger sp_helptrigger S_C_Info S_C_Info 41数据库技术与应用6.2.3 6.2.3 触发器的查看和修改触发器的查看和修改3.3.使用对象资源管理器修改触发器的正文使用对象资源管理器修改触发器的正文 注意：被设置成注意：被设置成“WITH ENCRYPTION”WITH ENCRYPTION”触发器是不能被修改的。触发器是不能被修改的。42数据库技术与应用6.2.3 6.2.3 触发器的查看和修改触发器的查看和修改4