存储过程与触发器ppt

存储过程与触发器ppt1第一页，共一百零三页，编辑于2023年，星期日本章要点存储过程概述创建存储过程执行存储过程存储过程的参数存储过程的返回值查看和修改存储过程删除存储过程触发器概述inserted和deleted表事务的概念及应用创建触发器修改和重命名触发器删除触发器2第二页，共一百零三页，编辑于2023年，星期日7.1存储过程在创建SQLServer数据库应用程序时，Transact-SQL语言是应用程序和SQLServer数据库之间的主要编程接口。可用如下两种方法存储和执行Transact-SQL语句。(1)将Transact-SQL程序保存在本地，创建向SQLServer发送命令并处理结果的应用程序。(2)可以将Transact-SQL程序保存在SQLServer中，即存储过程，在本地创建执行存储过程及处理结果的应用程序。任何一组Transact-SQL语句构成的代码块，都可以作为存储过程保存起来。它在服务器端对数据库中的数据进行处理，并将结果返回到客户端。这样就避免了从客户端多次连接并访问数据库的操作，减少了网络上的传输量，同时也提高了客户端的工作效率（因为这些操作都是在服务器端完成的）。3第三页，共一百零三页，编辑于2023年，星期日7.1.1存储过程概述存储过程是集中存储在SQLServer中的SQL语句和流程控制语句的预编译集合，用以实现某种任务（如查询）。这些语句在一个名称下存储并作为一个单元进行处理。4第四页，共一百零三页，编辑于2023年，星期日使用存储过程的优势可以减少客户端代码的重复。只需创建存储过程一次并将其存储在数据库中，以后即可在客户端程序中多次调用该存储过程。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。允许更快地执行。如果某操作需要大量的Transact-SQL代码或需要重复执行，使用存储过程将比在客户端执行Transact-SQL批代码的执行速度要快。存储过程在服务器端经过预编译，生成查询计划，可以直接执行，而在客户端每次运行Transact-SQL语句时，都要从客户端重复发送，并且在SQLServer每次执行这些语句时，都要对其进行编译和优化。因此，存储过程执行速度更快。5第五页，共一百零三页，编辑于2023年，星期日使用存储过程的优势减少网络流量。例如，一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。可以简化数据库管理。例如，要修改某种查询，如果查询重复存放在客户机上，则要在所有客户机上修改查询，而使用存储过程可以集中修改。可作为安全机制使用。例如，即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。6第六页，共一百零三页，编辑于2023年，星期日存储过程的分类系统存储过程:SQLServer内置的存储过程，存储在master库中，主要用途是执行SQLServer的某些管理功能、显示有关数据库和用户的信息。系统存储过程名以SP_开头，可以在任何数据库中执行系统存储过程。用户存储过程：用户自行创建并存储在用户数据库中的存储过程。临时存储过程：分为局部临时存储过程和全局临时存储过程。7第七页，共一百零三页，编辑于2023年，星期日存储过程的分类局部临时存储过程名称以#开头，存放在tempdb数据库中，只由创建并连接的用户使用，当该用户断开连接时将自动删除局部临时存储过程。全局临时存储过程名称以##开头，存放在tempdb数据库中，允许所有连接的用户使用，在所有用户断开连接时自动被删除。远程存储过程：位于远程服务器上的存储过程。扩展存储过程：利用外部语言（如C）编写的存储过程，以弥补SQLServer的不足之处，扩展新的功能8第八页，共一百零三页，编辑于2023年，星期日7.1.2创建存储过程使用CREATEPROCEDURE语句使用企业管理器中的菜单命令9第九页，共一百零三页，编辑于2023年，星期日CREATEPROCEDURE语句CREATEPROC[EDURE]存储过程名[;编号][{@参数数据类型}[VARYING][=默认值][OUTPUT]][,...n]WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSQL语句[...n]10第十页，共一百零三页，编辑于2023年，星期日参数说明存储过程名必须符合标识符的命名规则，且对于数据库及其所有者必须是惟一的。要创建局部临时存储过程，可以在存储过程名前面加一个#号，要创建全局临时过程，可以在存储过程名前面加两个#号。完整的名称（包括#或##）不能超过128个字符。编号：可选整数，用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的存储过程一起删除。11第十一页，共一百零三页，编辑于2023年，星期日参数说明@参数：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。用户必须在执行存储过程时提供每个所声明参数的值（除非定义了该参数的默认值）。参数名称前需要使用@符号。参数名称必须符合标识符的命名规则。每个过程的参数仅用于该过程本身。在其他过程中可以使用相同的参数名称。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。12第十二页，共一百零三页，编辑于2023年，星期日参数说明数据类型：参数的数据类型。所有数据类型（包括text、ntext和image）均可以用作存储过程的参数。不过，cursor数据类型只能用于OUTPUT参数。如果指定的数据类型为cursor，也必须同时指定VARYING和OUTPUT关键字。VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。13第十三页，共一百零三页，编辑于2023年，星期日参数说明默认值：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行存储过程。默认值必须是常量或NULL。如果要在存储过程中对该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_、[]和[^]）。OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可将信息返回给调用过程。text、ntext和image参数可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。14第十四页，共一百零三页，编辑于2023年，星期日参数说明RECOMPILE：表明不保存该存储过程的执行计划，该存储过程将在运行时重新编译。ENCRYPTION：指定SQLServer对syscomments表中包含本CREATEPROCEDURE语句文本的条目进行加密AS：用于指定该存储过程要执行的操作。SQL语句：存储过程中要包含的Transact-SQL语句。15第十五页，共一百零三页，编辑于2023年，星期日存储过程定义1.无参数存储过程2.有参数存储过程（输入参数、输出参数output）16第十六页，共一百零三页，编辑于2023年，星期日例题1：建立一个查询存储过程，实现查询成绩表中的所有及格成绩。（无参数存储过程）createprocseleprocas

