8.存储过程与触发器.ppt

《8.存储过程与触发器.ppt》由会员分享，可在线阅读，更多相关《8.存储过程与触发器.ppt（55页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、系系统统第九章第九章 存储过程与触发器存储过程与触发器v前前台台后台数据库后台数据库包含用户的操作界面。例如：登陆界面、商品入库界面、卖出商品界面等第九章第九章 存储过程与触发器存储过程与触发器v前前台台后台数据库后台数据库执行执行select命令，并将结果返回命令，并将结果返回商品查询界面根据关键词写出select 语句第九章第九章 存储过程与触发器存储过程与触发器v前前台台后台数据库后台数据库自动执行已编写好的命令自动执行已编写好的命令(存储过程存储过程)，将结果返回，将结果返回商品查询界面根据关键词写出调用语句第九章第九章 存储过程与触发器存储过程与触发器v存储过程综述存储过程综述v存储

2、过程的基本操作存储过程的基本操作v创建和执行带有参数的存储过程创建和执行带有参数的存储过程v存储过程重新编译存储过程重新编译v系统存储过程和扩展存储过程系统存储过程和扩展存储过程第九章第九章 存储过程与触发器存储过程与触发器v存储过程综述存储过程综述1、存储过程概念、存储过程概念存储过程是一种数据库对象，是为了实现某个特定任务，将一组存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编译的预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调语句以一个存储单元的形式存储在服务器上，供用户调用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在用。存储过程在第一次执行时进行

3、编译，然后将编译好的代码保存在高速缓存中以便以后调用，这样可以提高代码的执行效率。高速缓存中以便以后调用，这样可以提高代码的执行效率。存储过程同其他编程语言中的过程相似，有如下存储过程同其他编程语言中的过程相似，有如下特点特点：1）接受输入参数并以输出参数的形式将多个值返回至调用过程或）接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理。批处理。2）包含执行数据库操作（包括调用其它过程）的编程语句。）包含执行数据库操作（包括调用其它过程）的编程语句。3）向调用过程或批处理返回状态值，以表明成功或失败（以及失）向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）。败原因）。第

4、九章第九章 存储过程与触发器存储过程与触发器v存储过程综述存储过程综述优点：优点：1、安全机制：只给用户访问存储过程的权限，而不授予用户访问表、安全机制：只给用户访问存储过程的权限，而不授予用户访问表和视图的权限。和视图的权限。2、改良了执行性能：在第一次执行后，会在、改良了执行性能：在第一次执行后，会在SQLserver的缓冲区中的缓冲区中创建查询树，以后执行无需编译。创建查询树，以后执行无需编译。3、减少网络流量：存储过程存在于服务器上，调用时，只需传递执、减少网络流量：存储过程存在于服务器上，调用时，只需传递执行存储过程的执行命令和返回结果。行存储过程的执行命令和返回结果。4、模块化的程

5、序设计：增强了代码的可重用性与共享性，提高了开、模块化的程序设计：增强了代码的可重用性与共享性，提高了开发效率。发效率。5、存储过程提供了处理复杂任务的能力、存储过程提供了处理复杂任务的能力第九章第九章 存储过程与触发器存储过程与触发器v存储过程综述存储过程综述2、存储过程类型、存储过程类型系统存储过程系统存储过程：系统存储过程存储在：系统存储过程存储在master数据库中，并以数据库中，并以sp_为前缀，主为前缀，主要用来从系统表中获取信息，为系统管理员管理要用来从系统表中获取信息，为系统管理员管理SQLServer提供帮助，为用提供帮助，为用户查看数据库对象提供方便。户查看数据库对象提供方

