SQL Server 数据库技术 存储过程和触发器的创建与维护

会计学1SQLServer数据库技术存储过程和触发器的创建与维护本章学习目标了解存储过程的概念；掌握创建、执行存储过程的方法；掌握查看和修改存储过程的方法；掌握删除存储过程的方法；了解触发器和一般存储过程的主要区别；掌握创建和查看触发器的方法；掌握修改和删除触发器的方法。第1页/共120页8.1概述

8.1.1存储过程概述“存储过程”就是将常用的或很复杂的工作,预先以SQL程序写好,然后指定一个程序名称保存起来,以后只要用EXCUTE指令来执行这个程序,即可完成该项工作.第2页/共120页8.1.1存储过程概述应该提倡多使用存储过程，原因有以下几点。（1）实现了模块化编程。（2）调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。（3）存储过程可以接受输入参数并可以返回输出值。（4）存储过程具有对数据库立即访问的功能。（5）使用存储过程可以加快程序的运行速度。（6）使用存储过程可以减少网络流量。（7）使用存储过程可以提高数据库的安全性。第3页/共120页8.1.1存储过程概述

存储过程分为两类：系统存储过程和用户自定义的存储过程。系统存储过程是由系统自动创建的，主要存储在master数据库中，一般以sp_为前缀。系统存储过程完成的功能主要是从系统表中获取信息。进行系统的各项设置、相关管理工作。用户自定义存储过程由用户创建并能完成某一特定功能的存储过程。会被加入所属数据库的存储过程项目中，并以对象的形式保存。第4页/共120页8.2存储过程

8.2.1创建存储过程创建存储过程的三种方法：使用创建存储过程向导创建存储过程。使用企业管理器创建存储过程。使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。第5页/共120页1．使用创建存储过程向导创建存储过程（1）在企业管理器中，要创建存储过程的数据库，选择“工具”菜单中的“向导”菜单项，单击向导中“数据库”选项左边的加号，选中“创建存储过程向导”选项。第6页/共120页

（2）单击“确定”按钮。出现“欢迎使用创建存储过程”对话框，如图所示。第7页/共120页（3）选择数据库对话框．选择存放存储过程的数据库。第8页/共120页（4）“选择存储过程”对话框。选择存储过程中要针对哪些表做哪些操作。第9页/共120页（5）确认存储过程信息对话框向导会针对每一个数据表的每一个操作，都产生单独的存储过程来处理。按此按钮可编辑选定项目的设置。第10页/共120页（6）“编辑存储过程属性”对话框更改存储过程的名称。按此按钮可直接修改SQL程序代码。executeinsert_读者基本信息表_1'67','女','fggg',7,9第11页/共120页2．使用SQLServer2000企业管理器创建存储过程步骤如下：（1）在企业管理器中，选择指定的服务器和数据库，右击要创建存储过程的数据库，在弹出的快捷菜单中依次选择“新建”→“存储过程…”命令。第12页/共120页（2）在文本框中可以输入创建存储过程的T-SQL语句。（3）输入完毕可单击“检查语法”按钮。（4）最后单击“确定”保存。默认的样板。CREATEPROCEDURE存储过程_读者信息ASSELECT读者编号,姓名,性别,所在部门from读者基本信息表第13页/共120页3．使用Transact-SQL语句创建存储过程可以使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程，只能在当前数据库中创建存储过程。第14页/共120页3．使用Transact-SQL语句创建存储过程语法形式如下：CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASsql_statement[...n]第15页/共120页procedure_name：用于指定所要创建存储过程的名称。各参数的含义：第16页/共120页@parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。执行时应提供相应的实在参数.data_type：用于指定参数的数据类型。default：用于指定参数的默认值(常量)。各参数的含义：