select*from成绩表where成绩>=60执行存储过程：execseleproc结果如图：17第十七页，共一百零三页，编辑于2023年，星期日例2：创建一个向成绩表添加记录的存储过程。（有参数存储过程）createprocinsertproc@snochar(10),@cnochar(3),@gradefloatasinsertinto成绩表values(@sno,@cno,@grade)执行存储过程：execinsertproc'0009','03',9818第十八页，共一百零三页，编辑于2023年，星期日例3：创建一个存储过程，要求根据输入的课程号统计该课程平均分。createproccnoavgscore@cnochar(2),@avgscorefloatoutputasdeclare@cnonchar(2)

set@cnon=''

select@cnon=课程号from成绩表

where课程号=@cno

if@cnon<>''

begin

select@avgscore=avg(成绩)from成绩表

where课程号=@cno

end

else

print'此课程号不存在'

go19第十九页，共一百零三页，编辑于2023年，星期日运行该存储过程：declare@ifloatexeccnoavgscore'02',@ioutputprint@i20第二十页，共一百零三页，编辑于2023年，星期日说明：Withencryption子句的作用是将存储过程的原代码加密；Withrecompile子句的作用是对该存储过程重编译。21第二十一页，共一百零三页，编辑于2023年，星期日例4创建存储过程“增加成绩”，将表“成绩表”中所有学生的成绩增加10%。USE学生管理GOCREATEPROCEDURE增加成绩ASUPDATE成绩表SET成绩=成绩*1.1GO执行存储过程：exec增加成绩

22第二十二页，共一百零三页，编辑于2023年，星期日例5（细节考虑）在创建一个存储过程时，如果已经存在同名的存储过程，则不允许创建新的存储过程。可以将以上代码改写为：USE学生管理--如果存在名称为“增加成绩”的存储过程，则将其删除IFEXISTS(SELECTnameFROM

sysobjects

WHEREname='增加成绩'ANDtype='P')DROPPROCEDURE增加成绩GOCREATEPROCEDURE增加成绩ASUPDATE成绩表SET成绩=成绩*1.1GO23第二十三页，共一百零三页，编辑于2023年，星期日使用企业管理器创建存储过程在企业管理器中，展开要创建存储过程的数据库,用鼠标右击“存储过程”，在弹出菜单中单击“新建存储过程”命令，打开新建存储过程对话框，如下图：24第二十四页，共一百零三页，编辑于2023年，星期日使用企业管理器创建存储过程在该对话框的文本框中有以下的默认语句：CREATEPROCEDURE[OWNER].[PROCEDURENAME]AS这是CREATEPROCEDURE语句的开始部分，存储过程的具体文本需要用户自己输入。例如，将以上默认语句修改成：