6、便。本地存储过程本地存储过程：本地存储过程是用户根据需要，在自己的普通数据库中创：本地存储过程是用户根据需要，在自己的普通数据库中创建的存储过程。建的存储过程。临时存储过程临时存储过程：临时存储过程通常分为局部临时存储过程和全局临时存储：临时存储过程通常分为局部临时存储过程和全局临时存储过程。创建局部临时存储过程时，要以过程。创建局部临时存储过程时，要以“#”作为过程名称的第一个字符。创作为过程名称的第一个字符。创建全局临时存储过程时，要以建全局临时存储过程时，要以“#”作为过程名称的前两个字符。作为过程名称的前两个字符。远程存储过程远程存储过程：远程存储过程是：远程存储过程是SQLServe

7、r2000的一个传统功能，是指非的一个传统功能，是指非本地服务器上的存储过程。本地服务器上的存储过程。扩展存储过程扩展存储过程：扩展存储过程以：扩展存储过程以xp_为前缀，它是关系数据库引擎的开放式为前缀，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库数据服务层的一部分，其可以使用户在动态链接库(DLL)文件所包含的函数中文件所包含的函数中实现逻辑，从而扩展了实现逻辑，从而扩展了Transact-SQL的功能，并且可以象调用的功能，并且可以象调用Transact-SQL过程那样从过程那样从Transact-SQL语句调用这些函数。语句调用这些函数。第九章第九章 存储过程

8、与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建创建存储过程时，需要注意下列事项：创建存储过程时，需要注意下列事项：1、只能在当前数据库中创建存储过程。、只能在当前数据库中创建存储过程。2、数据库的所有者可以创建存储过程，也可以授权其他用户创建存、数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。储过程。3、存储过程是数据库对象，其名称必须遵守标识符命名规则。、存储过程是数据库对象，其名称必须遵守标识符命名规则。4、不能将、不能将CREATEPROCEDURE语句与其它语句与其它SQL语句组合到单语句组合到单个批处理中。个批处理中。5、创建存储过程时，应指

9、定所有输入参数和向调用过程或批处理返、创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。理以表明成功或失败的状态值。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建使用使用SQL语句创建不带参数的存储过程语法格式如下：语句创建不带参数的存储过程语法格式如下：CREATEPROCEDUREprocedure_name WITHRECOMPILE|ENCRYPTIONASsql_statement.n

10、 procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。数据库及其所有者必须唯一。RECOMPILE：SQL不会缓存该过程的计划，该过程将在运行时重新编译。不会缓存该过程的计划，该过程将在运行时重新编译。ENCRYPTION：SQLServer加密加密使用使用ENCRYPTION可防止将过程作为可防止将过程作为SQLServer复制的一部分发布。复制的一部分发布。第九章第九章 存储过程与触发器存储过程与触发器例、在例、在STUDENT数据库中，创建一个查询存储过程数据库中，创建一个查询存储过程

11、ST_PROC_BJ，该存储过程将返回计算机系的班级名称。其，该存储过程将返回计算机系的班级名称。其程序清单如下：程序清单如下：USESTUDENTGOCREATEPROCDBO.ST_PROC_BJASSELECT班级名称班级名称FROM班级班级,系部系部WHERE系部系部.系部代码系部代码=班级班级.系部代码系部代码and系部系部.系系部名称部名称=计算机计算机GO第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-执行执行执行存储过程执行存储过程对存储在服务器上的存储过程，可以使用对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其命

12、令或其名称执行它，其语法格式如下：语法格式如下：EXECUTEprocedure_namenumber如果存储过程是批处理中的第一条语句，如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略，可以使用命令可以省略，可以使用存储过程的名字执行该存储过程。存储过程的名字执行该存储过程。例：在查询分析器中执行存储过程例：在查询分析器中执行存储过程ST_PROC_BJ，其代码清单如下：，其代码清单如下：USESTUDENTEXECUTEST_PROC_BJGO第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建1、使用、使用SQL语句创建带参数的存储

13、过程语法格式如下：语句创建带参数的存储过程语法格式如下：CREATEPROCEDUREprocedure_namenumberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASsql_statement.n只有两个必需的参数必须传递给只有两个必需的参数必须传递给CREATEPROCEDURE语句：创建语句：创建存储过程所需的存储过程所需的procedure_name和和sql_statements。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储