[{@parameterdata_type}

第17页/共120页AS：用于指定该存储过程要执行的操作。sql_statement：是存储过程中包含的任意数目和类型的Transact-SQL语句。各参数的含义：第18页/共120页CREATEPROCEDURE<存储过程名>[{@参数名><数据类型>}]AS{<SQL语句>|<语句块>}第19页/共120页例:从STUDENT数据库的三个表中查询,返回学生学号、姓名、课程名、成绩、学分。USESTUDENTIFEXISTS(SELECTnameFROMsysobjectsWHEREname='student_info'ANDtype='P')DROPPROCEDUREstudent_infoGOCREATEPROCEDUREstudent_infoASSelecta.学号,姓名,课程名,成绩,学分Fromstudenta,scb,coursecWHEREa.学号=b.学号Andb.课号=c.课号GOEXECUTEstudent_info简单的存储过程第20页/共120页练习：从STUDENT数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。USESTUDENTIFEXISTS(SELECTnameFROMsysobjectsWHEREname='student_info1'ANDtype='P')DROPPROCEDUREstudent_info1GOCREATEPROCEDUREstudent_info1@namechar(8),@cnamechar(16)ASSelecta.学号,姓名,课程名,成绩,学分Fromstudenta,scb,coursecWHEREa.学号=b.学号Andb.课号=c.课号anda.姓名=@nameandc.课程名=@cnameGO带参数的存储过程第21页/共120页EXECUTEstudent_info1'王丽','计算机基础'EXECUTEstudent_info1@name='王丽',@cname='计算机基础‘第22页/共120页练习：从STUDENT数据库的三个表中返回指定学生的学号、姓名、所选课程名及该课的成绩。该存储过程在参数中可使用模式匹配，如果没提供参数，则使用预设的默认值．USESTUDENTIFEXISTS(SELECTnameFROMsysobjectsWHEREname='student_info3'ANDtype='P')DROPPROCEDUREstudent_info3GOCREATEPROCEDUREstudent_info3@namevarchar(30)='刘%'ASSelecta.学号,姓名,课程名,成绩Fromstudenta,scb,coursecWHEREa.学号=b.学号Andb.课号=c.课号and姓名like@nameGO带参数的存储过程第23页/共120页EXECUTEstudent_info3/*参数使用默认值*/EXECUTEstudent_info3'王%'/*传递给＠name的实参为‘王％’*/EXECUTEstudent_info3'[王张]%'[]（方括号）指定范围（[a-f]）或集合（[abcdef]）中的任何单个字符。第24页/共120页

创建一个带有参数的存储过程“存储过程_读者借阅信息”，该存储过程根据传入的读者编号，返回此人的基本信息及此人借阅的图书信息。例8-1第25页/共120页USEBOOKSIFEXISTS(SELECTnameFROMsysobjectsWHEREname='存储过程_读者借阅信息'ANDtype='P')DROPPROCEDURE存储过程_读者借阅信息GOCREATEPROCEDURE存储过程_读者借阅信息

@读者编号varchar(12)ASSelect读者基本信息表.读者编号,姓名,性别,所在部门,图书借阅信息表.图书编码,书名,借阅日期From图书基本信息表,图书借阅信息表,读者基本信息表WHERE读者基本信息表.读者编号=图书借阅信息表.读者编号and图书基本信息表.图书编码=图书借阅信息表.图书编码And读者基本信息表.读者编号=@读者编号GOexec存储过程_读者借阅信息'JSJ03'第26页/共120页

如果student表中男生入学成绩的平均值高于女生入学成绩的平均值则显示“男生的入学成绩平均值高于女生的入学成绩平均值”，否则显示“男生的入学成绩平均值低于女生的入学成绩平均值”.补例1：建立判断男学生与女学生的平均入学成绩高低的存储过程。第27页/共120页USEstudentGOCREATEPROCEDURE比较男女生入学成绩ASIf((selectavg(入学成绩)FromstudentWhere性别='男')>(selectavg(入学成绩)FromstudentWhere性别='女'))Print'男生的入学成绩平均值高于女生的入学成绩平均值'ElsePrint'男生的入学成绩平均值低于女生的入学成绩平均值'GO第28页/共120页参数传递技巧执行存储过程时,若未指明参数名称,则必须依照存储过程所需的参数依次传过去;而且除非该参数指定有默认值,否则不可省略.第29页/共120页补例2:CREATEPROCEDUREtest@aint,@bint=NULL,@cint=3ASSELECT@a,@b,@cGO第30页/共120页CREATEPROCEDUREtest@aint,@bint=NULL,@cint=3ASSELECT@a,@b,@cGOEXECtest

GOEXECtest1

GOEXECtest1,default

GOEXECtest1,default,5

GOEXECtest1,2,5

GOEXECtest/*错误,第一个参数不可省略*/GOEXECtest1/*OK,第2、3参数用默认值*/GOEXECtest1,default/*OK,可用default表示使用默认值*/GOEXECtest1,default,5/*OK*/GOEXECtest1,2,5/*OK*/GO第31页/共120页也可以使用在存储过程中声明的参数名称，以“@name=value”格式来指明传入参数的对应位置。例：EXECtest@c=5,@b=DEFAULT,@a=1/*不用按顺序传了*/判断正误：EXECtest1,@c=2EXECtest@c=2,1GOEXECtest@c=5EXECtest1,@c=2/*OK!1传入@a,而@b使用默认值*/GOEXECtest@c=2,1/*错误!因为使用过@name=value后就必须直使用此方式来传参数*/GOEXECtest@c=5/*错误!因为@a参数不可省略*/CREATEPROCEDUREtest@aint,@bint=NULL,@cint=3ASSELECT@a,@b,@cGO第32页/共120页8.2.2管理存储过程1．查看存储过程

存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。

可以使用企业管理器或系统存储过程来查看用户创建的存储过程。第33页/共120页1．查看存储过程

在企业管理器中查看用户创建的存储过程的方法如下:1）在企业管理器中，打开指定的服务器和数据库项，并单击存储过程文件夹，此时在右边的窗格中就会显示出数据库中的所有存储过程。第34页/共120页（2）右击要查看的存储过程，从弹出的快捷菜单中选择“属性”命令，会弹出“存储过程属性”对话框。第35页/共120页(3)右击存储过程“存储过程_读者借阅信息”,从弹出的快捷菜单中依次选择”所有任务”-”显示相关性”命令,会弹出相关对话框,显示与选择的存储过程有依赖关系的其他数据库对象的名称,如图所示.第36页/共120页使用系统存储过程来查看用户创建的存储过程可供使用的系统存储过程及其语法形式如下：（1）sp_help：用于显示存储过程的参数及其数据类型

sp_help[[@objname=]name]参数name为要查看的存储过程的名称。第37页/共120页例8-2使用系统存储过程查看“存储过程_读者借阅信息”的参数及其数据类型。程序清单如下：--查看存储过程的参数及其数据类型USEBOOKSGOsp_help存储过程_读者借阅信息GO第38页/共120页（2）sp_helptext：用于显示存储过程的源代码sp_helptext[[@objname=]name]参数name为要查看的存储过程的名称。第39页/共120页例:使用系统存储过程查看“存储过程_读者借阅信息”的源代码。程序清单如下：--查看存储过程的参数及其数据类型USEBOOKSGOsp_helptext

存储过程_读者借阅信息GO第40页/共120页（3）sp_depends：用于显示和存储过程相关的数据库对象sp_depends[@objname=]’object’

参数object为要查看依赖关系的存储过程的名称。第41页/共120页例8-3使用系统存储过程查看“存储过程_读者借阅信息”的相关的数据库对象。程序清单如下：--查看相关的数据库对象USEBOOKSGOsp_depends存储过程_读者借阅信息GO第42页/共120页（4）sp_stored_procedures：用于返回当前数据库中的存储过程列表sp_stored_procedures[[@sp_name=]'name'][,[@sp_owner=]'owner'][,[@sp_qualifier=]'qualifier']

用于指定返回目录信息的过程名。用于指定过程所有者的名称；用于指定过程限定符的名称。（表示数据库名称)第43页/共120页USEBOOKSExecsp_stored_proceduresGO第44页/共120页USEBOOKSExecsp_stored_procedures存储过程_读者借阅信息,dbo,booksGO第45页/共120页USEBOOKSexecsp_stored_procedures'%借%'GO第46页/共120页2．修改存储过程定义(1)在企业管理器中，单击目录树中的存储过程图标，在窗口的右侧右击要修改的存储过程，从弹出的快捷菜单中选择“属性”命令，则会出现存储过程属性对话框。第47页/共120页(2)T-SQL语句的修改存储过程:其语法形式如下：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]第48页/共120页简要格式:ALTERPROCEDURE<存储过程名>[{@参数名><数据类型>}]AS{<SQL语句>|<语句块>}第49页/共120页练习：创建名为select_students的存储过程，默认情况下，该过程可查询所有学生信息；

