系系统统第九章第九章 存储过程与触发器存储过程与触发器v前前台台后台数据库后台数据库包含用户的操作界面。例如：登陆界面、商品入库界面、卖出商品界面等第九章第九章 存储过程与触发器存储过程与触发器v前前台台后台数据库后台数据库执行执行select命令，并将结果返回命令，并将结果返回商品查询界面根据关键词写出select 语句第九章第九章 存储过程与触发器存储过程与触发器v前前台台后台数据库后台数据库自动执行已编写好的命令，将结果返回自动执行已编写好的命令，将结果返回商品查询界面根据关键词写出调用语句第九章第九章 存储过程与触发器存储过程与触发器v存储过程综述存储过程综述v存储过程的基本操作存储过程的基本操作v创建和执行带有参数的存储过程创建和执行带有参数的存储过程v存储过程重新编译存储过程重新编译v系统存储过程和扩展存储过程系统存储过程和扩展存储过程第九章第九章 存储过程与触发器存储过程与触发器v存储过程综述存储过程综述1、存储过程概念、存储过程概念存储过程是一种数据库对象，是为了实现某个特定任务，将一组存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编译的预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调语句以一个存储单元的形式存储在服务器上，供用户调用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中以便以后调用，这样可以提高代码的执行效率。高速缓存中以便以后调用，这样可以提高代码的执行效率。存储过程同其他编程语言中的过程相似，有如下存储过程同其他编程语言中的过程相似，有如下特点特点：1）接受输入参数并以输出参数的形式将多个值返回至调用过程或）接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理。批处理。2）包含执行数据库操作（包括调用其它过程）的编程语句。）包含执行数据库操作（包括调用其它过程）的编程语句。3）向调用过程或批处理返回状态值，以表明成功或失败（以及失）向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）。败原因）。第九章第九章 存储过程与触发器存储过程与触发器v存储过程综述存储过程综述优点：优点：1、安全机制：只给用户访问存储过程的权限，而不授予用户访问表、安全机制：只给用户访问存储过程的权限，而不授予用户访问表和视图的权限。和视图的权限。2、改良了执行性能：在第一次执行后，会在、改良了执行性能：在第一次执行后，会在SQLserver的缓冲区中的缓冲区中创建查询树，以后执行无需编译。创建查询树，以后执行无需编译。3、减少网络流量：存储过程存在于服务器上，调用时，只需传递执、减少网络流量：存储过程存在于服务器上，调用时，只需传递执行存储过程的执行命令和返回结果。行存储过程的执行命令和返回结果。4、模块化的程序设计：增强了代码的可重用性，提高了开发效率。、模块化的程序设计：增强了代码的可重用性，提高了开发效率。第九章第九章 存储过程与触发器存储过程与触发器v存储过程综述存储过程综述2、存储过程类型、存储过程类型系统存储过程系统存储过程：系统存储过程存储在：系统存储过程存储在master数据库中，并以数据库中，并以sp_为前缀，主为前缀，主要用来从系统表中获取信息，为系统管理员管理要用来从系统表中获取信息，为系统管理员管理SQLServer提供帮助，为用提供帮助，为用户查看数据库对象提供方便。户查看数据库对象提供方便。本地存储过程本地存储过程：本地存储过程是用户根据需要，在自己的普通数据库中创：本地存储过程是用户根据需要，在自己的普通数据库中创建的存储过程。建的存储过程。临时存储过程临时存储过程：临时存储过程通常分为局部临时存储过程和全局临时存储：临时存储过程通常分为局部临时存储过程和全局临时存储过程。创建局部临时存储过程时，要以过程。创建局部临时存储过程时，要以“#”作为过程名称的第一个字符。创作为过程名称的第一个字符。创建全局临时存储过程时，要以建全局临时存储过程时，要以“#”作为过程名称的前两个字符。作为过程名称的前两个字符。远程存储过程远程存储过程：远程存储过程是：远程存储过程是SQLServer2000的一个传统功能，是指非的一个传统功能，是指非本地服务器上的存储过程。本地服务器上的存储过程。扩展存储过程扩展存储过程：扩展存储过程以：扩展存储过程以xp_为前缀，它是关系数据库引擎的开放式为前缀，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库数据服务层的一部分，其可以使用户在动态链接库(DLL)文件所包含的函数中文件所包含的函数中实现逻辑，从而扩展了实现逻辑，从而扩展了Transact-SQL的功能，并且可以象调用的功能，并且可以象调用Transact-SQL过程那样从过程那样从Transact-SQL语句调用这些函数。语句调用这些函数。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建创建存储过程时，需要注意下列事项：创建存储过程时，需要注意下列事项：1、只能在当前数据库中创建存储过程。、只能在当前数据库中创建存储过程。2、数据库的所有者可以创建存储过程，也可以授权其他用户创建存、数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。储过程。3、存储过程是数据库对象，其名称必须遵守标识符命名规则。、存储过程是数据库对象，其名称必须遵守标识符命名规则。4、不能将、不能将CREATEPROCEDURE语句与其它语句与其它SQL语句组合到单语句组合到单个批处理中。个批处理中。5、创建存储过程时，应指定所有输入参数和向调用过程或批处理返、创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。理以表明成功或失败的状态值。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建使用使用SQL语句创建不带参数的存储过程语法格式如下：语句创建不带参数的存储过程语法格式如下：CREATEPROCEDUREprocedure_name WITHRECOMPILE|ENCRYPTIONASsql_statement.n procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。数据库及其所有者必须唯一。RECOMPILE：SQL不会缓存该过程的计划，该过程将在运行时重新编译。不会缓存该过程的计划，该过程将在运行时重新编译。ENCRYPTION：SQLServer加密加密使用使用ENCRYPTION可防止将过程作为可防止将过程作为SQLServer复制的一部分发布。复制的一部分发布。 第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建例、在例、在STUDENT数据库中，创建一个查询存储过程数据库中，创建一个查询存储过程ST_PROC_BJ，该存储过程将返回计算机系的班级名称。其程序清单如下：该存储过程将返回计算机系的班级名称。其程序清单如下：USESTUDENTGOCREATEPROCDBO.ST_PROC_BJASSELECT班级名称班级名称FROM班级班级,系部系部WHERE系部系部.系部代码系部代码=班级班级.系部代码系部代码and系部系部.系部名称系部名称=计算机系计算机系GO第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-执行执行执行存储过程执行存储过程对存储在服务器上的存储过程，可以使用对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其命令或其名称执行它，其语法格式如下：语法格式如下：EXECUTEprocedure_namenumber如果存储过程是批处理中的第一条语句，如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略，可以使用命令可以省略，可以使用存储过程的名字执行该存储过程。存储过程的名字执行该存储过程。例：在查询分析器中执行存储过程例：在查询分析器中执行存储过程ST_PROC_BJ，其代码清单如下：，其代码清单如下：USESTUDENTEXECUTEST_PROC_BJGO第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建使用使用SQL语句创建带参数的存储过程语法格式如下：语句创建带参数的存储过程语法格式如下：CREATEPROCEDUREprocedure_namenumberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASsql_statement.n第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建其中：其中：procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。且对于数据库及其所有者必须唯一。number：该参数是可选的整数，用来对同名的过程分组，以便用一条：该参数是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起删除。例如，名为语句即可将同组的过程一起删除。例如，名为orders的应用程的应用程序使用的过程可以命名为序使用的过程可以命名为orderproc1、orderproc2等。等。DROPPROCEDUREorderproc语句将删除整个组。语句将删除整个组。parameter：存储过程中的输入和输出参数。：存储过程中的输入和输出参数。data_type：参数的数据类型。：参数的数据类型。Default：指参数的默认值，必须是常量或：指参数的默认值，必须是常量或NULL。如果定义了默认值，不必。如果定义了默认值，不必指定该参数的值也可执行过程。指定该参数的值也可执行过程。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建其中：其中：OUTPUT：表明参数是返回参数。该选项的值可以返回给：表明参数是返回参数。该选项的值可以返回给EXECUTE。使用。使用OUTPUT参数可将信息返回给调用过程。参数可将信息返回给调用过程。RECOMPILE：表明：表明SQLServer不保存存储过程的计划，该过程将在运行时不保存存储过程的计划，该过程将在运行时重新编译。重新编译。ENCRYPTION：表示：表示SQLServer加密加密syscomments表中包含表中包含CREATEPROCEDURE语句文本的条目。语句文本的条目。sql_statement：指存储过程中的任意数目和类型的：指存储过程中的任意数目和类型的Transact-SQL语句。语句。在存储过程中使用参数，可以扩展存储过程的功能。使用输入参数在存储过程中使用参数，可以扩展存储过程的功能。使用输入参数，可以将外部信息传入到存储过程；使用输出参数，可以将存储过程，可以将外部信息传入到存储过程；使用输出参数，可以将存储过程内的信息传出到外部内的信息传出到外部。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建例：例：在在STUDENT数据库中，建立一个名为数据库中，建立一个名为XIBU_INFOR的存储过程，的存储过程，它带有一个参数，用于接受系部代码，显示该系部名称和系主任信息。它带有一个参数，用于接受系部代码，显示该系部名称和系主任信息。其程序清单如下：其程序清单如下：USESTUDENTGOCREATEPROCEDUREXIBU_INFOR系部代码系部代码CHAR(2)ASSELECT系部名称系部名称,系主任系主任FROM系部系部WHERE系部代码系部代码=系部代码系部代码GO第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-执行执行执行待参数的存储过程执行待参数的存储过程对存储在服务器上的存储过程，可以使用对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其命令或其名称执行它，其语法格式如下：语法格式如下：EXECUTEreturn_status=procedure_namenumberparameter=value|variableOUTPUT|DEFAULT,.n其中：其中：如果存储过程是批处理中的第一条语句，如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略，可以使命令可以省略，可以使用存储过程的名字执行该存储过程。用存储过程的名字执行该存储过程。return_status：是一个可选的整型变量，用来保存存储过程的返回状态。：是一个可选的整型变量，用来保存存储过程的返回状态。parameter：存储过程的参数。：存储过程的参数。例如：执行存储过程：例如：执行存储过程：EXECXIBU_INFOR01第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建例：例：usepubsgocreateproceduretitle_sumtitlevarchar(40)=%,summoneyoutputasselecttitlename=titlefromtitleswheretitleliketitleselectsum=sum(price)fromtitleswheretitleliketitlego第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-执行执行执行：执行：declaretotalcostmoneyexecutetitle_sumthe%,totalcostoutputiftotalcost=200beginselectthetotalcostofthesetitleis$+rtrim(cast(totalcostasvarchar(20)注意：注意：必须在必须在createprocedure和和execute语句中都指定语句中都指定output关键字。关键字。如果如果output关键字在存储过程执行过程中被忽略了，存储过程仍然执关键字在存储过程执行过程中被忽略了，存储过程仍然执行，但是他将会产生一个错误状态。行，但是他将会产生一个错误状态。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-创建创建使用企业管理器使用企业管理器例：在例：在STUDENT数据库中，创建一个名称为数据库中，创建一个名称为ST_CHAXUN_01的存储过程，该的存储过程，该存储过程返回计算机系学生的姓名、性别和年龄信息。其操作步骤如下：存储过程返回计算机系学生的姓名、性别和年龄信息。其操作步骤如下：1）打开企业管理器，展开控制台目录，依次展开服务器组、服务器、数据库）打开企业管理器，展开控制台目录，依次展开服务器组、服务器、数据库节点。节点。2）单击相应的数据库（这里我们选择）单击相应的数据库（这里我们选择student数据库），在其右边的详细窗格数据库），在其右边的详细窗格中右击中右击“存储过程存储过程”图标，在弹出的快捷菜单中选择图标，在弹出的快捷菜单中选择“新建存储过程新建存储过程”命令。命令。3）执行）执行“新建存储过程新建存储过程”命令，打开命令，打开“存储过程属性存储过程属性”对话框。在其文本框对话框。在其文本框中首先输入所有者和存储过程名称，例如用所有者中首先输入所有者和存储过程名称，例如用所有者DBO替换替换OWNER，用过程，用过程名名ST_CHAXUN_01替换替换PROCEDURENAME。4）在文本框的第二行输入存储过程文本，根据题意输入如下语句：）在文本框的第二行输入存储过程文本，根据题意输入如下语句：SELECT姓名姓名,性别性别,YEAR(GETDATE()-YEAR(出生日期出生日期)AS年龄年龄FROM学生学生WHERE系部代码系部代码=015）输入完成后，单击）输入完成后，单击“语法检查语法检查”按钮，检查语法是否正确，然后单击按钮，检查语法是否正确，然后单击“确确定定”。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-查看查看1、使用企业管理器查看存储过程的步骤、使用企业管理器查看存储过程的步骤为：为：1）打开企业管理器，展开控制台目录，依）打开企业管理器，展开控制台目录，依次展开服务器组、服务器、数据库节点。次展开服务器组、服务器、数据库节点。2）单击相应的数据库（这里我们选择）单击相应的数据库（这里我们选择student数据库），选择数据库），选择“存储过程存储过程”节点，节点，在右边的列表中显示出当前数据库中所有的在右边的列表中显示出当前数据库中所有的存储过程。存储过程。3）选择需要查看的存储过程右击，例如）选择需要查看的存储过程右击，例如“ST_PROC_BJ”，在弹出的快捷菜单中选择，在弹出的快捷菜单中选择“属性属性”命令，打开命令，打开“存储过程属性存储过程属性”对话对话框。框。4）在）在“属性属性”对话框中，既可以查看过程对话框中，既可以查看过程定义信息，又可以在文本框中对存储过程的定义信息，又可以在文本框中对存储过程的定义进行修改。修改后，可以单击定义进行修改。修改后，可以单击“应用应用”或或“确定确定”按钮，保存修改。按钮，保存修改。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-查看查看2、使用系统存储过程查看存储过程信息、使用系统存储过程查看存储过程信息在在SQLServer中，根据不同需要，可以使用中，根据不同需要，可以使用sp_helptext、sp_depends、sp_help等系统存储过程来查看存储过程的不同信息。具体作用和语法如下：等系统存储过程来查看存储过程的不同信息。具体作用和语法如下：sp_helptext查看存储过程的文本信息，其语法格式为：查看存储过程的文本信息，其语法格式为：sp_helptext存储过程名存储过程名sp_depends查看存储过程的相关性，其语法格式为：查看存储过程的相关性，其语法格式为：sp_depends存储过程名存储过程名sp_help查看存储过程的一般信息，其语法格式为：查看存储过程的一般信息，其语法格式为：sp_help存储过程名存储过程名例：使用有关系统过程查看例：使用有关系统过程查看STUDENT数据库中名为数据库中名为ST_PROC_BJ的存储过程的存储过程的定义、相关性以及一般信息。其程序清单如下：的定义、相关性以及一般信息。其程序清单如下：USESTUDENTGOEXECSP_HELPTEXTST_PROC_BJEXECSP_DEPENDSST_PROC_BJEXECSP_HELPST_PROC_BJGO第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-修改修改下面介绍下面介绍SQL语句，其语法格式为：语句，其语法格式为：ALTERPROCEDUREprocedure_namenumberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n例：修改存储过程例：修改存储过程ST_PROC_BJ，使该存储过程返回经济管理系的班级名称。，使该存储过程返回经济管理系的班级名称。其程序清单如下：其程序清单如下：ALTERPROCDBO.ST_PROC_BJASSELECT班级名称班级名称FROM班级班级,系部系部WHERE系部系部.系部代码系部代码=班级班级.系部代码系部代码and系部系部.系部名称系部名称=经济管理系经济管理系GO第九章第九章 存储过程与触发器存储过程与触发器v存储过程的基本操作存储过程的基本操作-删除删除1、使用企业管理器删除存储过程、使用企业管理器删除存储过程在企业管理器中，右击要删除的存储过程，从弹出的快捷菜单中选在企业管理器中，右击要删除的存储过程，从弹出的快捷菜单中选择择“删除删除”命令，将弹出命令，将弹出“移除对象移除对象”对话框。在此对话框中，单击对话框。在此对话框中，单击“全部除去全部除去”按钮，删除该存储过程。按钮，删除该存储过程。2、使用、使用DROPPROCEDURE语句删除存储过程语句删除存储过程DROPPROCEDURE语句可以一次从当前数据库中将一个或多个语句可以一次从当前数据库中将一个或多个存储过程或过程组删除，其语法格式如下：存储过程或过程组删除，其语法格式如下：DROPPROCEDURE存储过程名称存储过程名称,n例：删除存储过程例：删除存储过程ST_CHAXUN_01，其程序清单如下：，其程序清单如下：USESTUDENTGODROPPROCEDUREST_CHAXUN_01GO第九章第九章 存储过程与触发器存储过程与触发器v存储过程的重新编译存储过程的重新编译存储过程第一次执行后，其被编译的代码将驻留在高速缓存中，存储过程第一次执行后，其被编译的代码将驻留在高速缓存中，当用户再次执行该存储过程时，当用户再次执行该存储过程时，SQLServer将其从缓存中调出执行。将其从缓存中调出执行。有时，在我们使用了一次存储过程后，可能会因为某些原因，必须向有时，在我们使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，如果调用缓存中的存储过程，需要对它进行重新编译，使构。这时，如果调用缓存中的存储过程，需要对它进行重新编译，使存储过程能够得到优化。存储过程能够得到优化。SQLServer提供三种重新编译存储过程的方提供三种重新编译存储过程的方法：法：1、在建立存储过程时设定重新编译、在建立存储过程时设定重新编译创建存储过程时，在其定义中指定创建存储过程时，在其定义中指定WITHRECOMPILE选项，使选项，使SQLServer在每次执行存储过程时都要重新编译。在每次执行存储过程时都要重新编译。第九章第九章 存储过程与触发器存储过程与触发器v存储过程的重新编译存储过程的重新编译2、在执行存储过程时设定重编译、在执行存储过程时设定重编译在执行存储过程时指定在执行存储过程时指定WITHRECOMPILE选项，可强制对存储过程选项，可强制对存储过程进行重新编译。其语法格式如下：进行重新编译。其语法格式如下：EXECUTEprocedure_nameWITHRECOMPILE 3、通过使用系统存储过程设定重编译、通过使用系统存储过程设定重编译系统存储过程系统存储过程sp_recompile强制在下次运行存储过程时进行重新编译。强制在下次运行存储过程时进行重新编译。其语法格式为：其语法格式为：EXECsp_recompileOBJECT其中其中OBJECT是当前数据库中的存储过程、触发器、表或视图的名称。是当前数据库中的存储过程、触发器、表或视图的名称。如果如果object是存储过程或触发器的名称，那么该存储过程或触发器将在是存储过程或触发器的名称，那么该存储过程或触发器将在下次运行时重新编译。下次运行时重新编译。第九章第九章 存储过程与触发器存储过程与触发器v系统存储过程和扩展存储过程系统存储过程和扩展存储过程在在SQLServer中有两类重要的存储过程：系统存储过程和扩展存中有两类重要的存储过程：系统存储过程和扩展存储过程。这些存储过程为用户管理数据库、获取系统信息、查看系统储过程。这些存储过程为用户管理数据库、获取系统信息、查看系统对象提供了很大的帮助。对象提供了很大的帮助。 系统存储过程系统存储过程在在SQLServer中存在两百多个系统存储过程，这些系统存储过程的使用，中存在两百多个系统存储过程，这些系统存储过程的使用，使用户很容易的管理使用户很容易的管理SQLServer的数据库。在安装的数据库。在安装SQLServer数据库系统时，数据库系统时，系统存储过程被系统安装在系统存储过程被系统安装在master数据库中，并且初始状态只有系统管理员数据库中，并且初始状态只有系统管理员拥有使用权。所有的系统存储过程名称都是以拥有使用权。所有的系统存储过程名称都是以sp_开头。开头。在使用以在使用以sp_开头的系统存储过程时，开头的系统存储过程时，SQLServer首先在当前数据库中寻首先在当前数据库中寻找，如果没有找到，则再到找，如果没有找到，则再到master数据库中查找并执行。虽然存储在数据库中查找并执行。虽然存储在master数据库中，但是绝大部分系统存储过程可以在任何数据库中执行，而且在使数据库中，但是绝大部分系统存储过程可以在任何数据库中执行，而且在使用时不用在名称前加数据库名。当系统存储过程的参数是保留字或对象名时，用时不用在名称前加数据库名。当系统存储过程的参数是保留字或对象名时，在使用存储过程时候，作为参数的在使用存储过程时候，作为参数的“对象名或保留字对象名或保留字”必须用单引号括起来。必须用单引号括起来。 第九章第九章 存储过程与触发器存储过程与触发器v系统存储过程和扩展存储过程系统存储过程和扩展存储过程例：利用sp_addgroup命令在当前数据库中建立一个角色user_group： USE master GO EXEC sp_addgroup user_group例：利用sp_addlogin命令建立一个登录用户名为user01： USE master GO EXEC sp_addlogin user01运行后提示以创建。需要注意的是，在没有指定用户密码和默认数据库的时候，创建的用户默认数据库是master，默认的密码是NULL。例：利用sp_addtype创建新的用户自定义数据类型user_date，该类型为datetime数据类型： EXEC sp_addtype user_date ，datetime运行结果为类型已添加。例：使用sp_monitor显示CPU、I/O的使用信息： USE MASTER EXEC sp_monitor GO第九章第九章 存储过程与触发器存储过程与触发器v系统存储过程和扩展存储过程系统存储过程和扩展存储过程扩展存储过程扩展存储过程扩展存储过程是允许用户使用一种编程语言（例如扩展存储过程是允许用户使用一种编程语言（例如C语言）创建的语言）创建的应用程序，程序中使用应用程序，程序中使用SQLServer开放数据服务的开放数据服务的API函数，它们直接函数，它们直接可以在可以在SQLServer地址空间中运行。用户可以象使用普通的存储过程一地址空间中运行。用户可以象使用普通的存储过程一样使用它，同样也可以将参数传给它并返回结果和状态值。扩展存储过样使用它，同样也可以将参数传给它并返回结果和状态值。扩展存储过程编写好后，可以由系统管理员在程编写好后，可以由系统管理员在SQLServer中注册登记，然后将其执中注册登记，然后将其执行权限授予其他用户。扩展存储过程只能存储在行权限授予其他用户。扩展存储过程只能存储在master数据库中。下面数据库中。下面通过几个例子，介绍扩展存储过程的创建和应用实例。通过几个例子，介绍扩展存储过程的创建和应用实例。 第九章第九章 存储过程与触发器存储过程与触发器v系统存储过程和扩展存储过程系统存储过程和扩展存储过程扩展存储过程扩展存储过程例：使用例：使用sp_addextendedproc存储过程将一个编写好的扩展存储过程存储过程将一个编写好的扩展存储过程xp_userprint.dll注册注册到到SQLServer中。中。执行代码如下：执行代码如下：EXECsp_addextendedprocxp_userprint,xp_userprint.dll其中：其中：sp_addextendedproc：为系统存储过程。：为系统存储过程。xp_userprint：为扩展存储过程在：为扩展存储过程在SQLServer中的注册名。中的注册名。xp_userprint.dll：为用某种语言编写的扩展存储过程动态链接库。：为用某种语言编写的扩展存储过程动态链接库。例：使用扩展存储过程例：使用扩展存储过程xp_dirtree返回本地操作系统的系统目录返回本地操作系统的系统目录c:winnt目录树。目录树。执行代码如下：执行代码如下：EXECxp_dirtreec:winntGO执行结果返回执行结果返回c:winnt目录树。目录树。第九章第九章 存储过程与触发器存储过程与触发器v系统存储过程和扩展存储过程系统存储过程和扩展存储过程扩展存储过程扩展存储过程例：利用扩展存储过程例：利用扩展存储过程xp_cmdshell为一个操作系统外壳执行指定命令串，并作为文本返回为一个操作系统外壳执行指定命令串，并作为文本返回任何输出。任何输出。执行代码：执行代码：EXECmasterxp_cmdshell“dir*.exe”GO执行结果返回系统目录下的文件内容文本信息。执行结果返回系统目录下的文件内容文本信息。例：利用扩展存储过程实现远程备份数据库。假设例：利用扩展存储过程实现远程备份数据库。假设Windows2000Sever服务器计算机名为服务器计算机名为yang，本地域为，本地域为Domain域，域，SQLServer数据库名称为数据库名称为yang，系统管理员账号为，系统管理员账号为sa，密码为，密码为888，需要备份的数据库为，需要备份的数据库为student。实现此操作的代码如下：。实现此操作的代码如下：EXECxp_cmdshell“etsharebaktest=e:baktest”GOmaster.xp_cmdshell“netuseyangbaktest888/user:domainsa”GObackupdatabasestudenttodisk=“yangbakteststudent.bak”GOEXECxp_cmmdshell“netsharebaktest/delete”GO第九章第九章 存储过程与触发器存储过程与触发器v触发器综述触发器综述v触发器的基本操作触发器的基本操作v创建触发器创建触发器v查看触发器查看触发器v修改触发器修改触发器v禁止和启用触发器禁止和启用触发器v删除触发器删除触发器v嵌套触发器嵌套触发器第九章第九章 存储过程与触发器存储过程与触发器v触发器综述触发器综述1、触发器概念、触发器概念触发器是一种特殊类型的存储过程，不由用户直接调用，而且可以包触发器是一种特殊类型的存储过程，不由用户直接调用，而且可以包含复杂的含复杂的SQL语句。它们主要用于强制复杂的业务规则或要求。触发语句。它们主要用于强制复杂的业务规则或要求。触发器还有助于强制器还有助于强制引用完整性引用完整性，以便在添加、更新或删除表中的行时保，以便在添加、更新或删除表中的行时保留表之间已定义的关系留表之间已定义的关系。可以完成存储过程能完成的功能，但是它具可以完成存储过程能完成的功能，但是它具有自己显著的的特点：有自己显著的的特点：1）它与表紧密相连，可以看作表定义的一部分；）它与表紧密相连，可以看作表定义的一部分；2）它不能通过名称被直接调用，更不允许带参数，而是当用户对）它不能通过名称被直接调用，更不允许带参数，而是当用户对表中的数据进行修改时，自动执行；表中的数据进行修改时，自动执行；3）它可以用于）它可以用于SQLServer约束、默认值和规则的完整性检查，实约束、默认值和规则的完整性检查，实施更为复杂的数据完整性约束。施更为复杂的数据完整性约束。第九章第九章 存储过程与触发器存储过程与触发器v触发器综述触发器综述2、触发器的优点、触发器的优点触发器包含复杂的处理逻辑，能够实现复杂的数据完整性约束。触发器包含复杂的处理逻辑，能够实现复杂的数据完整性约束。同其他约束相比，它主要有以下优点：同其他约束相比，它主要有以下优点：1）触发器自动执行）触发器自动执行在对表的数据作了任何修改（比如手工输入或者应用程序采取的在对表的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。操作）之后立即被激活。2）触发器能够对数据库中的相关表实现级联更改）触发器能够对数据库中的相关表实现级联更改触发器是基于一个表创建的，但是可以针对多个表进行操作，实触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。例如，在学生数据库中，可以在产品现数据库中相关表的级联更改。例如，在学生数据库中，可以在产品表的产品编号字段上建立一个插入触发器，当对产品表增加记录时，表的产品编号字段上建立一个插入触发器，当对产品表增加记录时，在产品销售表的产品编号上自动插入编号值。在产品销售表的产品编号上自动插入编号值。第九章第九章 存储过程与触发器存储过程与触发器v触发器综述触发器综述2、触发器的优点、触发器的优点3）触发器可以实现比）触发器可以实现比CHECK约束更为复杂的数据完整性约束约束更为复杂的数据完整性约束在数据库中为了实现数据完整性约束，可以使用在数据库中为了实现数据完整性约束，可以使用CHECK约束或触发约束或触发器。器。CHECK约束不允许引用其它表中的列来完成检查工作，而触发约束不允许引用其它表中的列来完成检查工作，而触发器可以引用其它表中的列。例如，在器可以引用其它表中的列。例如，在STUDENT数据库中，向学生表数据库中，向学生表中插入记录时，当输入系部代码时，必须先检查系部表中是否存在该中插入记录时，当输入系部代码时，必须先检查系部表中是否存在该系。这只能通过触发器实现，而不能通过系。这只能通过触发器实现，而不能通过CHECK约束完成。约束完成。4）触发器可以评估数据修改前后的表状态，并根据其差异采取对策。）触发器可以评估数据修改前后的表状态，并根据其差异采取对策。5）一个表中可以存在多个同类触发器（）一个表中可以存在多个同类触发器（INSERT、UPDATE或或DELETE），对于同一个修改语句可以有多个不同的对策以响应。），对于同一个修改语句可以有多个不同的对策以响应。第九章第九章 存储过程与触发器存储过程与触发器v触发器综述触发器综述3、触发器的种类、触发器的种类SQLServer2000按触发被被激活的时机可以分为两种类型：按触发被被激活的时机可以分为两种类型：AFTER触发器和触发器和INSTEADOF触发器。触发器。AFTER触发器触发器又称为后触发器，该类触发器是在引起触发器执行又称为后触发器，该类触发器是在引起触发器执行的修改语句成功完成之后执行。如果修改语句因错误（如违反约束或的修改语句成功完成之后执行。如果修改语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。此类触发器只能定义在表上，语法错误）而失败，触发器将不会执行。此类触发器只能定义在表上，不能创建在视图上。可以为每个触发操作（不能创建在视图上。可以为每个触发操作（INSERT、UPDATE或或DELETE）创建多个）创建多个AFTER触发器。触发器。 INSTEADOF触发器触发器又称为替代触发器，当引起触发器执行的修又称为替代触发器，当引起触发器执行的修改语句停止执行时，该类触发器代替触发操作执行。该类触发器既可改语句停止执行时，该类触发器代替触发操作执行。该类触发器既可在表上定义，也可在视图上定义。对于每个触发操作（在表上定义，也可在视图上定义。对于每个触发操作（INSERT、UPDATE和和DELETE）只能定义一个）只能定义一个INSTEADOF触发器。触发器。第九章第九章 存储过程与触发器存储过程与触发器v触发器触发器在进行触发器的基本操作之前，介绍两张特殊的临时表，分别是在进行触发器的基本操作之前，介绍两张特殊的临时表，分别是inserted表和表和deleted表。这两张表都存在于高速缓存中。用户可以使表。这两张表都存在于高速缓存中。用户可以使用这两张临时表来检测某些修改操作所产生的效果。例如，可以使用用这两张临时表来检测某些修改操作所产生的效果。例如，可以使用SELECT语句来检查语句来检查INSERT和和UPDATE语句执行的插入操作是否语句执行的插入操作是否成功，触发器是否被这些语句触发等。但是不允许用户直接修改成功，触发器是否被这些语句触发等。但是不允许用户直接修改inserted表和表和deleted表中数据。表中数据。第九章第九章 存储过程与触发器存储过程与触发器v触发器触发器deleted表中存储着被表中存储着被DELETE和和UPDATE语句影响的旧数据行。在语句影响的旧数据行。在执行执行DELETE和和UPDATE语句过程中，指定的数据行被用户从基本表语句过程中，指定的数据行被用户从基本表中删除，然后转移到了中删除，然后转移到了delete表中。一般来说，在基本表中表中。一般来说，在基本表中delete表中表中不会存在有相同的数据行。不会存在有相同的数据行。inserted表中存储着被表中存储着被INSERT和和UPDATE语句影响的新的数据行。语句影响的新的数据行。当用户执行当用户执行INSERT和和UPDATE语句时，新的数据行被添加到语句时，新的数据行被添加到insert表表中，同时这些数据行的备份被复制到中，同时这些数据行的备份被复制到inserted临时表中。临时表中。一个典型的一个典型的UPDATE事务实际上是由两个操作组成。首先，旧的数事务实际上是由两个操作组成。首先，旧的数据行从基本表中转移到据行从基本表中转移到delete表中，前提是这个过程没有出错；紧接表中，前提是这个过程没有出错；紧接着将新的数据行同时插入基本表和着将新的数据行同时插入基本表和insert表。表。第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_创建创建在创建触发器前，必须注意以下几点：在创建触发器前，必须注意以下几点：1、CREATETRIGGER语句必须是批处理中的第一条语句。语句必须是批处理中的第一条语句。2、只能在当前数据库中创建触发器，名称必须遵循标识符的命名规则。、只能在当前数据库中创建触发器，名称必须遵循标识符的命名规则。3、表的所有者具有创建触发器的默认权限，不能将该权限转给其他用户。、表的所有者具有创建触发器的默认权限，不能将该权限转给其他用户。4、不能在临时表或系统表上创建触发器，但是触发器可以引用临时表，但、不能在临时表或系统表上创建触发器，但是触发器可以引用临时表，但是不能引用系统表。是不能引用系统表。5、尽管、尽管TRUNCATETABLE语句类似于没有语句类似于没有WHERE子句（用于删除行）子句（用于删除行）的的DELETE语句，但由于该语句不被记入日志，所以它不会引发语句，但由于该语句不被记入日志，所以它不会引发DELETE触触发器。发器。6、WRITETEXT语句不会引发语句不会引发INSERT或或UPDATE触发器。触发器。在创建触发器时，必须指明在哪一个表上定义触发器以及触发器的名在创建触发器时，必须指明在哪一个表上定义触发器以及触发器的名称、称、激发时机、激活触发器的修改语句（激发时机、激活触发器的修改语句（INSERT、UPDATE或或DELETE）。）。第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_创建创建使用使用SQL语句创建触发器语法格式为：语句创建触发器语法格式为：CREATETRIGGERtrigger_nameONtable|viewWITHENCRYPTIONFOR|AFTER|INSTEADOFINSERT,DELETE,UPDATENOTFORREPLICATIONASIFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_statement.n第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_创建创建其中：其中：trigger_name：触发器名称，其必须符合命名标识规则，并且在当前数据库：触发器名称，其必须符合命名标识规则，并且在当前数据库中唯一。中唯一。table|view：被定义触发器的表或视图。：被定义触发器的表或视图。WITHENCRYPTION：对：对syscomments表中含表中含CREATETRIGGER语句语句文本进行加密。文本进行加密。AFTER：默认的触发器类型，后触发器。此类型触发器不能在视图上定义。：默认的触发器类型，后触发器。此类型触发器不能在视图上定义。INSTEADOF：表示建立替代类型的触发器。：表示建立替代类型的触发器。NOTFORREPLICATION：表示当复制进程更改触发器所涉及的表时，不：表示当复制进程更改触发器所涉及的表时，不应执行该触发器。应执行该触发器。IFUPDATE：指定对表中字段进行增加或修改内容时起作用，不能用于删：指定对表中字段进行增加或修改内容时起作用，不能用于删除操作。除操作。sql_statement：定义触发器被触发后，将执行的：定义触发器被触发后，将执行的SQL语句。语句。第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_创建创建例：在例：在STUDENT数据库中，为产品表中产品编号建立一个名为数据库中，为产品表中产品编号建立一个名为del_xiaoshao的的DELETE触发器，其作用是当删除产品表中的记录时，触发器，其作用是当删除产品表中的记录时，同时删除产品销售表中与产品表相关的记录。其程序清单如下：同时删除产品销售表中与产品表相关的记录。其程序清单如下：USESTUDENTGOCREATETRIGGERdel_xiaoshouONdbo.产品产品FORDELETEASDELETE产品销售产品销售WHERE产品编号产品编号IN(SELECT产品编号产品编号FROMDELETED)GO第九章第九章 存储过程与触发器存储过程与触发器v触发器触发器例：例：USENorthWindGOCREATETRIGGERTR_DELONsuppliersFORDELETEASIFrowcount=0RETURNDELETEPRODUCTSFROMdeletedd,suppllierssWHEREd.supplierid=s.supplieridIFERROR!=0BEGINROLLBACKTRANRETURNENDRETURNGO这这个个例例子子首首先先检检测测数数据据行行的的数数量量，如如果果数数量量为为0，则则触触发发器器不不执执行行任任何何操操作作。如如果果删删除除数数据据行行的的数数量量大大于于0，则则表表示示删删除除操操作作成成功功，触触发发器器根根据据deleted表表 中中 的的 数数 据据 ， 将将suppliers表表中中的的相相关数据也删除掉。关数据也删除掉。第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_创建创建使用企业管理器创建触发器具体创建步使用企业管理器创建触发器具体创建步骤：骤：1）在企业管理器，单击相应的数据）在企业管理器，单击相应的数据库选择库选择“表表”节点，在其右边的详细窗节点，在其右边的详细窗格中显示出当前数据库中所有的表。格中显示出当前数据库中所有的表。2）选择需要创建触发器的表右击，）选择需要创建触发器的表右击，在弹出的快捷菜单中选择在弹出的快捷菜单中选择“所有任务所有任务”，再选择，再选择“管理触发器管理触发器”命令，打开命令，打开“触发器属性触发器属性”对话框。对话框。3）在其名称框中选择）在其名称框中选择“新建新建”，在，在文本框中输入触发器文本，然后单击文本框中输入触发器文本，然后单击“检查语法检查语法”按钮检查语句是否正确。按钮检查语句是否正确。4）单击）单击“应用应用”按钮，完成触发器按钮，完成触发器的创建。的创建。第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_查看查看1、使用系统存储过程查看触发器信息、使用系统存储过程查看触发器信息使用使用sp_help查看触发器的一般信息查看触发器的一般信息使用使用sp_helptext查看未加密的触发器的定义信息查看未加密的触发器的定义信息使用使用sp_depends查看触发器的依赖关系查看触发器的依赖关系专门查看触发器信息的系统存储过程专门查看触发器信息的系统存储过程sp_helptrigger，语法如下：语法如下：sp_helptrigger表名，表名，INSERT,DELETE,UPDATE例：查看产品表上存在的触发器的信息。其程序清单如下：例：查看产品表上存在的触发器的信息。其程序清单如下：EXECsp_helptrigger产品产品GO2、使用企业管理器查看触发器信息、使用企业管理器查看触发器信息在企业管理器中，选择需要的数据库，然后选择表右击，在弹出的快捷菜在企业管理器中，选择需要的数据库，然后选择表右击，在弹出的快捷菜单中选择单中选择“所有任务所有任务”，再选择，再选择“管理触发器管理触发器”命令，打开命令，打开“触发器属性触发器属性”对话框，在该对话框中，可以从名称列表框中查看当前表的所有触发器名称对话框，在该对话框中，可以从名称列表框中查看当前表的所有触发器名称及其所有者，当需要查看触发器定义信息时，只要选择该触发器名称，其文及其所有者，当需要查看触发器定义信息时，只要选择该触发器名称，其文本内容就显示在本内容就显示在“触发器属性触发器属性”对话框的文本框中。对话框的文本框中。第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_修改修改1、使用系统存储过程修改触发器名称、使用系统存储过程修改触发器名称对触发器进行重命名，可以使用系统存储过程对触发器进行重命名，可以使用系统存储过程sp_rename来完成，来完成，语法格式如下：语法格式如下：EXECUTEsp_rename触发器原名触发器原名,触发器新名触发器新名2、使用企业管理器修改触发器文本、使用企业管理器修改触发器文本使用企业管理器修改触发器的操作步骤与创建触发器相似，只不使用企业管理器修改触发器的操作步骤与创建触发器相似，只不过在打开过在打开“触发器属性触发器属性”对话框后，从名称对话框中选择需要修改的对话框后，从名称对话框中选择需要修改的触发器，然后对文本中的触发器，然后对文本中的SQL语句进行修改即可。修改完后，使用语句进行修改即可。修改完后，使用“检查语法检查语法”选项来验证语法是否正确。最后，单击选项来验证语法是否正确。最后，单击“确定确定”按钮，完按钮，完成触发其的修改。成触发其的修改。第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_修改修改3、使用、使用SQL语句修改触发器，语法格式如下：语句修改触发器，语法格式如下：ALTERTRIGGERtrigger_nameON(table|view)WITHENCRYPTION(FOR|AFTER|INSTEADOF)DELETE,INSERT,UPDATENOTFORREPLICATIONASsql_statement.nIFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_statement.n其中的参数与创建触发器语句中的参数相同。其中的参数与创建触发器语句中的参数相同。第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_修改修改例：在例：在STUDENT数据库中，修改产品表的触发器数据库中，修改产品表的触发器del_xiaoshao，使其在实现，使其在实现级联删除时，显示一条语句级联删除时，显示一条语句“产品销售表中相应记录也被删除产品销售表中相应记录也被删除”。程序如下：。程序如下：USESTUDENTGOALTERTRIGGERdel_xiaoshouONdbo.产品产品FORDELETEASBEGINDELETE产品销售产品销售WHERE产品编号产品编号IN(SELECT产品编号产品编号FROMDELETED)PRINT产品销售表中相应记录也被删除产品销售表中相应记录也被删除ENDGO例：删除产品表中产品编号为例：删除产品表中产品编号为“0001”的记录，观察触发器的记录，观察触发器del_xiaoshao的作的作用。其程序清单如下：用。其程序清单如下：DELETEFROM产品产品WHERE产品编号产品编号=0001GO 第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_禁止和启动禁止和启动针对某个表创建的触发器，可以根据需要，禁止或启用其执行。针对某个表创建的触发器，可以根据需要，禁止或启用其执行。禁止触发器或启用触发器执行只能在查询分析器中进行，其语法格式禁止触发器或启用触发器执行只能在查询分析器中进行，其语法格式为：为：ALTERTABLE表名表名ENABLE|DISABLE触发器名称触发器名称其中：其中：ENABLE：该选项为启用触发器：该选项为启用触发器DISABLE：该选项为禁用触发器：该选项为禁用触发器第九章第九章 存储过程与触发器存储过程与触发器v触发器的基本操作触发器的基本操作_删除删除当不再需要某个触发器时，可以将其删除。只有触发器的所有者当不再需要某个触发器时，可以将其删除。只有触发器的所有者才有权删除触发器。可以使用下面的方法将触发器删除：才有权删除触发器。可以使用下面的方法将触发器删除：1、使用企业管理删除触发器，其步骤为：、使用企业管理删除触发器，其步骤为：在企业管理器中，展开控制台目录，依次展开服务器组、服务器、在企业管理器中，展开控制台目录，依次展开服务器组、服务器、数据库节点，选择数据库，选择表右击，在弹出的快捷菜单中选择数据库节点，选择数据库，选择表右击，在弹出的快捷菜单中选择“所有任务所有任务”，再选择，再选择“管理触发器管理触发器”命令，打开命令，打开“触发器属性触发器属性”对话对话框，从名称下拉列表框中选择要删除的触发器，单击框，从名称下拉列表框中选择要删除的触发器，单击“删除删除”按钮，按钮，将触发器删除。将触发器删除。2、使用、使用SQL语句删除触发器语句删除触发器删除一个或多个触发器，可以使用删除一个或多个触发器，可以使用DROPTRIGGER语句，语法如下：语句，语法如下：DROPTRIGGER触发器名称触发器名称,.n3、删除表同时删除触发器、删除表同时删除触发器当某个表被删除后，该表上的所有触发器将同时被删除，但是删当某个表被删除后，该表上的所有触发器将同时被删除，但是删除触发器不会对表中数据有影响。除触发器不会对表中数据有影响。第九章第九章 存储过程与触发器存储过程与触发器v嵌套触发器嵌套触发器在在触触发发器器中中可可以以包包含含影影响响另另外外一一个个表表的的INSERT、UPDATE或或者者DELETE语语句句。这这就就是是嵌嵌套套触触发发器器，具具体体来来说说就就是是，如如果果表表A上上的的触触发发器器在在执执行行时时引引发发了了表表B上上的的触触发发器器，而而表表B上上的的触触发发器器又又激激活活了了表表C上上的的触触发发器器，表表C上上的的触触发发器器又又激激活活了了表表D上上的的触触发发器器，所所有有触触发发器器依依次次触触发发。这这些些触触发发器器不不会会形形成成无无限限循循环环，SQLServer规规定定触触发发器器最最多多可可嵌嵌套套至至32层层。如如果果允允许许使使用用嵌嵌套套触触发发器器，且且链链中中的的一一个个触触发发器器开开始始一一个个无无限限循循环环，如如果果超超出出嵌嵌套套级级，触触发发器器将将被被终终止止执执行行。正正确确的的使使用用嵌嵌套套触触发发器器，可可以以执执行行一一些些有有用用的的日日常常工工作作，但但是是嵌嵌套套触触发发器器比比较较复复杂杂，使使用用时时要要注注意意技技巧巧，比比如如，由由于于触触发发器器在在事事务务中中执执行行，如如果果在在一一系系列列嵌嵌套套触触发发器器的的任任意意层层中中发发生生错错误误，则则整整个个事事务务都都将将取取消消，且且所所有有的的数数据据修修改改都都将将回回滚滚。一一般般情情况况下下，在在触触发发器器中中包包含含PRINT语语句，用以确定错误发生的位置。句，用以确定错误发生的位置。第九章第九章 存储过程与触发器存储过程与触发器v嵌套触发器嵌套触发器设置是否嵌套的方法：设置是否嵌套的方法：1、使用系统存储过程改变嵌套使用系统存储过程改变嵌套使用使用sp_config系统存储过程设置是否允许嵌套的语法格式如下：系统存储过程设置是否允许嵌套的语法格式如下：EXECsp_configurenestedtrigger,0|1其中，如果设置为其中，如果设置为0，则允许嵌套，设置为，则允许嵌套，设置为1，禁止嵌套，禁止嵌套2、使用企业管理器设置嵌套使用企业管理器设置嵌套1）在企业管理器中，展开控制台目录，展开服务器组，选择服务）在企业管理器中，展开控制台目录，展开服务器组，选择服务器。器。2）选择需要修改的服务器右击，在弹出的快捷菜单中选择）选择需要修改的服务器右击，在弹出的快捷菜单中选择“属性属性”，打开，打开“SQLServer属性配置属性配置”对话框。对话框。3）在对话框中选择）在对话框中选择“服务器设置服务器设置”标签卡，如图标签卡，如图9.2所示。在所示。在“服服务器行为务器行为”选项中通过勾选复选框来设置是否允许使用嵌套触发器。选项中通过勾选复选框来设置是否允许使用嵌套触发器。 第九章第九章 存储过程与触发器存储过程与触发器v触发器案例触发器案例例：在例：在STUDENT数据库中建立一个名为数据库中建立一个名为insert_xibu的的INSERT触发器，存储触发器，存储在专业表中。当用户向专业表中插入记录时，如果插入了在系部表中没有的在专业表中。当用户向专业表中插入记录时，如果插入了在系部表中没有的系部代码，则提示用户不能插入记录，否则提示记录插入成功。系部代码，则提示用户不能插入记录，否则提示记录插入成功。CREATETRIGGERinsert_xibuONdbo.专业专业FORINSERTASDECLAREXIBUCHAR(2)SELECTXIBU=系部系部.系部代码系部代码FROM系部系部,insertedWHERE系部系部.系部代码系部代码=inserted.系部代码系部代码IFXIBUPRINT(记录插入成功记录插入成功)ELSEBEGINPRINT(系部代码不存在系部表中，不能插入记录，插入将终止！系部代码不存在系部表中，不能插入记录，插入将终止！)ROLLBACKTRANSACTIONENDGO第九章第九章 存储过程与触发器存储过程与触发器v触发器案例触发器案例例：在例：在STUDENT数据库中建立一个名为数据库中建立一个名为delete_zhye的的DELETE触发器，存储触发器，存储在专业表中。当用户删除专业表中的记录时，如果班级表引用了此记录的专在专业表中。当用户删除专业表中的记录时，如果班级表引用了此记录的专业代码，则提示用户不能删除记录，否则提示记录已删除。业代码，则提示用户不能删除记录，否则提示记录已删除。CREATETRIGGERdelete_zhyeON专业专业FORDELETEASIF(SELECTCOUNT(*)FROM班级班级INNERJOINDELETEDON班级班级.专业代码专业代码=DELETED.专业代码专业代码)0BEGINPRINT(该专业被班级表所引用，你不可以删除此条记录，删除将终止该专业被班级表所引用，你不可以删除此条记录，删除将终止)ROLLBACKTRANSACTIONENDELSEPRINT记录已删除记录已删除GO第九章第九章 存储过程与触发器存储过程与触发器v触发器案例触发器案例例：在例：在STUDENT数据库中建立一个名为数据库中建立一个名为update_zymc的的UPDATE触发器，存触发器，存储在专业表中。当用户更新专业表中的专业名称时，提示用户不能修改专业储在专业表中。当用户更新专业表中的专业名称时，提示用户不能修改专业名称。名称。USESTUDENTGOCREATETRIGGERupdate_zymcONdbo.专业专业FORUPDATEASIFUPDATE(专业名称专业名称)BEGINPRINT不能修改系部名称不能修改系部名称ROLLBACKTRANSACTIONENDGO