14、过程的基本操作-创建创建其中：其中：procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。且对于数据库及其所有者必须唯一。number：该参数是可选的整数，用来对同名的过程分组，以便用一条：该参数是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起删除。例如，名为语句即可将同组的过程一起删除。例如，名为orders的应用程的应用程序使用的过程可以命名为序使用的过程可以命名为orderproc1、orderproc2等。等。DROPPROCED

15、UREorderproc语句将删除整个组。语句将删除整个组。parameter：存储过程中的输入和输出参数。：存储过程中的输入和输出参数。data_type：参数的数据类型。：参数的数据类型。Default：指参数的默认值，必须是常量或：指参数的默认值，必须是常量或NULL。如果定义了默认值，不必。如果定义了默认值，不必指定该参数的值也可执行过程。指定该参数的值也可执行过程。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建其中：其中：OUTPUT：表明参数是返回参数。该选项的值可以返回给：表明参数是返回参数。该选项的值可以返回给EXECUTE。使用

16、。使用OUTPUT参数可将信息返回给调用过程。参数可将信息返回给调用过程。RECOMPILE：表明：表明SQLServer不保存存储过程的计划，该过程将在运行时不保存存储过程的计划，该过程将在运行时重新编译。重新编译。ENCRYPTION：表示：表示SQLServer加密加密syscomments表中包含表中包含CREATEPROCEDURE语句文本的条目。语句文本的条目。sql_statement：指存储过程中的任意数目和类型的：指存储过程中的任意数目和类型的Transact-SQL语句。语句。在存储过程中使用参数，可以扩展存储过程的功能。使用输入参数在存储过程中使用参数，可以扩展存储过程的

17、功能。使用输入参数，可以将外部信息传入到存储过程；使用输出参数，可以将存储过程，可以将外部信息传入到存储过程；使用输出参数，可以将存储过程内的信息传出到外部内的信息传出到外部。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建2、创建步骤、创建步骤一般来说，创建一个存储过程应按照以下步骤进行：一般来说，创建一个存储过程应按照以下步骤进行：编写编写SQL语句。语句。测试测试SQL语句是否正确，并能实现功能要求。语句是否正确，并能实现功能要求。若得到的结果数据符合预期要求，则按照存储过若得到的结果数据符合预期要求，则按照存储过程的语法，创建该建存储过程。

18、程的语法，创建该建存储过程。执行该存储过程，验证其正确性。执行该存储过程，验证其正确性。第九章第九章 存储过程与触发器存储过程与触发器例：在例：在STUDENT数据库中，建立一个名为数据库中，建立一个名为XIBU_INFOR的的存储过程，它带有一个参数，用于接受系部代码，显示该系存储过程，它带有一个参数，用于接受系部代码，显示该系部名称和系主任信息。部名称和系主任信息。USESTUDENTGOCREATEPROCEDUREXIBU_INFOR系部代码系部代码CHAR(2)ASSELECT系部名称系部名称,系主任系主任FROM系部系部WHERE系部代码系部代码=系部代码系部代码GO第九章第九章

19、存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-执行执行3、执行有参数的存储过程、执行有参数的存储过程对存储在服务器上的存储过程，可以使用对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其命令或其名称执行它，其语法格式如下：语法格式如下：EXECUTEreturn_status=procedure_namenumberparameter=value|variableOUTPUT|DEFAULT,.n其中：其中：如果存储过程是批处理中的第一条语句，如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略，可以使命令可以省略，可以使用存储过程的名

20、字执行该存储过程。用存储过程的名字执行该存储过程。return_status：是一个可选的整型变量，用来保存存储过程的返回状态。：是一个可选的整型变量，用来保存存储过程的返回状态。parameter：存储过程的参数。：存储过程的参数。例如：执行存储过程：例如：执行存储过程：EXECXIBU_INFOR01第九章第九章 存储过程与触发器存储过程与触发器例：包含了复杂的查询语句的存储过程例：包含了复杂的查询语句的存储过程。Createprocedureabcasselectsno,sum(credit)total_creditinto#o=owheregrade=60groupbysnohavin