再重新改为能检索计算机专业的学生信息的存储过程。USESTUDENTIFEXISTS(SELECTnameFROMsysobjectsWHEREname='select_students'ANDtype='P')DROPPROCEDUREselect_studentsGOCREATEPROCEDUREselect_studentsASSelect*Fromstudentorderby学号GO建立过程第50页/共120页ALTERPROCEDUREselect_studentsASSelect*Fromstudentwhere院系名称='计算机系'orderby学号GO修改过程第51页/共120页3．重命名存储过程(1)在企业管理器中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”命令，或者双击存储过程名称，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称了。第52页/共120页(2)也可以使用系统存储过程sp_rename修改存储过程的名称，其语法形式如下：sp_rename原存储过程名称，新存储过程名称第53页/共120页8.2.3执行存储过程

在SQLServer2000中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：

[EXEC[UTE]]存储过程名[参数]

第54页/共120页程序清单如下：USEBOOKSEXEC存储过程_读者信息或直接写存储过程的名称：USEBOOKSGO存储过程_读者信息例8-4：执行前面创建的无参存储过程“存储过程_读者信息”。第55页/共120页存储过程_读者借阅信息创建一个带有参数的存储过程“存储过程_读者借阅信息”，该存储过程根据传入的读者编号，返回此人的基本信息及此人借阅的图书信息。第56页/共120页条件（再运行一下）USEBOOKSIFEXISTS(SELECTnameFROMsysobjectsWHEREname='存储过程_读者借阅信息'ANDtype='P')DROPPROCEDURE存储过程_读者借阅信息GOCREATEPROCEDURE存储过程_读者借阅信息

