存储过程和触发器管理PPT课件

1,第7章存储过程和触发器管理,存储过程,7.1,触发器,7.2,2,本章要点,存储过程概述创建存储过程执行存储过程存储过程的参数存储过程的返回值查看和修改存储过程删除存储过程触发器概述inserted和deleted表事务的概念及应用创建触发器修改和重命名触发器删除触发器,3,7.1存储过程,在创建SQLServer数据库应用程序时，Transact-SQL语言是应用程序和SQLServer数据库之间的主要编程接口。可用如下两种方法存储和执行Transact-SQL语句。(1)将Transact-SQL程序保存在本地，创建向SQLServer发送命令并处理结果的应用程序。(2)可以将Transact-SQL程序保存在SQLServer中，即存储过程，在本地创建执行存储过程及处理结果的应用程序。任何一组Transact-SQL语句构成的代码块，都可以作为存储过程保存起来。它在服务器端对数据库中的数据进行处理，并将结果返回到客户端。这样就避免了从客户端多次连接并访问数据库的操作，减少了网络上的传输量，同时也提高了客户端的工作效率（因为这些操作都是在服务器端完成的）。,4,7.1.1存储过程概述,存储过程是集中存储在SQLServer中的SQL语句和流程控制语句的预编译集合，用以实现某种任务（如查询）。这些语句在一个名称下存储并作为一个单元进行处理。,5,使用存储过程的优势,可以减少客户端代码的重复。只需创建存储过程一次并将其存储在数据库中，以后即可在客户端程序中多次调用该存储过程。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。允许更快地执行。如果某操作需要大量的Transact-SQL代码或需要重复执行，使用存储过程将比在客户端执行Transact-SQL批代码的执行速度要快。存储过程在服务器端经过预编译，生成查询计划，可以直接执行，而在客户端每次运行Transact-SQL语句时，都要从客户端重复发送，并且在SQLServer每次执行这些语句时，都要对其进行编译和优化。因此，存储过程执行速度更快。,6,使用存储过程的优势,减少网络流量。例如，一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。可以简化数据库管理。例如，要修改某种查询，如果查询重复存放在客户机上，则要在所有客户机上修改查询，而使用存储过程可以集中修改。可作为安全机制使用。例如，即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。,7,存储过程的分类,系统存储过程:SQLServer内置的存储过程，存储在master库中，主要用途是执行SQLServer的某些管理功能、显示有关数据库和用户的信息。系统存储过程名以SP_开头，可以在任何数据库中执行系统存储过程。用户存储过程：用户自行创建并存储在用户数据库中的存储过程。临时存储过程：分为局部临时存储过程和全局临时存储过程。,8,存储过程的分类,局部临时存储过程名称以#开头，存放在tempdb数据库中，只由创建并连接的用户使用，当该用户断开连接时将自动删除局部临时存储过程。全局临时存储过程名称以#开头，存放在tempdb数据库中，允许所有连接的用户使用，在所有用户断开连接时自动被删除。远程存储过程：位于远程服务器上的存储过程。扩展存储过程：利用外部语言（如C）编写的存储过程，以弥补SQLServer的不足之处，扩展新的功能,9,7.1.2创建存储过程,使用CREATEPROCEDURE语句使用企业管理器中的菜单命令,10,CREATEPROCEDURE语句,CREATEPROCEDURE存储过程名;编号参数数据类型VARYING=默认值OUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASSQL语句.n,11,参数说明,存储过程名必须符合标识符的命名规则，且对于数据库及其所有者必须是惟一的。要创建局部临时存储过程，可以在存储过程名前面加一个#号，要创建全局临时过程，可以在存储过程名前面加两个#号。完整的名称（包括#或#）不能超过128个字符。编号：可选整数，用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的存储过程一起删除。,12,参数说明,参数：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。用户必须在执行存储过程时提供每个所声明参数的值（除非定义了该参数的默认值）。参数名称前需要使用符号。参数名称必须符合标识符的命名规则。每个过程的参数仅用于该过程本身。在其他过程中可以使用相同的参数名称。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。,13,参数说明,数据类型：参数的数据类型。所有数据类型（包括text、ntext和image）均可以用作存储过程的参数。不过，cursor数据类型只能用于OUTPUT参数。如果指定的数据类型为cursor，也必须同时指定VARYING和OUTPUT关键字。VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。,14,参数说明,默认值：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行存储过程。默认值必须是常量或NULL。如果要在存储过程中对该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_、和）。OUTPUT：表明参数是返回参数。该选项的值可以返回给EXECUTE。使用OUTPUT参数可将信息返回给调用过程。text、ntext和image参数可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。,15,参数说明,RECOMPILE：表明不保存该存储过程的执行计划，该存储过程将在运行时重新编译。ENCRYPTION：指定SQLServer对syscomments表中包含本CREATEPROCEDURE语句文本的条目进行加密AS：用于指定该存储过程要执行的操作。SQL语句：存储过程中要包含的Transact-SQL语句。,16,例7-1,创建存储过程“增加成绩”，将表“学生”中所有学生的入学成绩增加10%。USE学生管理GOCREATEPROCEDURE增加成绩ASUPDATE学生SET入学成绩=入学成绩+10GO,17,例7-1,在创建一个存储过程时，如果已经存在同名的存储过程，则不允许创建新的存储过程。可以将以上代码改写为：USE学生管理-如果存在名称为“增加成绩”的存储过程，则将其删除IFEXISTS(SELECTnameFROMsysobjectsWHEREname=增加成绩ANDtype=P)DROPPROCEDURE增加成绩GOCREATEPROCEDURE增加成绩ASUPDATE学生SET入学成绩=入学成绩+10GO,18,19,使用企业管理器创建存储过程,在该对话框的文本框中有以下的默认语句：CREATEPROCEDUREOWNER.PROCEDURENAMEAS这是CREATEPROCEDURE语句的开始部分，存储过程的具体文本需要用户自己输入。例如，将以上默认语句修改成：CREATEPROCEDURE增加成绩ASUPDATE学生SET入学成绩=入学成绩+10GO,20,7.1.3执行存储过程,EXECUTE返回状态=存储过程名|存储过程名变量参数名称=值|变量OUTPUT|DEFAULT,.nWITHRECOMPILE,21,参数说明,返回状态：是一个可选的整型变量，保存存储过程的返回状态。这个变量在用于EXECUTE语句前，必须在批处理、存储过程或函数中声明过。存储过程名：要调用的存储过程的名称。存储过程名变量：局部变量名，代表存储过程的名称。参数名称：存储过程的参数，在CREATEPROCEDURE语句中定义。参数名称前必须加上符号。在使用格式“参数=值”时，参数名称和常量不一定按照CREATEPROCEDURE语句中定义的顺序出现。但是，如果有一个参数使用“参数=值”格式，则其他所有参数都必须使用这种格式。,22,参数说明,值：过程中参数的值。如果没有指定参数名称，参数值必须以CREATEPROCEDURE语句中定义的顺序给出。如果在CREATEPROCEDURE语句中定义了默认值，用户执行该存储过程时可以不必指定对应的参数。如果该存储过程使用了带LIKE关键字的参数名称，则默认值必须是常量，并且可以包含%、_、及通配符。默认值也可以为NULL。通常，定义存储过程时会指定当参数值为NULL时应该执行的操作。变量：是用来保存参数或者返回参数的变量。,23,参数说明,OUTPUT：指定存储过程必须返回一个参数。该存储过程的匹配参数也必须由关键字OUTPUT创建。使用游标变量作参数时使用该关键字。DEFAULT：根据存储过程的定义，提供参数的默认值。当过程需要的参数值没有事先定义好的默认值，或缺少参数，或指定了DEFAULT关键字时，就会出错。WITHRECOMPILE：强制编译新的计划。如果所提供的参数为非典型参数或者数据有很大的改变，使用该选项。在以后的程序执行中使用更改过的计划。该选项不能用于扩展存储过程。建议尽量少使用该选项，因为它消耗较多系统资源。,24,例7-2,执行存储过程“增加成绩”，并查看执行结果。USE学生管理GOEXEC增加成绩SELECT*FROM学生GO,执行结果:,25,7.1.4存储过程的参数,为了提高存储过程的灵活性，SQLServer2000支持在存储过程中使用参数。存储过程的参数分为输入参数和输出参数两种类型，输入参数用于向存储过程中带入数据，而输出参数则能将存储过程中的数据返回到调用程序。在定义存储过程时,可以同时指定参数,格式如下：参数名数据类型=默认值OUTPUT,.n如果参数后面使用OUTPUT关键字，则表明它是输出参数。,26,例7-3,创建存储过程add_proc，用于计算两个参数之和并将其输出。CREATEPROCEDUREadd_procnum1INT=0,num2INT=0ASDECLAREnum3INTSETnum3=num1+num2PRINTnum3,该存储过程定义了两个参数num1和num2，它们都是输入参数,参数类型为INT,默认值为0。,27,执行例7-3,不带参数执行：EXECadd_proc运行结果为：0带参数执行：EXECadd_proc13,25运行结果为：38,有时需要将存储过程中的计算结果返回到调用程序中，以便进行进一步的处理，此时就需要在存储过程中使用输出参数。,28,例7-4,创建存储过程add_proc1，用于计算两个参数之和，并使用输出参数返回结果。CREATEPROCEDUREadd_proc1num1INT=0,num2INT=0,num3INTOUTPUT-num3为输出参数ASSETnum3=num1+num2,29,执行例7-4,DECLAREnumASINTEXECadd_proc112,23,numOUTPUTPRINTnum运行结果为：38,30,例7-5,创建存储过程AvgScore，用于根据给定的院系和班级名称计算平均成绩，并使用输出参数返回结果。CREATEPROCEDUREAvgScoreorgvarchar(100),-院系名称，输入参数classvarchar(50),-班级名称，输入参数scorefloatOUTPUT-成绩，输出参数ASDECLAREorgidintSETorgid=0,31,例7-5,-根据参数中指定的院系名称org,获取院系编号SELECTorgid=记录编号FROM院系WHERE院系名称=orgIForgid=0BEGINSETscore=0PRINT指定的院系记录不存在ENDELSEBEGINSELECTscore=AVG(入学成绩)FROM学生WHERE所属院系=orgidAND班级=classGROUPBY所属院系,班级END,32,执行例7-5,DECLAREscorefloatEXECAvgScore软件系,二班,scoreOUTPUTPRINTscore运行结果为：584,33,7.1.5存储过程的返回值,可以在存储过程中使用RETURN语句返回一个状态值，返回值只能是整数。,34,例7-6,创建存储过程AvgScore1，根据给定的院系和班级名称计算平均成绩，并将结果使用输出参数返回。如果指定的院系存在，则返回1，否则返回0。CREATEPROCEDUREAvgScore1orgvarchar(100),classvarchar(50),scorefloatOUTPUTASDECLAREorgidintSETorgid=0,35,例7-6,-根据参数中指定的院系名称org,获取院系编号SELECTorgid=记录编号FROM院系WHERE院系名称=orgIForgid=0RETURN0ELSEBEGINSELECTscore=AVG(入学成绩)FROM学生WHERE所属院系=orgidAND班级=classGROUPBY所属院系,班级RETURN1ENDGO,36,执行例7-6,DECLAREscorefloatDECLAREresultintEXECresult=AvgScore1软件系,二班,scoreOUTPUT-检查返回值IFresult=1PRINTscoreELSEPRINT没有对应的记录,37,7.1.6查看和修改存储过程,使用企业管理器查看和修改存储过程使用查询分析器查看和修改存储过程使用企业管理器重命名存储过程使用ALTERPROCEDURE语句修改存储过程使用sp_rename重命名存储过程,38,1.使用企业管理器查看和修改存储过程,在企业管理器中展开指定的数据库，选中“存储过程”项，可以查看指定数据库中定义的所有存储过程。,39,1.使用企业管理器查看和修改存储过程,双击指定的存储过程，可以打开存储过程属性对话框，查看存储过程的定义情况。,用户可以在存储过程属性对话框中修改存储过程的代码。,40,2.使用查询分析器查看和修改存储过程,右键单击要编辑的存储过程，在弹出菜单中选择“编辑”，可以在右键的窗格中显示指定存储过程的代码。,41,3.使用企业管理器重命名存储过程,在企业管理器，展开“数据库”文件夹，选择存储过程所在的数据库，单击“存储过程”文件夹。在右侧的窗口中，列出了选择数据库的所有存储过程。右击要重命名的存储过程，选择“重命名”菜单项，就可以在当前位置上修改存储过程的名字。,42,4.使用ALTERPROCEDURE语句修改存储过程,ALTERPROCEDURE存储过程名;编号参数名数据类型VARYING=默认值OUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASSQL语句.n各参数含义与CREATEPROCEDURE语句相同,43,例7-7,使用ALTERPROCEDURE语句修改存储过程“增加成绩”，对其进行加密处理。USE学生管理GOALTERPROCEDURE增加成绩WITHENCRYPTIONASUPDATE学生SET入学成绩=入学成绩+10,指定以上语句后，在企业管理器中查看“增加成绩”存储过程，将弹出一个对话框，提示用户存储过程已经加密，不能够查看。,44,5.使用sp_rename重命名存储过程,系统存储过程sp_rename的功能是更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。使用sp_rename重命名存储过程的语法结构如下：sp_renameobjname=对象名,newname=新对象名参数说明：objname=对象名：指定存储过程的当前名称。newname=新对象名：指定存储过程的新名称。,45,例7-8,将存储过程add_proc重命名为add_proc2，则可以使用以下命令：EXECsp_renameadd_proc,add_proc2运行结果为：注意:更改对象名的任一部分都可能破坏脚本和存储过程。object已重命名为add_proc2。,46,7.1.7删除存储过程,使用企业管理器使用DROPPROCEDURE语句,47,使用企业管理器,在企业管理器中，右击要删除的存储过程，选择“删除”命令，并确认删除。,48,使用DROPPROCEDURE语句,DROPPROCEDURE存储过程名,.n,49,例7-9,删除存储过程add_proc，可以使用以下命令：DROPPROCEDUREadd_proc,50,例7-10删除一组存储过程,设某“职工”数据库中有一个“职工工资”表，该表包含有“基本工资”、“奖金”和“实发工资”等列。创建一组存储过程，求“职工工资”表的平均基本工资、平均奖金和平均实发工资。USE职工GOCREATEPROCAveSalary;1ASSELECTAVG(基本工资)FROM职工工资GOCREATEPROCAveSalary;2ASSELECTAVG(奖金)FROM职工工资GOCREATEPROCAveSalary;3ASSELECTAVG(实发工资)FROM职工工资GO,51,例7-10删除一组存储过程,使用以下语句执行以上存储过程。EXECAveSalary;1EXECAveSalary;2EXECAveSalary;3要删除以上创建的三个存储过程，可以使用语句：DROPPROCEDUREAveSalary注意，不能写成：DROPPROCEDUREAveSalary;1DROPPROCEDUREAveSalary;2DROPPROCEDUREAveSalary;3,52,7.2触发器,触发器概述inserted和deleted表事务的概念及应用创建触发器修改和重命名触发器删除触发器,53,7.2.1触发器的基本概念,触发器与普通存储过程的区别：触发器的执行是由事件触发的，而普通存储过程是由命令调用执行的。使用触发器有助于强制保持数据库的数据完整性。例如，在触发器中可以完成如下功能：不允许删除或更新特定的记录。不允许插入不符合逻辑关系的记录。在删除一条记录的同时删除其他表中与其相关的记录。在修改一条记录的同时修改其他表中与其相关的记录。,54,SQLServer2000提供了两种触发器,INSTEADOF触发器：INSTEADOF触发器在指定的操作（INSERT、UPDATE或DELETE语句）之前被执行，它的功能是不执行指定的操作，而是执行INSTEADOF触发器中定义的操作。可以在表和视图上定义INSTEADOF触发器。AFTER触发器。在执行了INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器。AFTER触发器只能在表上指定。,55,触发器的主要优点,触发器是自动执行的，不需要管理员手动维护数据库的数据完整性。触发器可以对数据库中的相关表进行级联更改。例如，可以在表“院系”中定义触发器，当用户删除表“院系”中的记录时，触发器将删除表“学生”中对应院系的记录。触发器可以限制向表中插入无效的数据，这一点与CHECK约束的功能相似。但在CHECK约束中不能使用到其他表中的字段，而在触发器中则没有此限制。例如，可以在表“学生”中定义触发器，限制插入的记录其“院系编号”字段值必须在表“院系”中存在对应的记录。,56,7.2.2inserted和deleted表,deleted表用于存储DELETE和UPDATE语句所影响的行的复本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到deleted表中。deleted表和触发器表通常没有相同的行。inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。inserted表中的行是触发器表中新行的副本。,57,inserted和deleted表的变化,在设置触发器条件时，应当为引发触发器的操作恰当使用inserted和deleted表。通常在插入数据时，可以从insert表中读取新插入的值，此时delete表不会发生变化。在删除数据时，可以从delete表中读取已经删除或修改的值，而insert表不会发生变化。在更新数据时，insert表和delete表都发生变化。可以从delete表中读取原有的值，从insert表中读取修改后的值。,58,7.2.3事务的概念及应用,在触发器中经常会取消用户先前进行的操作，例如不允许插入不符合条件的数据。SQLServer提供了一种叫做事务的机制，它可以保证指定的对数据库的一系列操作作为一个整体被执行，在最终提交操作之间，用户可以随时取消前面的操作，将数据库还原到没有执行操作前的状态。,59,事务的属性,原子性:事务必须是原子工作单元。它对数据库所进行的操作，要么全都执行，要么全都不执行。一致性:事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构都必须是正确的。隔离性:由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。持久性:事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。,60,日常生活中事务的例子,用户在网上商场选择商品，然后向商家提交购物请求，并进行网上支付。此时，交易状态为提交。商家获得用户提交的购物请求，在确认收到网上支付的金额后向用户邮寄商品。此时，交易状态为已处理。用户在收到商品并确认商品无质量后，在网上商场确认已收到商品。此时，交易状态为成功。,定义一个事务需要3种操作，即启动事务、回滚事务和提交事务。启动事务相当于用户提交购物请求之前的状态，回滚事务相当于用户取消当前交易，提交事务相当于用户确认交易成功。,61,启动事务,SQLServer中包括2种启动事务的模式，即显式事务和隐式事务。,显式事务：通过BEGINTRANSACTION语句显式启动事务。BEGINTRANSACTION语句的基本语法如下：BEGINTRANSACTION事务名在显式事务中，事务名是可选项。事务名必须符合标识符命名规则。隐式事务：当用户没有显式地定义事务时，SQLServer按其默认的规定自动划分事务。,62,回滚事务,如果服务器错误使事务无法成功完成，SQLServer将自动回滚该事务，并释放该事务占用的所有资源。如果客户端与SQLServer的网络连接中断了，那么当网络告知SQLServer该中断时，将回滚该连接的所有未完成事务。如果用户需要手动回滚事务，可以使用语句：ROLLBACKTRANSACTION事务名其中，“事务名”是给BEGINTRANSACTION上的事务指派的名称。,63,提交事务,COMMITTRANSACTION语句可以标志一个成功的隐性事务或显式事务的结束，它的基本语法如下：COMMITTRANSACTION事务名注意，不能在发出COMMITTRANSACTION语句之后回滚事务，因为数据修改已经成为数据库的永久部分。,64,例7-11,定义一个事务，向“学生”表中插入两条记录。其中，第1条INSERT语句是正确的，而第2条INSERT语句是错误的。执行此事务语句后，查看“学生”表中的数据，确认第1条语句没有被执行。USE学生管理GOBEGINTRANSACTIONINSERTINTO学生VALUES(test1,1,一班,500,2)INSERTINTO学生VALUES(10,test2,1,二班,500,2)COMMITTRANSACTIONGO,65,例7-11,第2条插入语句因为指定了标识列的值，所以产生错误。执行此事务语句的结果如下：服务器:消息8101，级别16，状态1，行1在查询分析器中执行如下语句：SELECT*FROM学生可以看到，因为第2条INSERT语句出现错误，导致事务回滚，所以第1条语句所插入的记录也没有出现在结果集中。,66,例7-12,定义一个事务，向“学生”表中插入一条记录，然后将事务回滚。执行此事务语句后，查看“学生”表中的数据，确认INSERT语句插入的数据不在结果集中。USE学生管理GOBEGINTRANSACTIONINSERTINTO学生VALUES(test1,1,一班,500,2)ROLLBACKTRANSACTIONGO,67,例7-12,执行此事务语句的结果如下：（所影响的行数为1行）证明INSERT语句已经被执行。在查询分析器中执行如下语句：SELECT*FROM学生可以看到，因为执行了ROLLBACKTRANSACTION语句，导致事务回滚，所以INSERT语句所插入的记录也没有出现在结果集中。,68,7.2.4创建触发器,在企业管理器中手动地创建使用CREATETRIGGER语句创建,69,在创建触发器之前，应该考虑以下问题,CREATETRIGGER语句必须是批处理中的第一条语句。SQLServer将该批处理中随后的其他所有语句解释为CREATETRIGGER语句定义的一部分。创建触发器的权限默认分配给表的所有者，且不能将该权限转移给其他用户。触发器为数据库对象，其名称必须遵循标识符的命名规则。虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。,70,在创建触发器之前，应该考虑以下问题,虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表，不应引用系统表。如果已经给一个表的外键定义了级联删除或级联更新，则不能在该表上定义INSTEADOFDELETE或INSTEADOFDELETEUPDATE触发器。虽然TRUNCATETABLE语句（删除表中的所有行）类似于没有WHERE子句的DELETE语句，但它并不会引发DELETE触发器。WRITETEXT语句（更新text、ntext或image类型的列）不会引发INSERT或UPDATE触发器。,71,使用CREATETRIGGER语句创建触发器,CREATETRIGGER触发器名ON表名|视图名WITHENCRYPTIONFOR|AFTER|INSTEADOFDELETE,INSERT,UPDATEASIFUPDATE(列)AND|ORUPDATE(列).nSQL语句.n,72,参数说明,触发器名：必须符合标识符的命名规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者的名称。表名|视图名：是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。WITHENCRYPTION：加密syscomments表中包含CREATETRIGGER语句文本的条目。AFTER：指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定FOR关键字，则AFTER是默认设置。不能在视图上定义AFTER触发器。,73,参数说明,INSTEADOF：指定执行触发器而不是执行触发SQL语句，从而替代触发SQL语句的操作。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEADOF触发器。然而，可以在每个具有INSTEADOF触发器的视图上定义视图。DELETE,INSERT,UPDATE：是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。,74,参数说明,AS：引入触发器要执行的操作。IFUPDATE(列)：用于判断是否在指定的列上进行了INSERT或UPDATE操作（不能用于DELETE操作），可以指定多列。因为在ON子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。对于INSERT操作，IFUPDATE将返回TRUE值，因为这些列插入了数据。SQL语句：当尝试DELETE、INSERT或UPDATE操作时要执行的Transact-SQL语句。,75,例7-13INSERT触发器,在“学生”表中创建一个INSERT触发器，如果插入记录的院系编号值在“院系”表中存在下级单位（例如，计算机学院包括软件系），则不执行插入操作，并提示用户。CREATETRIGGERinsert_studentON学生FORINSERTASDECLAREorgidintDECLAREorgnamevarchar(100)SELECTorgid=所属院系FROMinserted,76,例7-13INSERT触发器,-判断插入的院系记录是否存在上级记录SELECTorgname=院系名称FROM院系WHERE上级编号=orgidIForgname-如果存在上级院系BEGINPRINT指定院系存在下级单位，请选择具体单位！ROLLBACKTRANSACTIONENDGO,77,例7-13INSERT触发器,为了验证触发器是否正常工作，在查询分析器中执行如下语句：INSERTINTO学生VALUES(小朱,0,二班,500,1)因为院系编号为1的记录存在下级单位，所以返回结果如下：指定院系存在下级单位，请选择具体单位！,78,例7-14UPDATE触发器,在“学生”表中创建一个UPDATE触发器，如果修改记录的院系编号值在“院系”表中存在下级单位（例如，计算机学院包括软件系），则不执行修改操作，并提示用户。CREATETRIGGERupdate_studentON学生FORUPDATEAS-从表inserted中获取更新后记录的院系编号DECLAREorgidintDECLAREorgnamevarchar(100)SELECTorgid=所属院系FROMinserted,79,例7-14UPDATE触发器,-判断修改的院系记录是否存在上级记录SELECTorgname=院系名称FROM院系WHERE上级编号=orgidIForgnameBEGINPRINT指定院系存在下级单位，请选择具体单位！ROLLBACKTRANSACTION-回滚操作ENDGO,80,例7-14UPDATE触发器,为了验证触发器是否正常工作，在查询分析器中执行如下语句：UPDATE学生SET所属院系=1WHERE所属院系=2因为院系编号为1的记录存在下级单位，所以返回结果如下：指定院系存在下级单位，请选择具体单位！,81,例7-15DELETE触发器,在“院系”表中创建一个DELETE触发器，如果删除记录的院系编号值在“院系”表中存在下级单位（例如，计算机学院包括软件系），则不执行删除操作，并提示用户。CREATETRIGGERdelete_orgON院系FORDELETEAS-从表deleted中获取删除记录的院系编号DECLAREorgidintDECLAREorgnamevarchar(100)SELECTorgid=记录编号FROMdeleted,82,例7-15DELETE触发器,-判断删除的院系记录是否存在下级记录SELECTorgname=院系名称FROM院系WHERE上级编号=orgidIForgnameBEGINPRINT指定院系存在下级单位，不允许被删除！ROLLBACKTRANSACTION-回滚操作ENDGO,83,例7-15DELETE触发器,为了验证触发器是否正常工作，在查询分析器中执行如下语句：DELETEFROM院系WHERE记录编号=1因为院系编号为1的记录存在下级单位，所以返回结果如下：指定院系存在下级单位，不允许被删除！,84,例7-16INSTEADOF触发器,使用INSTEADOF触发器实现例7-15的功能。CREATETRIGGERdelete_org1ON院系INSTEADOFDELETEAS-从表deleted中获取删除记录的院系编号DECLAREorgidintDECLAREorgnamevarchar(100)SELECTorgid=记录编号FROMdeleted,85,例7-16INSTEADOF触发器,-判断修改的院系记录是否存在下级记录SELECTorgname=院系名称FROM院系WHERE上级编号=orgidIForgnamePRINT指定院系存在下级单位，不允许被删除！ELSEDELETEFROM院系WHERE记录编号=orgidGO,86,例7-16INSTEADOF触发器,因为INSTEADOF触发器使用触发器中定义的代码取代原操作，所以不需要进行回滚操作。当然，如果原操作符合规定的条件，还需要在触发器中重新执行此操作。为了验证触发器是否正常工作，在查询分析器中执行如下语句：DELETEFROM院系WHERE记录编号=1因为院系编号为1的记录存在下级单位，所以返回结果如下：指定院系存在下级单位，不允许被删除！（所影响的行数为1行）,87,例7-17对特定列进行测试,使用IFUPDATE(列名)子句实现例7-14的功能。CREATETRIGGERupdate_student1ON学生FORUPDATEAS-从表inserted中获取更新后记录的院系编号DECLAREorgidintDECLAREorgnamevarchar(100)