CREATEPROCEDURE增加成绩ASUPDATE成绩表SET成绩=成绩*1.1GO25第二十五页，共一百零三页，编辑于2023年，星期日7.1.3执行存储过程[[EXEC[UTE]]{[@返回状态=]{存储过程名|@存储过程名变量}}[[@参数名称=]{值|@变量[OUTPUT]|[DEFAULT]}]

[,...n][WITHRECOMPILE]26第二十六页，共一百零三页，编辑于2023年，星期日参数说明返回状态：是一个可选的整型变量，保存存储过程的返回状态。这个变量在用于EXECUTE语句前，必须在批处理、存储过程或函数中声明过。存储过程名：要调用的存储过程的名称。@存储过程名变量：局部变量名，代表存储过程的名称。@参数名称：存储过程的参数，在CREATEPROCEDURE语句中定义。参数名称前必须加上符号@。在使用格式“@参数=值”时，参数名称和常量不一定按照CREATEPROCEDURE语句中定义的顺序出现。但是，如果有一个参数使用“@参数=值”格式，则其他所有参数都必须使用这种格式。27第二十七页，共一百零三页，编辑于2023年，星期日参数说明值：过程中参数的值。如果没有指定参数名称，参数值必须以CREATEPROCEDURE语句中定义的顺序给出。如果在CREATEPROCEDURE语句中定义了默认值，用户执行该存储过程时可以不必指定对应的参数。如果该存储过程使用了带LIKE关键字的参数名称，则默认值必须是常量，并且可以包含%、_、[]及[^]通配符。默认值也可以为NULL。通常，定义存储过程时会指定当参数值为NULL时应该执行的操作。@变量：是用来保存参数或者返回参数的变量。28第二十八页，共一百零三页，编辑于2023年，星期日参数说明OUTPUT：指定存储过程必须返回一个参数。该存储过程的匹配参数也必须由关键字OUTPUT创建。使用游标变量作参数时使用该关键字。DEFAULT：根据存储过程的定义，提供参数的默认值。当过程需要的参数值没有事先定义好的默认值，或缺少参数，或指定了DEFAULT关键字时，就会出错。WITHRECOMPILE：强制编译新的计划。如果所提供的参数为非典型参数或者数据有很大的改变，使用该选项。在以后的程序执行中使用更改过的计划。该选项不能用于扩展存储过程。建议尽量少使用该选项，因为它消耗较多系统资源。29第二十九页，共一百零三页，编辑于2023年，星期日7.1.4存储过程的参数为了提高存储过程的灵活性，SQLServer2000支持在存储过程中使用参数。存储过程的参数分为输入参数和输出参数两种类型，输入参数用于向存储过程中带入数据，而输出参数则能将存储过程中的数据返回到调用程序。在定义存储过程时,可以同时指定参数,格式如下：

@参数名数据类型[=默认值][OUTPUT][,...n]如果参数后面使用OUTPUT关键字，则表明它是输出参数。30第三十页，共一百零三页，编辑于2023年，星期日例6创建存储过程add_proc，用于计算两个参数之和并将其输出。

CREATEPROCEDUREadd_proc@num1INT=0,@num2INT=0ASDECLARE@num3INTSET@num3=@num1+@num2PRINT@num3该存储过程定义了两个参数@num1和@num2，它们都是输入参数,参数类型为INT,默认值为0。31第三十一页，共一百零三页，编辑于2023年，星期日执行例6不带参数执行：EXECadd_proc运行结果为：

0带参数执行：EXECadd_proc13,25

运行结果为：38有时需要将存储过程中的计算结果返回到调用程序中，以便进行进一步的处理，此时就需要在存储过程中使用输出参数。

32第三十二页，共一百零三页，编辑于2023年，星期日例7创建存储过程add_proc1，用于计算两个参数之和，并使用输出参数返回结果。

CREATEPROCEDUREadd_proc1@num1INT=0,@num2INT=0,@num3INTOUTPUT--@num3为输出参数ASSET@num3=@num1+@num233第三十三页，共一百零三页，编辑于2023年，星期日执行例7DECLARE@numASINTEXECadd_proc112,23,@numOUTPUTPRINT@num运行结果为：3834第三十四页，共一百零三页，编辑于2023年，星期日例题8：编辑一个可自由运算的存储过程createproccalculate@num1asfloat,@num2float,@schar(2),@num3floatoutputasset@num3=case@swhen'+'then@num1+@num2when'-'then@num1-@num2when'*'then@num1*@num2when'/'then@num1/@num2end35第三十五页，共一百零三页，编辑于2023年，星期日运行该存储过程declare@num3floatexeccalculate2,4,'/',@num3outputselect@num336第三十六页，共一百零三页，编辑于2023年，星期日7.1.6查看和修改存储过程使用企业管理器查看和修改存储过程使用查询分析器查看和修改存储过程使用企业管理器重命名存储过程使用ALTERPROCEDURE语句修改存储过程使用sp_rename重命名存储过程37第三十七页，共一百零三页，编辑于2023年，星期日1.使用企业管理器查看和修改存储过程在企业管理器中展开指定的数据库，选中“存储过程”项，可以查看指定数据库中定义的所有存储过程。38第三十八页，共一百零三页，编辑于2023年，星期日1.使用企业管理器查看和修改存储过程双击指定的存储过程，可以打开存储过程属性对话框，查看存储过程的定义情况。用户可以在存储过程属性对话框中修改存储过程的代码。

39第三十九页，共一百零三页，编辑于2023年，星期日2.使用查询分析器查看和修改存储过程右键单击要编辑的存储过程，在弹出菜单中选择“编辑”，可以在右键的窗格中显示指定存储过程的代码。40第四十页，共一百零三页，编辑于2023年，星期日3.使用企业管理器重命名存储过程在企业管理器，展开“数据库”文件夹，选择存储过程所在的数据库，单击“存储过程”文件夹。在右侧的窗口中，列出了选择数据库的所有存储过程。右击要重命名的存储过程，选择“重命名”菜单项，就可以在当前位置上修改存储过程的名字。41第四十一页，共一百零三页，编辑于2023年，星期日4.使用ALTERPROCEDURE语句修改存储过程ALTERPROC[EDURE]存储过程名[;编号][{@参数名数据类型}[VARYING][=默认值][OUTPUT]][,...n]WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSQL语句[...n]各参数含义与CREATEPROCEDURE语句相同42第四十二页，共一百零三页，编辑于2023年，星期日例9使用ALTERPROCEDURE语句修改存储过程“增加成绩”，对其进行加密处理。USE学生管理GOALTERPROCEDURE增加成绩WITHENCRYPTIONASUPDATE成绩表SET成绩=成绩+10指定以上语句后，在企业管理器中查看“增加成绩”存储过程，将弹出一个对话框，提示用户存储过程已经加密，不能够查看。43第四十三页，共一百零三页，编辑于2023年，星期日5.使用sp_rename重命名存储过程系统存储过程sp_rename的功能是更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。使用sp_rename重命名存储过程的语法结构如下：

sp_rename[@objname=]'对象名',[@newname=]'新对象名'参数说明：[@objname=]'对象名'：指定存储过程的当前名称。[@newname=]'新对象名'：指定存储过程的新名称。

44第四十四页，共一百零三页，编辑于2023年，星期日例10将存储过程add_proc重命名为add_proc2，则可以使用以下命令：

EXECsp_rename'add_proc','add_proc2'运行结果为：

注意:更改对象名的任一部分都可能破坏脚本和存储过程。object已重命名为'add_proc2'。45第四十五页，共一百零三页，编辑于2023年，星期日7.1.7删除存储过程使用企业管理器使用DROPPROCEDURE语句46第四十六页，共一百零三页，编辑于2023年，星期日使用企业管理器在企业管理器中，右击要删除的存储过程，选择“删除”命令，并确认删除。47第四十七页，共一百零三页，编辑于2023年，星期日使用DROPPROCEDURE语句DROPPROCEDURE{存储过程名}[,...n]48第四十八页，共一百零三页，编辑于2023年，星期日例11删除存储过程add_proc，可以使用以下命令：

DROPPROCEDUREadd_proc49第四十九页，共一百零三页，编辑于2023年，星期日例12删除一组存储过程设某“职工”数据库中有一个“职工工资”表，该表包含有“基本工资”、“奖金”和“实发工资”等列。创建一组存储过程，求“职工工资”表的平均基本工资、平均奖金和平均实发工资。USE职工GOCREATEPROCAveSalary;1ASSELECTAVG(基本工资)FROM职工工资GOCREATEPROCAveSalary;2ASSELECTAVG(奖金)FROM职工工资GOCREATEPROCAveSalary;3ASSELECTAVG(实发工资)FROM职工工资GO50第五十页，共一百零三页，编辑于2023年，星期日例13删除一组存储过程使用以下语句执行以上存储过程。EXECAveSalary;1EXECAveSalary;2EXECAveSalary;3要删除以上创建的三个存储过程，可以使用语句：

DROPPROCEDUREAveSalary注意，不能写成：DROPPROCEDUREAveSalary;1DROPPROCEDUREAveSalary;2DROPPROCEDUREAveSalary;351第五十一页，共一百零三页，编辑于2023年，星期日7.2触发器触发器概述inserted和deleted表事务的概念及应用创建触发器修改和重命名触发器删除触发器52第五十二页，共一百零三页，编辑于2023年，星期日7.2.1触发器的基本概念触发器是一种特殊的存储过程，与普通存储过程的区别：触发器的执行是由事件触发的，而普通存储过程是由命令调用执行的。使用触发器有助于强制保持数据库的数据完整性。例如，在触发器中可以完成如下功能：不允许删除或更新特定的记录。不允许插入不符合逻辑关系的记录。在删除一条记录的同时删除其他表中与其相关的记录。在修改一条记录的同时修改其他表中与其相关的记录。53第五十三页，共一百零三页，编辑于2023年，星期日SQLServer2000提供了两种触发器INSTEADOF触发器(前置触发器)：INSTEADOF触发器在指定的操作（INSERT、UPDATE或DELETE语句）之前被执行，它的功能是不执行指定的操作，而是执行INSTEADOF触发器中定义的操作。可以在表和视图上定义INSTEADOF触发器。AFTER/FOR触发器（后置触发器）。在执行了INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器。AFTER触发器只能在表上指定。54第五十四页，共一百零三页，编辑于2023年，星期日触发器的主要优点触发器是自动执行的，不需要管理员手动维护数据库的数据完整性。触发器可以对数据库中的相关表进行级联更改。例如，可以在表“院系”中定义触发器，当用户删除表“院系”中的记录时，触发器将删除表“学生”中对应院系的记录。触发器可以限制向表中插入无效的数据，这一点与CHECK约束的功能相似。但在CHECK约束中不能使用到其他表中的字段，而在触发器中则没有此限制。例如，可以在表“学生”中定义触发器，限制插入的记录其“院系编号”字段值必须在表“院系”中存在对应的记录。55第五十五页，共一百零三页，编辑于2023年，星期日7.2.2inserted和deleted表deleted表用于存储DELETE和UPDATE语句所影响的行的复本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到deleted表中。deleted表和触发器表通常没有相同的行。inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。inserted表中的行是触发器表中新行的副本。56第五十六页，共一百零三页，编辑于2023年，星期日inserted和deleted表的变化在设置触发器条件时，应当为引发触发器的操作恰当使用inserted和deleted表。通常在插入数据时，可以从inserted表中读取新插入的值，此时deleted表不会发生变化。在删除数据时，可以从deleted表中读取已经删除或修改的值，而inserted表不会发生变化。在更新数据时，inserted表和deleted表都发生变化。可以从deleted表中读取原有的值，从inserted表中读取修改后的值。57第五十七页，共一百零三页，编辑于2023年，星期日7.2.3事务的概念及应用在触发器中经常会取消用户先前进行的操作，例如不允许插入不符合条件的数据。SQLServer提供了一种叫做事务的机制，它可以保证指定的对数据库的一系列操作作为一个整体被执行，在最终提交操作之间，用户可以随时取消前面的操作，将数据库还原到没有执行操作前的状态。58第五十八页，共一百零三页，编辑于2023年，星期日事务的属性原子性:事务必须是原子工作单元。它对数据库所进行的操作，要么全都执行，要么全都不执行。一致性:事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构都必须是正确的。隔离性:由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。持久性:事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。59第五十九页，共一百零三页，编辑于2023年，星期日日常生活中事务的例子用户在网上商场选择商品，然后向商家提交购物请求，并进行网上支付。此时，交易状态为提交。商家获得用户提交的购物请求，在确认收到网上支付的金额后向用户邮寄商品。此时，交易状态为已处理。用户在收到商品并确认商品无质量后，在网上商场确认已收到商品。此时，交易状态为成功。定义一个事务需要3种操作，即启动事务、回滚事务和提交事务。启动事务相当于用户提交购物请求之前的状态，回滚事务相当于用户取消当前交易，提交事务相当于用户确认交易成功。60第六十页，共一百零三页，编辑于2023年，星期日启动事务SQLServer中包括2种启动事务的模式，即显式事务和隐式事务。显式事务：通过BEGINTRANSACTION语句显式启动事务。BEGINTRANSACTION语句的基本语法如下：

BEGINTRANSACTION[事务名]在显式事务中，事务名是可选项。事务名必须符合标识符命名规则。隐式事务：当用户没有显式地定义事务时，SQLServer按其默认的规定自动划分事务。61第六十一页，共一百零三页，编辑于2023年，星期日回滚事务如果服务器错误使事务无法成功完成，SQLServer将自动回滚该事务，并释放该事务占用的所有资源。如果客户端与SQLServer的网络连接中断了，那么当网络告知SQLServer该中断时，将回滚该连接的所有未完成事务。如果用户需要手动回滚事务，可以使用语句：ROLLBACKTRANSACTION[事务名]其中，“事务名”是给BEGINTRANSACTION上的事务指派的名称。62第六十二页，共一百零三页，编辑于2023年，星期日提交事务COMMITTRANSACTION语句可以标志一个成功的隐性事务或显式事务的结束，它的基本语法如下：

COMMIT[TRANSACTION][事务名]注意，不能在发出COMMITTRANSACTION语句之后回滚事务，因为数据修改已经成为数据库的永久部分。63第六十三页，共一百零三页，编辑于2023年，星期日例7-11定义一个事务，向“学生”表中插入两条记录。其中，第1条INSERT语句是正确的，而第2条INSERT语句是错误的。执行此事务语句后，查看“学生”表中的数据，确认第1条语句没有被执行。

USE学生管理GOBEGINTRANSACTIONINSERTINTO学生VALUES('test1',1,'一班',500,2)INSERTINTO学生VALUES(10,'test2',1,'二班',500,2)COMMITTRANSACTIONGO64第六十四页，共一百零三页，编辑于2023年，星期日例7-11第2条插入语句因为指定了标识列的值，所以产生错误。执行此事务语句的结果如下：服务器:消息8101，级别16，状态1，行1在查询分析器中执行如下语句：

SELECT*FROM学生可以看到，因为第2条INSERT语句出现错误，导致事务回滚，所以第1条语句所插入的记录也没有出现在结果集中。65第六十五页，共一百零三页，编辑于2023年，星期日例7-12定义一个事务，向“学生”表中插入一条记录，然后将事务回滚。执行此事务语句后，查看“学生”表中的数据，确认INSERT语句插入的数据不在结果集中。

USE学生管理GOBEGINTRANSACTION

INSERTINTO学生VALUES('test1',1,'一班',500,2)ROLLBACKTRANSACTIONGO66第六十六页，共一百零三页，编辑于2023年，星期日例7-12执行此事务语句的结果如下：（所影响的行数为1行）证明INSERT语句已经被执行。在查询分析器中执行如下语句：

SELECT*FROM学生可以看到，因为执行了ROLLBACKTRANSACTION语句，导致事务回滚，所以INSERT语句所插入的记录也没有出现在结果集中。67第六十七页，共一百零三页，编辑于2023年，星期日7.2.4创建触发器在企业管理器中手动地创建使用CREATETRIGGER语句创建68第六十八页，共一百零三页，编辑于2023年，星期日在创建触发器之前，应该考虑以下问题CREATETRIGGER语句必须是批处理中的第一条语句。SQLServer将该批处理中随后的其他所有语句解释为CREATETRIGGER语句定义的一部分。创建触发器的权限默认分配给表的所有者，且不能将该权限转移给其他用户。触发器为数据库对象，其名称必须遵循标识符的命名规则。虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。69第六十九页，共一百零三页，编辑于2023年，星期日在创建触发器之前，应该考虑以下问题虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表，不应引用系统表。如果已经给一个表的外键定义了级联删除或级联更新，则不能在该表上定义INSTEADOFDELETE或INSTEADOFDELETEUPDATE触发器。虽然TRUNCATETABLE语句（删除表中的所有行）类似于没有WHERE子句的DELETE语句，但它并不会引发DELETE触发器。WRITETEXT语句（更新text、ntext或image类型的列）不会引发INSERT或UPDATE触发器。70第七十页，共一百零三页，编辑于2023年，星期日使用CREATETRIGGER语句创建触发器CREATETRIGGER触发器名ON{表名|视图名}[WITHENCRYPTION]{{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}AS[IFUPDATE(列)[{AND|OR}UPDATE(列)][...n]]SQL语句[...n]}71第七十一页，共一百零三页，编辑于2023年，星期日参数说明触发器名：必须符合标识符的命名规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者的名称。表名|视图名：是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。WITHENCRYPTION：加密syscomments表中包含CREATETRIGGER语句文本的条目。AFTER：指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定FOR关键字，则AFTER是默认设置。不能在视图上定义AFTER触发器。72第七十二页，共一百零三页，编辑于2023年，星期日参数说明INSTEADOF：指定执行触发器而不是执行触发SQL语句，从而替代触发SQL语句的操作。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEADOF触发器。然而，可以在每个具有INSTEADOF触发器的视图上定义视图。{[DELETE][,][INSERT][,][UPDATE]}：是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。73第七十三页，共一百零三页，编辑于2023年，星期日参数说明AS：引入触发器要执行的操作。IFUPDATE(列)：用于判断是否在指定的列上进行了INSERT或UPDATE操作（不能用于DELETE操作），可以指定多列。因为在ON子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。对于INSERT操作，IFUPDATE将返回TRUE值，因为这些列插入了数据。SQL语句：当尝试DELETE、INSERT或UPDATE操作时要执行的Transact-SQL语句。74第七十四页，共一百零三页，编辑于2023年，星期日图书馆管理案例读者表读者种类表图书表借阅表75第七十五页，共一百零三页，编辑于2023年，星期日案例：图书借阅CreateTRIGGER[inserttr1]ON[dbo].[借阅表]

FORINSERT,update

AS/*自动填充应还书日期值*/

declare@dayint

select@day=借书期限from读者种类信息表a,读者表b,insertedwherea.种类编号=b.读者种类andb.读者编号=inserted.读者编号update借阅表set应还书日期=出借日期+@daywhere读者编号=(select读者编号frominserted)76第七十六页，共一百零三页，编辑于2023年，星期日/*自动更新罚金*/declare@retudatedatetimeset@retudate=''select@retudate=还书日期

from

inserteddeclare@numintif@retudate<>''ifexists(select*frominsertedwhere应还书日期<还书日期)beginupdate借阅表set罚金=0.1*datediff(day,应还书日期,@retudate)where读者编号=(select读者编号frominserted)endelsebeginupdate借阅表set罚金=0where读者编号=(select读者编号frominserted)end77第七十七页，共一百零三页，编辑于2023年，星期日/*自动更新图书表中的已借出量*/select@num=借阅数量,@retudate=还书日期frominsertedif@retudate<>''beginupdate图书表set已借出量=已借出量-@numwhere图书编号=(select图书编号frominserted)endelsebeginupdate图书表set已借出量=已借出量+@numwhere图书编号=(select图书编号frominserted)end

78第七十八页，共一百零三页，编辑于2023年，星期日例7-13INSERT触发器在“学生”表中创建一个INSERT触发器，如果插入记录的院系编号值在“院系”表中存在下级单位（例如，计算机学院包括软件系），则不执行插入操作，并提示用户。

CREATETRIGGERinsert_studentON学生FORINSERTASDECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=所属院系FROMinserted79第七十九页，共一百零三页，编辑于2023年，星期日例7-13INSERT触发器--判断插入的院系记录是否存在上级记录SELECT@orgname=院系名称FROM院系WHERE上级编号=@orgidIF@orgname<>''--如果存在上级院系BEGIN

PRINT'指定院系存在下级单位，请选择具体单位！'ROLLBACKTRANSACTION ENDGO80第八十页，共一百零三页，编辑于2023年，星期日例7-13INSERT触发器

为了验证触发器是否正常工作，在查询分析器中执行如下语句：INSERTINTO学生VALUES('小朱',0,'二班',500,1)

因为院系编号为1的记录存在下级单位，所以返回结果如下：

指定院系存在下级单位，请选择具体单位！81第八十一页，共一百零三页，编辑于2023年，星期日例7-14UPDATE触发器在“学生”表中创建一个UPDATE触发器，如果修改记录的院系编号值在“院系”表中存在下级单位（例如，计算机学院包括软件系），则不执行修改操作，并提示用户。

CREATETRIGGERupdate_studentON学生FORUPDATEAS--从表inserted中获取更新后记录的院系编号

DECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=所属院系FROMinserted82第八十二页，共一百零三页，编辑于2023年，星期日例7-14UPDATE触发器--判断修改的院系记录是否存在上级记录SELECT@orgname=院系名称FROM院系WHERE上级编号=@orgidIF@orgname<>''BEGIN

PRINT'指定院系存在下级单位，请选择具体单位！'ROLLBACKTRANSACTION--回滚操作ENDGO83第八十三页，共一百零三页，编辑于2023年，星期日例7-14UPDATE触发器

为了验证触发器是否正常工作，在查询分析器中执行如下语句：UPDATE学生SET所属院系=1WHERE所属院系=2因为院系编号为1的记录存在下级单位，所以返回结果如下：

指定院系存在下级单位，请选择具体单位！84第八十四页，共一百零三页，编辑于2023年，星期日例7-15DELETE触发器在“院系”表中创建一个DELETE触发器，如果删除记录的院系编号值在“院系”表中存在下级单位（例如，计算机学院包括软件系），则不执行删除操作，并提示用户。CREATETRIGGERdelete_orgON院系FORDELETEAS

--从表deleted中获取删除记录的院系编号DECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=记录编号FROMdeleted85第八十五页，共一百零三页，编辑于2023年，星期日例7-15DELETE触发器--判断删除的院系记录是否存在下级记录SELECT@orgname=院系名称FROM院系WHERE上级编号=@orgidIF@orgname<>''BEGIN

PRINT'指定院系存在下级单位，不允许被删除！'

ROLLBACKTRANSACTION--回滚操作 ENDGO86第八十六页，共一百零三页，编辑于2023年，星期日例7-15DELETE触发器

为了验证触发器是否正常工作，在查询分析器中执行如下语句：

DELETEFROM院系WHERE记录编号=1因为院系编号为1的记录存在下级单位，所以返回结果如下：

指定院系存在下级单位，不允许被删除！87第八十七页，共一百零三页，编辑于2023年，星期日例7-16INSTEADOF触发器使用INSTEADOF触发器实现例7-15的功能。CREATETRIGGERdelete_org1ON院系INSTEADOFDELETEAS--从表deleted中获取删除记录的院系编号DECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=记录编号FROMdeleted88第八十八页，共一百零三页，编辑于2023年，星期日例7-16INSTEADOF触发器--判断修改的院系记录是否存在下级记录SELECT@orgname=院系名称FROM院系WHERE上级编号=@orgidIF@orgname<>''

PRINT'指定院系存在下级单位，不允许被删除！'

ELSE

DELETEFROM院系WHERE记录编号=@orgidGO89第八十九页，共一百零三页，编辑于2023年，星期日例7-16INSTEADOF触发器

因为INSTEADOF触发器使用触发器中定义的代码取代原操作，所以不需要进行回滚操作。当然，如果原操作符合规定的条件，还需要在触发器中重新执行此操作。为了验证触发器是否正常工作，在查询分析器中执行如下语句：DELETEFROM院系WHERE记录编号=1因为院系编号为1的记录存在下级单位，所以返回结果如下：指定院系存在下级单位，不允许被删除！（所影响的行数为1行）90第九十页，共一百零三页，编辑于2023年，星期日例7-17对特定列进行测试使用IFUPDATE(列名)子句实现例7-14的功能。CREATETRIGGERupdate_student1ON学生FORUPDATEAS--从表inserted中获取更新后记录的院系编号DECLARE@orgidintDECLARE@orgnamevarchar(100)IFUPDATE(所属院系)BEGINSELECT@orgid=所属院系FROMinserted

--判断修改的院系记录是否存在上级记录SELECT@orgname=院系名称FROM院系WHERE上级编号=@orgid