@读者编号varchar(12)ASSelect读者基本信息表.读者编号,姓名,性别,所在部门,图书借阅信息表.图书编码,书名,借阅日期From图书基本信息表,图书借阅信息表,读者基本信息表WHERE读者基本信息表.读者编号=图书借阅信息表.读者编号and图书基本信息表.图书编码=图书借阅信息表.图书编码And读者基本信息表.读者编号=@读者编号GO第57页/共120页例8-5执行有参存储过程“存储过程_读者借阅信息”，该存储过程有一个输入参数“读者编号”。程序清单如下：USEBOOKSGOEXECUTE存储过程_读者借阅信息'JSJ03'或：USEBOOKSGOEXECUTE存储过程_读者借阅信息@读者编号='JSJ03’第58页/共120页8.2.4删除存储过程1．使用企业管理器删除存储过程

在企业管理器中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”命令，会弹出“除去对象”对话框。在该对话框中，单击“全部除去”按钮，即可完成删除操作。第59页/共120页2．使用Transact-SQL语句删除存储过程删除存储过程也可以使用Transact-SQL语言中的DROP命令;DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：dropprocedure{procedure}[,…n]指定过程名称。可删除多个过程。第60页/共120页程序清单如下：USEBOOKSGODropprocedure

存储过程_读者借阅信息GO例8-6使用DROP命令删除存储过程“存储过程_读者借阅信息”。第61页/共120页8.1.2触发器概述触发器是一种特殊类型的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。