21、gsum(credit)4selecta.sno,name,total_creditfromstudentajoin#1bona.sno=b.sno第九章第九章 存储过程与触发器存储过程与触发器例：查询某系学生人数例：查询某系学生人数createprocedurenumberdeptvarchar(10),countintoutputasselectcount=count(*)fromstudentwheredept=deptreturncount执行：执行：declarexintexecnumber计算机计算机,xoutputprintx第九章第九章 存储过程与触发器存储过程与触发器例：查询

22、选修课程门数的学生基本情况例：查询选修课程门数的学生基本情况createprocedurexxmsxxmsintoutputasselect*fromstudentwheresnoin(selectsnofromscgroupbysnohavingcount(*)=xxms)执行：执行：execxxmc2第九章第九章 存储过程与触发器存储过程与触发器例：例：usepubsgocreateproceduretitle_sumtitlevarchar(40)=%,summoneyoutputasselecttitlename=titlefromtitleswheretitleliketitlese

23、lectsum=sum(price)fromtitleswheretitleliketitlego第九章第九章 存储过程与触发器存储过程与触发器执行：执行：declaretotalcostmoneyexecutetitle_sumthe%,totalcostoutputiftotalcost=20beginselectthetotalcostofthesetitleis$+rtrim(cast(totalcostasvarchar(20)end注意：注意：必须在必须在createprocedure和和execute语句中都指定语句中都指定output关键字。如果关键字。如果output关键字在

24、存储过程执行过程中被忽略了，关键字在存储过程执行过程中被忽略了，存储过程仍然执行，但是他将会产生一个错误状态。存储过程仍然执行，但是他将会产生一个错误状态。例：自动编号例：自动编号createprocedureinsert_student1namechar(8),sexchar(2),ageint,deptvarchar(10)asdeclaresnochar(5)selectsno=max(sno)fromstudent1ifsno=setsno=00001elsebeginSETsno=CAST(CAST(snoASINT)+1ASVARCHAR(5)SETsno=REPLICATE(0,

25、5-LEN(sno)+snoendinsertintostudent1values(sno,name,sex,age,dept)Go执行：执行：execinsert_student1cd,女女,20,经管经管第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建使用企业管理器使用企业管理器例：在例：在STUDENT数据库中，创建一个名称为数据库中，创建一个名称为ST_CHAXUN_01的存储过程，该的存储过程，该存储过程返回计算机系学生的姓名、性别和年龄信息。其操作步骤如下：存储过程返回计算机系学生的姓名、性别和年龄信息。其操作步骤如下：1）打开企业管理

26、器，展开控制台目录，依次展开服务器组、服务器、数据库）打开企业管理器，展开控制台目录，依次展开服务器组、服务器、数据库节点。节点。2）单击相应的数据库（这里我们选择）单击相应的数据库（这里我们选择student数据库），在其右边的详细窗格数据库），在其右边的详细窗格中右击中右击“存储过程存储过程”图标，在弹出的快捷菜单中选择图标，在弹出的快捷菜单中选择“新建存储过程新建存储过程”命令。命令。3）执行）执行“新建存储过程新建存储过程”命令，打开命令，打开“存储过程属性存储过程属性”对话框。在其文本框对话框。在其文本框中首先输入所有者和存储过程名称，例如用所有者中首先输入所有者和存储过程名称，例如

27、用所有者DBO替换替换OWNER，用过程，用过程名名ST_CHAXUN_01替换替换PROCEDURENAME。4）在文本框的第二行输入存储过程文本，根据题意输入如下语句：）在文本框的第二行输入存储过程文本，根据题意输入如下语句：SELECT姓名姓名,性别性别,YEAR(GETDATE()-YEAR(出生日期出生日期)AS年龄年龄FROM学生学生WHERE系部代码系部代码=015）输入完成后，单击）输入完成后，单击“语法检查语法检查”按钮，检查语法是否正确，然后单击按钮，检查语法是否正确，然后单击“确确定定”。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作

28、-查看查看1、使用企业管理器查看存储过程的步骤、使用企业管理器查看存储过程的步骤为：为：1）打开企业管理器，展开控制台目录，依）打开企业管理器，展开控制台目录，依次展开服务器组、服务器、数据库节点。次展开服务器组、服务器、数据库节点。2）单击相应的数据库（这里我们选择）单击相应的数据库（这里我们选择student数据库），选择数据库），选择“存储过程存储过程”节点，节点，在右边的列表中显示出当前数据库中所有的在右边的列表中显示出当前数据库中所有的存储过程。存储过程。3）选择需要查看的存储过程右击，例如）选择需要查看的存储过程右击，例如“ST_PROC_BJ”，在弹出的快捷菜单中选择，在弹出的快

29、捷菜单中选择“属性属性”命令，打开命令，打开“存储过程属性存储过程属性”对话对话框。框。4）在）在“属性属性”对话框中，既可以查看过程对话框中，既可以查看过程定义信息，又可以在文本框中对存储过程的定义信息，又可以在文本框中对存储过程的定义进行修改。修改后，可以单击定义进行修改。修改后，可以单击“应用应用”或或“确定确定”按钮，保存修改。按钮，保存修改。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-查看查看2、使用系统存储过程查看存储过程信息、使用系统存储过程查看存储过程信息在在SQLServer中，根据不同需要，可以使用中，根据不同需要，可以使用sp_h

30、elptext、sp_depends、sp_help等系统存储过程来查看存储过程的不同信息。具体作用和语法如下：等系统存储过程来查看存储过程的不同信息。具体作用和语法如下：sp_helptext查看存储过程的文本信息，其语法格式为：查看存储过程的文本信息，其语法格式为：sp_helptext存储过程名存储过程名sp_depends查看存储过程的相关性，其语法格式为：查看存储过程的相关性，其语法格式为：sp_depends存储过程名存储过程名sp_help查看存储过程的一般信息，其语法格式为：查看存储过程的一般信息，其语法格式为：sp_help存储过程名存储过程名例：使用有关系统过程查看例：使用

31、有关系统过程查看STUDENT数据库中名为数据库中名为ST_PROC_BJ的存储过程的存储过程的定义、相关性以及一般信息。其程序清单如下：的定义、相关性以及一般信息。其程序清单如下：USESTUDENTGOEXECSP_HELPTEXTST_PROC_BJEXECSP_DEPENDSST_PROC_BJEXECSP_HELPST_PROC_BJGO第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-修改修改下面介绍下面介绍SQL语句，其语法格式为：语句，其语法格式为：ALTERPROCEDUREprocedure_namenumberparameterdata

32、_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n例：修改存储过程例：修改存储过程ST_PROC_BJ，使该存储过程返回经济管理系的班级名称。，使该存储过程返回经济管理系的班级名称。其程序清单如下：其程序清单如下：ALTERPROCDBO.ST_PROC_BJASSELECT班级名称班级名称FROM班级班级,系部系部WHERE系部系部.系部代码系部代码=班级班级.系部代码系部代码and系部系部.系部名称系部名称=经济管理系经济管理系GO

33、第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-删除删除1、使用企业管理器删除存储过程、使用企业管理器删除存储过程在企业管理器中，右击要删除的存储过程，从弹出的快捷菜单中选在企业管理器中，右击要删除的存储过程，从弹出的快捷菜单中选择择“删除删除”命令，将弹出命令，将弹出“移除对象移除对象”对话框。在此对话框中，单击对话框。在此对话框中，单击“全部除去全部除去”按钮，删除该存储过程。按钮，删除该存储过程。2、使用、使用DROPPROCEDURE语句删除存储过程语句删除存储过程DROPPROCEDURE语句可以一次从当前数据库中将一个或多个语句可以一次从当前数

34、据库中将一个或多个存储过程或过程组删除，其语法格式如下：存储过程或过程组删除，其语法格式如下：DROPPROCEDURE存储过程名称存储过程名称,n例：删除存储过程例：删除存储过程ST_CHAXUN_01，其程序清单如下：，其程序清单如下：USESTUDENTGODROPPROCEDUREST_CHAXUN_01GO第九章第九章 存储过程与触发器存储过程与触发器v存储过程的重新编译存储过程的重新编译存储过程第一次执行后，其被编译的代码将驻留在高速缓存中，存储过程第一次执行后，其被编译的代码将驻留在高速缓存中，当用户再次执行该存储过程时，当用户再次执行该存储过程时，SQLServer将其从缓存中

35、调出执行。将其从缓存中调出执行。有时，在我们使用了一次存储过程后，可能会因为某些原因，必须向有时，在我们使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，如果调用缓存中的存储过程，需要对它进行重新编译，使构。这时，如果调用缓存中的存储过程，需要对它进行重新编译，使存储过程能够得到优化。存储过程能够得到优化。SQLServer提供三种重新编译存储过程的方提供三种重新编译存储过程的方法：法：1、在建立存储过程时设定重新编译、在建立存储过程时设定重新编译创建存储过程时，在其

36、定义中指定创建存储过程时，在其定义中指定WITHRECOMPILE选项，使选项，使SQLServer在每次执行存储过程时都要重新编译。在每次执行存储过程时都要重新编译。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的重新编译存储过程的重新编译2、在执行存储过程时设定重编译、在执行存储过程时设定重编译在执行存储过程时指定在执行存储过程时指定WITHRECOMPILE选项，可强制对存储过程选项，可强制对存储过程进行重新编译。其语法格式如下：进行重新编译。其语法格式如下：EXECUTEprocedure_nameWITHRECOMPILE 3、通过使用系统存储过程设定重编译、通过使用系统存

37、储过程设定重编译系统存储过程系统存储过程sp_recompile强制在下次运行存储过程时进行重新编译。强制在下次运行存储过程时进行重新编译。其语法格式为：其语法格式为：EXECsp_recompileOBJECT其中其中OBJECT是当前数据库中的存储过程、触发器、表或视图的名称。是当前数据库中的存储过程、触发器、表或视图的名称。如果如果object是存储过程或触发器的名称，那么该存储过程或触发器将在是存储过程或触发器的名称，那么该存储过程或触发器将在下次运行时重新编译。下次运行时重新编译。第九章第九章 存储过程与触发器存储过程与触发器v触发器综述触发器综述v触发器的基本操作触发器的基本操作v

38、创建触发器创建触发器v查看触发器查看触发器v修改触发器修改触发器v禁止和启用触发器禁止和启用触发器v删除触发器删除触发器v嵌套触发器嵌套触发器第九章第九章 存储过程与触发器存储过程与触发器v触发器综述触发器综述1、触发器概念、触发器概念触发器是一种特殊类型的存储过程，不由用户直接调用，而且可以包触发器是一种特殊类型的存储过程，不由用户直接调用，而且可以包含复杂的含复杂的SQL语句。它们主要用于强制复杂的业务规则或要求。触发语句。它们主要用于强制复杂的业务规则或要求。触发器还有助于强制器还有助于强制引用完整性引用完整性，以便在添加、更新或删除表中的行时保，以便在添加、更新或删除表中的行时保留表之

39、间已定义的关系留表之间已定义的关系。可以完成存储过程能完成的功能，但是它具可以完成存储过程能完成的功能，但是它具有自己显著的的特点：有自己显著的的特点：1）它与表紧密相连，可以看作表定义的一部分；）它与表紧密相连，可以看作表定义的一部分；2）它不能通过名称被直接调用，更不允许带参数，而是当用户对）它不能通过名称被直接调用，更不允许带参数，而是当用户对表中的数据进行修改时，自动执行；表中的数据进行修改时，自动执行；3）它可以用于）它可以用于SQLServer约束、默认值和规则的完整性检查，实约束、默认值和规则的完整性检查，实施更为复杂的数据完整性约束。施更为复杂的数据完整性约束。第九章第九章 存

40、储过程与触发器存储过程与触发器v触发器综述触发器综述2、触发器的优点、触发器的优点触发器包含复杂的处理逻辑，能够实现复杂的数据完整性约束。触发器包含复杂的处理逻辑，能够实现复杂的数据完整性约束。同其他约束相比，它主要有以下优点：同其他约束相比，它主要有以下优点：1）触发器自动执行）触发器自动执行在对表的数据作了任何修改（比如手工输入或者应用程序采取的在对表的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。操作）之后立即被激活。2）触发器能够对数据库中的相关表实现级联更改）触发器能够对数据库中的相关表实现级联更改触发器是基于一个表创建的，但是可以针对多个表进行操作，实触发器

41、是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。例如，在学生数据库中，可以在产品现数据库中相关表的级联更改。例如，在学生数据库中，可以在产品表的产品编号字段上建立一个插入触发器，当对产品表增加记录时，表的产品编号字段上建立一个插入触发器，当对产品表增加记录时，在产品销售表的产品编号上自动插入编号值。在产品销售表的产品编号上自动插入编号值。第九章第九章 存储过程与触发器存储过程与触发器v触发器综述触发器综述2、触发器的优点、触发器的优点3）触发器可以实现比）触发器可以实现比CHECK约束更为复杂的数据完整性约束约束更为复杂的数据完整性约束在数据库中为了实现数据完整性

42、约束，可以使用在数据库中为了实现数据完整性约束，可以使用CHECK约束或触发约束或触发器。器。CHECK约束不允许引用其它表中的列来完成检查工作，而触发约束不允许引用其它表中的列来完成检查工作，而触发器可以引用其它表中的列。例如，在器可以引用其它表中的列。例如，在STUDENT数据库中，向学生表数据库中，向学生表中插入记录时，当输入系部代码时，必须先检查系部表中是否存在该中插入记录时，当输入系部代码时，必须先检查系部表中是否存在该系。这只能通过触发器实现，而不能通过系。这只能通过触发器实现，而不能通过CHECK约束完成。约束完成。4）触发器可以评估数据修改前后的表状态，并根据其差异采取对策。）

43、触发器可以评估数据修改前后的表状态，并根据其差异采取对策。5）一个表中可以存在多个同类触发器（）一个表中可以存在多个同类触发器（INSERT、UPDATE或或DELETE），对于同一个修改语句可以有多个不同的对策以响应。），对于同一个修改语句可以有多个不同的对策以响应。第九章第九章 存储过程与触发器存储过程与触发器v触发器综述触发器综述3、触发器的种类、触发器的种类SQLServer2000按触发被被激活的时机可以分为两种类型：按触发被被激活的时机可以分为两种类型：AFTER触发器和触发器和INSTEADOF触发器。触发器。AFTER触发器触发器又称为后触发器，该类触发器是在引起触发器执行又称

44、为后触发器，该类触发器是在引起触发器执行的修改语句成功完成之后执行。如果修改语句因错误（如违反约束或的修改语句成功完成之后执行。如果修改语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。此类触发器只能定义在表上，语法错误）而失败，触发器将不会执行。此类触发器只能定义在表上，不能创建在视图上。可以为每个触发操作（不能创建在视图上。可以为每个触发操作（INSERT、UPDATE或或DELETE）创建多个）创建多个AFTER触发器。触发器。INSTEADOF触发器触发器又称为替代触发器，当引起触发器执行的修又称为替代触发器，当引起触发器执行的修改语句停止执行时，该类触发器代替触发操作执行。

45、该类触发器既可改语句停止执行时，该类触发器代替触发操作执行。该类触发器既可在表上定义，也可在视图上定义。对于每个触发操作（在表上定义，也可在视图上定义。对于每个触发操作（INSERT、UPDATE和和DELETE）只能定义一个）只能定义一个INSTEADOF触发器。触发器。第九章第九章 存储过程与触发器存储过程与触发器v触发器触发器在进行触发器的基本操作之前，介绍两张特殊的临时表，分别是在进行触发器的基本操作之前，介绍两张特殊的临时表，分别是inserted表和表和deleted表。这两张表都存在于高速缓存中。用户可以使表。这两张表都存在于高速缓存中。用户可以使用这两张临时表来检测某些修改操作

46、所产生的效果。例如，可以使用用这两张临时表来检测某些修改操作所产生的效果。例如，可以使用SELECT语句来检查语句来检查INSERT和和UPDATE语句执行的插入操作是否语句执行的插入操作是否成功，触发器是否被这些语句触发等。但是不允许用户直接修改成功，触发器是否被这些语句触发等。但是不允许用户直接修改inserted表和表和deleted表中数据。表中数据。第九章第九章 存储过程与触发器存储过程与触发器v触发器触发器deleted表中存储着被表中存储着被DELETE和和UPDATE语句影响的旧数据行。在语句影响的旧数据行。在执行执行DELETE和和UPDATE语句过程中，指定的数据行被用户从

47、基本表语句过程中，指定的数据行被用户从基本表中删除，然后转移到了中删除，然后转移到了deleted表中。一般来说，在基本表中表中。一般来说，在基本表中deleted表表中不会存在有相同的数据行。中不会存在有相同的数据行。inserted表中存储着被表中存储着被INSERT和和UPDATE语句影响的新的数据行。语句影响的新的数据行。当用户执行当用户执行INSERT和和UPDATE语句时，新的数据行被添加到语句时，新的数据行被添加到inserted表中，同时这些数据行的备份被复制到表中，同时这些数据行的备份被复制到inserted临时表中。临时表中。一个典型的一个典型的UPDATE事务实际上是由两

48、个操作组成。首先，旧的数事务实际上是由两个操作组成。首先，旧的数据行从基本表中转移到据行从基本表中转移到deleted表中，前提是这个过程没有出错；紧接表中，前提是这个过程没有出错；紧接着将新的数据行同时插入基本表和着将新的数据行同时插入基本表和inserted表。表。第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_创建创建在创建触发器前，必须注意以下几点：在创建触发器前，必须注意以下几点：1、CREATETRIGGER语句必须是批处理中的第一条语句。语句必须是批处理中的第一条语句。2、只能在当前数据库中创建触发器，名称必须遵循标识符的命名规则。、只能在当前数

49、据库中创建触发器，名称必须遵循标识符的命名规则。3、表的所有者具有创建触发器的默认权限，不能将该权限转给其他用户。、表的所有者具有创建触发器的默认权限，不能将该权限转给其他用户。4、不能在临时表或系统表上创建触发器，但是触发器可以引用临时表，但、不能在临时表或系统表上创建触发器，但是触发器可以引用临时表，但是不能引用系统表。是不能引用系统表。5、尽管、尽管TRUNCATETABLE语句类似于没有语句类似于没有WHERE子句（用于删除行）子句（用于删除行）的的DELETE语句，但由于该语句不被记入日志，所以它不会引发语句，但由于该语句不被记入日志，所以它不会引发DELETE触触发器。发器。6、W

50、RITETEXT语句不会引发语句不会引发INSERT或或UPDATE触发器。触发器。在创建触发器时，必须指明在哪一个表上定义触发器以及触发器的名在创建触发器时，必须指明在哪一个表上定义触发器以及触发器的名称、称、激发时机、激活触发器的修改语句（激发时机、激活触发器的修改语句（INSERT、UPDATE或或DELETE）。）。第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_创建创建使用使用SQL语句创建触发器语法格式为：语句创建触发器语法格式为：CREATETRIGGERtrigger_nameONtable|viewWITHENCRYPTIONFOR|AFTE