当数据表有INSERT、UPDATE、DELETE事件发生时，所设置的触发器即会自动被执行，以进行维护数据完整性，或其他数据处理工作。就是当表内容被更改时，会自动执行的存储过程。第62页/共120页数据的完整性（１）实体完整性。（２）域完整性。（３）参照完整性。1、实体完整性要求数据表中的每一行在表中是惟一的实体。2、采用三种方式主键约束：IDENTITY属性：1、用来保证在数据表中输入有效的数据值。方法定义字段的数据类型、设置字段非空检查约束默认约束默认规则默认值。对输入的数据值进行检查，满足条件可以保存在数据表中，反之则拒绝接受。惟一性约束：规则是对存储在数据表中的数据值的规定和限制。设置参照完整性是为了保证数据表之间的数据保持一致，通过在两个数据表之间的主键和外键之间或惟一键和外键之间建立外键约束来实现。约束特点:直接设置于表内,不需编程;只能进行比较简单的操作;第63页/共120页触发器作用触发器可以用于SQLServer2000约束、默认值和规则的完整性检查；和CHECK约束相比较，触发器是特殊的存储过程，由于是编写的程序,可以强制实现更加复杂的数据完整性，而且可以引用其他表中的字段。注：简单的工作应尽量用约束来完成，这样容易设置及维护，执行效率也比较好。只有当它无法满足需要时考虑使用触发器。第64页/共120页补：触发器的“特异功能”检查所作的更改是否允许（更多样更复杂的检查：同时检查多个数据表、用IF…ELSE等来作更弹性的检查）；自定义错误信息（不是原来的固定信息，而是返回自定义的错误信息）；进行其它相关数据的更改动作第65页/共120页触发器分类

触发器分为两种，即AFTER触发器和INSTEADOF触发器。AFTER触发器：这种类型的触发器将在数据变动（INSERT、UPDATE和DELETE操作）完成以后才被触发。可以对变动的数据进行检查，如果发现错误，将拒绝接受或回滚变动的数据。AFTER触发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。第66页/共120页INSTEADOF触发器：这种类型的触发器将在数据变动以前被触发，并取代变动数据的操作（INSERT、UPDATE和DELETE操作），而去执行触发器定义的操作。在表或视图上，每个INSERT、UPDATE和DELETE语句最多只可以定义一个INSTEADOF触发器。第67页/共120页8.3触发器

8.3.1创建触发器只能在当前数据库中创建触发器，创建触发器时可以引用其他数据库中的对象。只有表的所有者有权创建触发器，且不能将该权限转给其他用户。创建方法：使用企业管理器或者Transact-SQL语句来创建触发器。第68页/共120页1．使用企业管理器创建触发器步骤如下：在企业管理器中展开BOOKS数据库，右击“图书基本信息表”，从弹出的快捷菜单中依次选择“所有任务”→“管理触发器”命令。出现“触发器属性”对话框，如图所示。默认是新建一个触发器填上内容进行语法检查。第69页/共120页可在此查看或修改已建好的触发器的内容。按此按钮可删除当前触发器。第70页/共120页（2）在“触发器属性”对话框中，在“名称”文本框中选择“新建”，然后在“文本”框中输入创建触发器的文本。当创建一个触发器时必须指定以下几项内容：1）触发器的名称；2）在其上定义触发器的表；3）触发器将何时激发；4）执行触发操作的编程语句。这里在“文本”框中输入以一文本：（功能：创建一个INSERT触发器）CREATETRIGGER触发器_图书信息表_插入ON图书基本信息表FORINSERTASDECLARE@提示char(30)SET@提示='你插入了一条新记录!'PRINT@提示当在“图书基本信息表”中插入一条新记录时，触发该触发器，并给出“你插入了一条新记录！”的提示信息。第71页/共120页USEBOOKSGOInsertinto图书基本信息表(图书编码)Values(‘100009')GO例8-7创建了“触发器_图书信息表_插入”后，查看向图书基本信息表中插入数据时，此触发器所完成的功能。第72页/共120页2．使用Transact-SQL语句创建触发器创建触发器的语法：CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}[WITHAPPEND][NOTFORREPLICATION]第73页/共120页AS[{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}]sql_statement[...n]}}第74页/共120页CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}

ASsql_statement[...n]用于指定触发器的名称。在所属数据库中必须是唯一的。用于指定在其上执行触发器的表或视图。（只有INSTEADOF触发器才能设置于视图上。用于加密syscomments表中包含CREATETRIGGER语句文本的条目。

FOR/AFTER类触发器：只有在数据表的操作都已正确完成后才会激活触发器；INSTEADOF类触发器：用触发器替代原来要执行的数据操作。用于指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。如果指定的选项多于一个，需用逗号分隔这些选项。对于INSTEADOF类限制三项最多只能有一个。而AFTER类无限制。用来定义触发器的内容。第75页/共120页

在创建触发器时，可以使用两个特殊的临时表：inserted表和deleted表，这两个表都存在于内存中。通过二表可知哪些数据已被新建、修改或删除。触发器可检查deleted表、inserted表及被修改的表。两个特殊的表：第76页/共120页在inserted表中存储着被INSERT和UPDATE语句影响的新的数据行。在执行INSERT或UPDATE语句时，新的数据行被添加到基本表中，同时这些数据行的备份

被复制到inserted临时表中。第77页/共120页在deleted表中存储着被DELETE和UPDATE语句影响的旧数据行。在执行DELETE或UPDATE语句时，指定的数据行从基本表中删除，然后被转移到了deleted表中。在基本表和deleted表

中一般不会存在相同的数据行。第78页/共120页一个UPDATE操作实际上是由一个DELETE操作和一个INSERT操作组成的。在执行UPDATE操作时，旧的数据行从基本表中转移到deleted表中，然后将新的数据行同时插入基本表和inserted表中。第79页/共120页总结Inserted数据表Deleted数据表在插入时存放要插入的记录在修改时存放要更新的记录存放更新前的旧记录在删除时存放被删除的旧记录若要检索Deleted和Inserted表中的所有记录：可使用如下的语句：SELECT*FORMDeletedSELECT*FORMInserted第80页/共120页补例：对于BOOKS数据库，如果在图书基本信息表中添加或更改数据，则向用户显示一条信息。第81页/共120页USEBOOKSGO--如果已经存在“显示信息_触发器”，则先删除此触发器IFEXISTS(SELECTnameFROMsysobjectsWHEREname='显示信息_触发器'ANDtype='TR')DROPTRIGGER显示信息_触发器GO--创建“显示信息_触发器”CREATETRIGGER显示信息_触发器ON图书基本信息表FORINSERT,UPDATEASPRINT‘尊敬的用户：您添加或修改了一条数据'GO运行：INSERTINTO图书基本信息表(图书编码,书名,作者,定价)VALUES('100068','SQLSERVER数据库基础','王鹏',28)第82页/共120页补例：创建一个AFTER触发器，要求实现以下功能：

在图书基本信息表上创建一个插入类型的触发器“触发器_定价检查1”，当在定价字段中插入数据后，触发该触发器，检查定价的数据值是否大于0。第83页/共120页USEBOOKSGO--如果已经存在“触发器_定价检查1”，则先删除此触发器IFEXISTS(SELECTnameFROMsysobjectsWHEREname='触发器_定价检查1'ANDtype='TR')DROPTRIGGER触发器_定价检查1GO--创建“触发器_定价检查1”CREATETRIGGER触发器_定价检查1ON图书基本信息表FORINSERTASIF(SELECT定价FROMinserted)<=0BEGINPRINT'输入的定价数据应该大于0!'ROLLBACKENDGO数据回滚。在sql语句执行失败时，使用了此语句，可以将数据回滚到sql语句执行前的状态。第84页/共120页例8-8创建一个AFTER触发器，要求实现以下功能：在图书基本信息表上创建一个插入、更新类型的触发器“触发器_定价检查2”，当在定价字段中插入或修改数据后，触发该触发器，检查定价的数据值是否大于0。第85页/共120页USEBOOKSGO--如果已经存在“触发器_定价检查2”，则先删除此触发器IFEXISTS(SELECTnameFROMsysobjectsWHEREname='触发器_定价检查2'ANDtype='TR')DROPTRIGGER触发器_定价检查2GO--创建“触发器_定价检查2”CREATETRIGGER触发器_定价检查2ON图书基本信息表FORINSERT,UPDATEASIFUPDATE(定价)BEGINPRINT'AFTER触发器开始执行……'DECLARE@定价realSELECT@定价=(SELECT定价FROMinserted)IF@定价<=0PRINT'输入的定价数据应该大于0!'ENDGO用于测试在指定的列上进行的INSERT或UPDATE操作。做二操作时都返回TRUE值。第86页/共120页USEBOOKSGOPRINT'在图书基本信息表中插入记录时触发器的执行结果:'PRINT''INSERTINTO图书基本信息表(图书编码,书名,作者,定价)VALUES('100068','SQLSERVER数据库基础','王鹏',28)INSERTINTO图书基本信息表(图书编码,书名,作者,定价)VALUES('100078','SQLSERVER学习指南','李东方',-35)GOPRINT'在图书基本信息表中修改记录时触发器的执行结果:'PRINT''UPDATE图书基本信息表SET定价=-29WHERE图书编码='100005'UPDATE图书基本信息表SET定价=-65WHERE图书编码='100006'GO第87页/共120页

例8-9创建一个AFTER触发器，要求如下：

在“读者基本信息表”上创建一个修改类型的触发器“触发器_级联修改图书借阅信息表”，当在“读者基本信息表”中修改某个记录的读者编号后，触发该触发器，在“图书借阅信息表”中查找此人是否借阅了图书，如果在图书借阅信息表中存在此人的借阅信息，则级联修改此人的读者编号，使之与读者基本信息表中修改后的读者编号一致。第88页/共120页USEBOOKSGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='触发器_级联修改图书借阅信息表'ANDtype='TR')DROPTRIGGER触发器_级联修改图书借阅信息表GOCREATETRIGGER触发器_级联修改图书借阅信息表ON读者基本信息表FORUPDATEASPRINT'“触发器_级联修改图书借阅信息表”开始执行…….'DECLARE@读者编号_修改前char(10)DECLARE@读者编号_修改后char(10)PRINT'把在读者基本信息表中修改前的记录的读者编号赋值给局部变量@读者编号_修改前。'SELECT@读者编号_修改前=读者编号FROMDELETEDPRINT'把在读者基本信息表中修改后的记录的读者编号赋值给局部变量@读者编号_修改后。'SELECT@读者编号_修改后=读者编号FROMINSERTEDPRINT'开始查找并修改图书借阅信息表相关记录…'IF(SELECTCOUNT(*)FROM图书借阅信息表WHERE读者编号=@读者编号_修改前)>0BEGINupdate图书借阅信息表set读者编号=@读者编号_修改后where读者编号=@读者编号_修改前

print'已经将图书借阅信息表中的读者编号为：'+rtrim(@读者编号_修改前)+'的所有记录的读者编号修改为：'+rtrim(@读者编号_修改后)+'。'end第89页/共120页CREATETRIGGER触发器_级联修改图书借阅信息表ON读者基本信息表FORUPDATEASPRINT'“触发器_级联修改图书借阅信息表”开始执行…….'DECLARE@读者编号_修改前char(10)DECLARE@读者编号_修改后char(10)PRINT'把读者基本信息表中修改前的记录的读者编号赋值给局部变量@读者编号_修改前。'SELECT@读者编号_修改前=读者编号FROMDELETEDPRINT'把读者基本信息表中修改后的记录的读者编号赋值给局部变量@读者编号_修改后。'SELECT@读者编号_修改后=读者编号FROMINSERTEDPRINT'开始查找并修改图书借阅信息表相关记录…'IF(SELECTCOUNT(*)FROM图书借阅信息表WHERE读者编号=@读者编号_修改前)>0BEGINupdate图书借阅信息表set读者编号=@读者编号_修改后where读者编号=@读者编号_修改前

print'已经将图书借阅信息表中的读者编号为：'+rtrim(@读者编号_修改前)+'的所有记录的读者编号修改为：'+rtrim(@读者编号_修改后)+'。'end第90页/共120页运行：USEBOOKSGO--修改未借阅图书的诗编号update读者基本信息表set读者编号='JSJ05'where读者编号='JSJ04'go第91页/共120页update读者基本信息表set读者编号='JSJ05‘where读者编号='JSJ04'DELETEDINSERTED图书借阅信息表中有无读者编号为‘JSJ0４‘的？修改图书借阅信息表中‘JSJ0４‘为'JSJ05‘

例8-9流程分析图第92页/共120页USEBOOKSGO--修改已借阅图书的读者编号update读者基本信息表set读者编号='JJX02'where读者编号='JJX01'go运行：第93页/共120页8.3.2管理触发器1．查看触发器企业管理器方法如下：（1）查看触发器定义信息。

在企业管理器中，展开选定数据库，并右击其中的某个数据表，从弹出的快捷菜单中依次选择“所有任务”→“管理触发器”命令，会打开触发器属性对话框。第94页/共120页在“对象”下拉列表框中可以选择要查看的数据库对象名称。左边的页框中会显示依赖于该对象的其他对象，右边的页框中显示的是该对象依赖的其他对象。（2）查看与触发器有依赖关系的其他数据库对象右击图书基本信息表，从弹出的快捷菜单中依次选择“所有任务”→“显示相关性”命令，会出现相关性对话框。第95页/共120页

使用系统存储过程sp_help、sp_helptext和sp_depends可分别查看触发器的不同信息。sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。格式：sp_help‘触发器名称’例：sp_help

触发器_定价检查2使用系统存储过程查看触发器第96页/共120页sp_helptext：用于查看触发器的正文信息。

sp_helptext‘触发器名称’例：sp_helptext触发器_定价检查2第97页/共120页sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。

sp_depends'触发器名称'

sp_depends‘表名’例：sp_depends

触发器_定价检查2sp_depends

读者基本信息表第98页/共120页例8-10使用系统存储过程查看“触发器_级联修改图书借阅信息表”的一般信息。程序清单如下：USEBOOKSGOsp_help'触发器_级联修改图书借阅信息表'GO第99页/共120页例8-11使用系统存储过程查看“触发器_级联修改图书借阅信息表”涉及的数据表的相关信息。程序清单如下：USEBOOKSGOsp_depends'触发器_级联修改图书借阅信息表'GO第100页/共120页2．修改触发器修改触发器包括修改触发器的正文和修改触发器的名称。展开选定数据库，右击某一个数据表（从弹出的快捷菜单中依次选择“所有任务”→“管理触发器”命令。出现如图所示对话框。在名称选项框中选择要修改的触发器的名称，然后在文本框中修改触发器的SQL语句，单击“检查语法”按钮，可以检查语法是否正确。第101页/共120页2．修改触发器

使用Transact-SQL语句中的altertrigger命令来修改触发器正文。altertrigger命令的语法形式如下：ALTERTRIGGERtrigger_nameON(table|view)[WITHENCRYPTION]{第102页/共120页{(FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}[NOTFORREPLICATION]ASsql_statement[...n]}|第103页/共120页{(FOR|AFTER|INSTEADOF){[INSERT][,]UPDATE}}[NOTFORREPLICATION]AS{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]第104页/共120页|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}sql_statement[...n]}}第105页/共120页简要格式：ALTERTRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASsql_statement[...n]第106页/共120页2．利用系统存储过程修改触发器名称

可以使用系统存储过程sp_rename来修改触发器的名称。sp_rename命令的语法形式如下：sp_renameoldname,newname第107页/共120页8.3.3删除触发器只有触发器所有者才有权删除触发器。删除已创建的触发器有三种方法：直接删除触发器所在的数据表使用企业管理器删除触发器使用Transact-SQL语句删除触发器第108页/共120页1．直接删除触发器所在的数据表如果触发器所在的数据表已经不再使用了，可以直接删除此数据表。删除数据表时，SQLServer2000将会自动删除与该表相关的所有触发器。第109页/共120页2．使用企业管理器删除触发器在企业管理器中，右击要删除的触发器所在的数据表，从弹出的快捷菜单中依次选择“所有任务”→“管理触发器”命令，会出现触发器属性对话框。在“名称”选项框中选择要删除的触发器，然后单击“删除”按钮，即可删除该触发器。第110页/共120