《SQL Server 数据库技术存储过程和触发器的创建与维护.pptx》由会员分享，可在线阅读，更多相关《SQL Server 数据库技术存储过程和触发器的创建与维护.pptx（120页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、本章学习目标了解存储过程的概念；掌握创建、执行存储过程的方法；掌握查看和修改存储过程的方法；掌握删除存储过程的方法；了解触发器和一般存储过程的主要区别；掌握创建和查看触发器的方法；掌握修改和删除触发器的方法。第1页/共120页8.1 概述8.1.1 存储过程概述“存储过程”就是将常用的或很复杂的工作,预先以SQL程序写好,然后指定一个程序名称保存起来,以后只要用EXCUTE指令来执行这个程序,即可完成该项工作.第2页/共120页8.1.1 存储过程概述应该提倡多使用存储过程，原因有以下几点。（1）实现了模块化编程。（2）调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。（3）存储过
2、程可以接受输入参数并可以返回输出值。（4）存储过程具有对数据库立即访问的功能。（5）使用存储过程可以加快程序的运行速度。（6）使用存储过程可以减少网络流量。（7）使用存储过程可以提高数据库的安全性。第3页/共120页8.1.1 存储过程概述 存储过程分为两类：系统存储过程和用户自定义的存储过程。系统存储过程n是由系统自动创建的，主要存储在master数据库中，一般以sp_为前缀。n系统存储过程完成的功能主要是从系统表中获取信息。进行系统的各项设置、相关管理工作。用户自定义存储过程n由用户创建并能完成某一特定功能的存储过程。n会被加入所属数据库的存储过程项目中，并以对象的形式保存。第4页/共12
3、0页8.2 存储过程8.2.1 创建存储过程创建存储过程的三种方法：使用创建存储过程向导创建存储过程。使用企业管理器创建存储过程。使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。第5页/共120页1使用创建存储过程向导创建存储过程（1）在企业管理器中，要创建存储过程的数据库，选择“工具”菜单中的“向导”菜单项，单击向导中“数据库”选项左边的加号，选中“创建存储过程向导”选项。第6页/共120页 （2）单击“确定”按钮。出现“欢迎使用创建存储过程”对话框，如图所示。第7页/共120页（3）选择数据库对话框选择存放存储过程的数据库。第8页/共120页（4）“选
4、择存储过程”对话框。选择存储过程中要针对哪些表做哪些操作。第9页/共120页（5）确认存储过程信息对话框向导会针对每一个数据表的每一个操作，都产生单独的存储过程来处理。按此按钮可编辑选定项目的设置。第10页/共120页（6）“编辑存储过程属性”对话框更改存储过程的名称。按此按钮可直接修改SQL程序代码。execute insert_读者基本信息表_1 67,女,fggg,7,9第11页/共120页2使用SQL Server 2000企业管理器创建存储过程步骤如下：（1）在企业管理器中，选择指定的服务器和数据库，右击要创建存储过程的数据库，在弹出的快捷菜单中依次选择“新建”“存储过程”命令。第1
5、2页/共120页（2 2）在文本框中可以输入创建存储过程的）在文本框中可以输入创建存储过程的T-T-SQLSQL语句。语句。（3 3）输入完毕可单击）输入完毕可单击“检查语法检查语法”按钮。按钮。（4 4）最后单击）最后单击“确定确定”保存。保存。默认的样板。CREATE PROCEDURE 存储过程_读者信息AS SELECT 读者编号,姓名,性别,所在部门 from 读者基本信息表第13页/共120页3使用Transact-SQL语句创建存储过程可以使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程，只能在当前数据库中创建存储过程。第14页/共120页3使
6、用Transact-SQL语句创建存储过程语法形式如下：CREATE PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n 第15页/共120页procedure_name：用于指定所要创建存储过程的名称。各参数的含义：第16页/共120页parameter：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个
7、参数。执行时应提供相应的实在参数.data_type：用于指定参数的数据类型。default：用于指定参数的默认值(常量)。各参数的含义：parameter data_type parameter data_type第17页/共120页AS：用于指定该存储过程要执行的操作。sql_statement：是存储过程中包含的任意数目和类型的Transact-SQL语句。各参数的含义：第18页/共120页CREATE PROCEDURE 参数名 AS|第19页/共120页例:从STUDENT数据库的三个表中查询,返回学生学号、姓名、课程名、成绩、学分。USE STUDENTIF EXISTS(SELE
8、CT name FROM sysobjects WHERE name=student_info AND type=P)DROP PROCEDURE student_info GOCREATE PROCEDURE student_infoASSelect a.学号,姓名,课程名,成绩,学分From student a,sc b,course cWHERE a.学号=b.学号 And b.课号=c.课号GOEXECUTE student_infoEXECUTE student_info简单的存储过程第20页/共120页练习：从STUDENT数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接
9、受与传递参数精确匹配的值。USE STUDENTIF EXISTS(SELECT name FROM sysobjects WHERE name=student_info1 AND type=P)DROP PROCEDURE student_info1GOCREATE PROCEDURE student_info1 name char(8),cname char(16)ASSelect a.学号,姓名,课程名,成绩,学分From student a,sc b,course cWHERE a.学号=b.学号 And b.课号=c.课号 and a.姓名=name and c.课程名=cnameG
10、O带参数的存储过程第21页/共120页EXECUTE student_info1 王丽,计算机基础EXECUTE student_info1 name=王丽,cname=计算机基础第22页/共120页练习：从STUDENT数据库的三个表中返回指定学生的学号、姓名、所选课程名及该课的成绩。该存储过程在参数中可使用模式匹配，如果没提供参数，则使用预设的默认值USE STUDENTIF EXISTS(SELECT name FROM sysobjects WHERE name=student_info3 AND type=P)DROP PROCEDURE student_info3 GOCREATE
11、 PROCEDURE student_info3 name varchar(30)=刘%ASSelect a.学号,姓名,课程名,成绩From student a,sc b,course cWHERE a.学号=b.学号 And b.课号=c.课号 and 姓名 like name GO带参数的存储过程第23页/共120页EXECUTE student_info3 /*参数使用默认值*/EXECUTE student_info3 王%/*传递给name的实参为王*/EXECUTE student_info3 王张%（方括号）指定范围（a-f）或集合（abcdef）中的任何单个字符。第24页/共
12、120页 创建一个带有参数的存储过程“存储过程_读者借阅信息”，该存储过程根据传入的读者编号，返回此人的基本信息及此人借阅的图书信息。例8-1第25页/共120页USE BOOKSIF EXISTS(SELECT name FROM sysobjects WHERE name=存储过程_读者借阅信息 AND type=P)DROP PROCEDURE 存储过程_读者借阅信息GOCREATE PROCEDURE 存储过程_读者借阅信息 读者编号 varchar(12)ASSelect 读者基本信息表.读者编号,姓名,性别,所在部门,图书借阅信息表.图书编码,书名,借阅日期From 图书基本信息表
13、,图书借阅信息表,读者基本信息表WHERE 读者基本信息表.读者编号=图书借阅信息表.读者编号 and 图书基本信息表.图书编码=图书借阅信息表.图书编码And 读者基本信息表.读者编号=读者编号GOexec 存储过程_读者借阅信息 JSJ03第26页/共120页 如果student表中男生入学成绩的平均值高于女生入学成绩的平均值则显示“男生的入学成绩平均值高于女生的入学成绩平均值”，否则显示“男生的入学成绩平均值低于女生的入学成绩平均值”.补例1：建立判断男学生与女学生的平均入学成绩高低的存储过程。第27页/共120页USE studentGOCREATE PROCEDURE 比较男女生入学
14、成绩ASIf(select avg(入学成绩)From studentWhere 性别=男)(select avg(入学成绩)From student Where 性别=女)Print 男生的入学成绩平均值高于女生的入学成绩平均值ElsePrint 男生的入学成绩平均值低于女生的入学成绩平均值GO第28页/共120页参数传递技巧执行存储过程时,若未指明参数名称,则必须依照存储过程所需的参数依次传过去;而且除非该参数指定有默认值,否则不可省略.第29页/共120页补例2:CREATE PROCEDURE testa int,b int=NULL,c int=3ASSELECT a,b,cGO第3
15、0页/共120页CREATE PROCEDURE testCREATE PROCEDURE testa int,a int,b int=NULL,b int=NULL,c int=3c int=3ASASSELECT a,b,cSELECT a,b,cGOGOEXEC testEXEC test GOGOEXEC test 1EXEC test 1 GOGOEXEC test 1,defaultEXEC test 1,default GOGOEXEC test 1,default,5EXEC test 1,default,5 GOGOEXEC test 1,2,5EXEC test 1,2,5
16、 GOGOEXEC testEXEC test /*/*错误错误,第一个参数不可省略第一个参数不可省略*/GOGOEXEC test 1EXEC test 1 /*OK,/*OK,第第2 2、3 3参数用默认值参数用默认值*/GOGOEXEC test 1,defaultEXEC test 1,default /*OK,/*OK,可用可用defaultdefault表示使用表示使用默认值默认值*/GOGOEXEC test 1,default,5EXEC test 1,default,5 /*OK*/*OK*/GOGOEXEC test 1,2,5EXEC test 1,2,5 /*OK*/*
17、OK*/GOGO第31页/共120页也可以使用在存储过程中声明的参数名称，以“name=value”格式来指明传入参数的对应位置。例：EXEC test c=5,b=DEFAULT,a=1/*不用按顺序传了*/判断正误：判断正误：EXEC test 1,c=2 EXEC test 1,c=2 EXEC test c=2,1EXEC test c=2,1GOGOEXEC test c=5EXEC test c=5EXEC test 1,c=2 /*OK!1EXEC test 1,c=2 /*OK!1传入传入a,a,而而bb使用默认值使用默认值*/GOGOEXEC test c=2,1 /*EXE
18、C test c=2,1 /*错误错误!因为使用过因为使用过name=valuename=value后就必须后就必须 直使用此方式来传参数直使用此方式来传参数*/GOGOEXEC test c=5 /*EXEC test c=5 /*错误错误!因为因为aa参数不可省略参数不可省略*/CREATE PROCEDURE testCREATE PROCEDURE testa int,a int,b int=NULL,b int=NULL,c int=3c int=3ASASSELECT a,b,cSELECT a,b,cGOGO第32页/共120页8.2.2 管理存储过程1查看存储过程 存储过程被创
19、建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用企业管理器或系统存储过程来查看用户创建的存储过程。第33页/共120页1查看存储过程 在企业管理器中查看用户创建的存储过程的方法如下:1）在企业管理器中，打开指定的服务器和数据库项，并单击存储过程文件夹，此时在右边的窗格中就会显示出数据库中的所有存储过程。第34页/共120页（2）右击要查看的存储过程，从弹出的快捷菜单中选择“属性”命令，会弹出“存储过程属性”对话框。第35页/共120页(3)右击存储过程“存储过程_读者借阅信息”,从弹出的快捷菜单中依次选择”所有任务”-”显示相关性”
20、命令,会弹出相关对话框,显示与选择的存储过程有依赖关系的其他数据库对象的名称,如图所示.第36页/共120页使用系统存储过程来查看用户创建的存储过程可供使用的系统存储过程及其语法形式如下：（1）sp_help：用于显示存储过程的参数及其数据类型 sp_help objname=name参数name为要查看的存储过程的名称。第37页/共120页例8-2 使用系统存储过程查看“存储过程_读者借阅信息”的参数及其数据类型。程序清单如下：-查看存储过程的参数及其数据类型USE BOOKSGOsp_help 存储过程_读者借阅信息GO第38页/共120页（2）sp_helptext：用于显示存储过程的源
21、代码sp_helptext objname=name参数name为要查看的存储过程的名称。第39页/共120页例:使用系统存储过程查看“存储过程_读者借阅信息”的源代码。程序清单如下：-查看存储过程的参数及其数据类型USE BOOKSGOsp_helptext 存储过程_读者借阅信息GO第40页/共120页（3）sp_depends：用于显示和存储过程相关的数据库对象sp_depends objname=object 参数object为要查看依赖关系的存储过程的名称。第41页/共120页例8-3 使用系统存储过程查看“存储过程_读者借阅信息”的相关的数据库对象。程序清单如下：-查看相关的数据库
22、对象USE BOOKSGOsp_depends 存储过程_读者借阅信息GO第42页/共120页（4）sp_stored_procedures：用于返回当前数据库中的存储过程列表sp_stored_proceduressp_name=namesp_stored_proceduressp_name=name,sp_owner=owner,sp_owner=owner,sp_qualifier=qualifier,sp_qualifier=qualifier 用于指定返回用于指定返回目录信息的过程目录信息的过程名名。用于指定过程所用于指定过程所有者的名称；有者的名称；用于指定过程限用于指定过程限定符
23、的名称。定符的名称。（表示数据库名称表示数据库名称)第43页/共120页USE BOOKSExec sp_stored_proceduresGO 第44页/共120页USE BOOKSExec sp_stored_procedures 存储过程_读者借阅信息,dbo,booksGO 第45页/共120页USE BOOKSexec sp_stored_procedures%借%GO第46页/共120页2修改存储过程定义(1)在企业管理器中，单击目录树中的存储过程图标，在窗口的右侧右击要修改的存储过程，从弹出的快捷菜单中选择“属性”命令，则会出现存储过程属性对话框。第47页/共120页(2)T-S
24、QL语句的修改存储过程:其语法形式如下：ALTER PROCEDURE procedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONASsql_statement .n 第48页/共120页简要格式:ALTER PROCEDURE 参数名 AS|第49页/共120页练习：创建名为select_students的存储过程，默认情况下，该过程可查询所有学生信息；再重新改为能检索计算机专业的学生信息的存储过程。USE
25、 STUDENTIF EXISTS(SELECT name FROM sysobjects WHERE name=select_students AND type=P)DROP PROCEDURE select_students GOCREATE PROCEDURE select_studentsASSelect*From student order by 学号GO建建立立过过程程第50页/共120页ALTER PROCEDURE select_studentsASSelect*From student where 院系名称=计算机系 order by 学号GO修修改改过过程程第51页/共12
26、0页3重命名存储过程(1)在企业管理器中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”命令，或者双击存储过程名称，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称了。第52页/共120页 (2)也可以使用系统存储过程sp_rename修改存储过程的名称，其语法形式如下：sp_rename sp_rename 原存储过程名称，新存储过程名称原存储过程名称，新存储过程名称第53页/共120页8.2.3 执行存储过程在SQL Server 2000中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：EXECUTE 存储过程名参数 第54页/共120页程序清单如下
27、：USE BOOKSEXEC 存储过程_读者信息或直接写存储过程的名称：USE BOOKSGO存储过程_读者信息例8-4：执行前面创建的无参存储过程“存储过程_读者信息”。第55页/共120页存储过程_读者借阅信息创建一个带有参数的存储过程“存储过程_读者借阅信息”，该存储过程根据传入的读者编号，返回此人的基本信息及此人借阅的图书信息。第56页/共120页条件（再运行一下）USE BOOKSIF EXISTS(SELECT name FROM sysobjects WHERE name=存储过程_读者借阅信息 AND type=P)DROP PROCEDURE 存储过程_读者借阅信息GOCRE
28、ATE PROCEDURE 存储过程_读者借阅信息 读者编号 varchar(12)ASSelect 读者基本信息表.读者编号,姓名,性别,所在部门,图书借阅信息表.图书编码,书名,借阅日期From 图书基本信息表,图书借阅信息表,读者基本信息表WHERE 读者基本信息表.读者编号=图书借阅信息表.读者编号 and 图书基本信息表.图书编码=图书借阅信息表.图书编码And 读者基本信息表.读者编号=读者编号GO第57页/共120页例8-5 执行有参存储过程“存储过程_读者借阅信息”，该存储过程有一个输入参数“读者编号”。程序清单如下：USE BOOKSGOEXECUTE 存储过程_读者借阅信息
29、 JSJ03或：USE BOOKSGOEXECUTE 存储过程_读者借阅信息 读者编号=JSJ03第58页/共120页8.2.4 删除存储过程1使用企业管理器删除存储过程 在企业管理器中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”命令，会弹出“除去对象”对话框。在该对话框中，单击“全部除去”按钮，即可完成删除操作。第59页/共120页2使用Transact-SQL语句删除存储过程删除存储过程也可以使用Transact-SQL语言中的DROP命令;DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：drop procedure procedure,n
30、指定过程名称。指定过程名称。可删除多个过程可删除多个过程。第60页/共120页程序清单如下：USE BOOKSGODrop procedure 存储过程_读者借阅信息GO例8-6 使用DROP命令删除存储过程“存储过程_读者借阅信息”。第61页/共120页8.1.2 触发器概述触发器是一种特殊类型的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。当数据表有当数据表有INSERTINSERT、UPDATEUPDATE、DELETEDELETE事件发生时，事件发生时，所设置的触发器即会自动被执所设置的触发器即会自动被执行，以进行维护数据完整性，行，以进行
31、维护数据完整性，或其他数据处理工作。或其他数据处理工作。就是当表内容被更改时，会自动执行的存储过程。第62页/共120页数据的完整性（）实实体体完完整整性性。（）域域完完整整性性。（）参参照照完完整整性性。1 1、实体完整性要求数据实体完整性要求数据表中的每一行在表中是表中的每一行在表中是惟一的实体。惟一的实体。2 2、采用三种方式采用三种方式主键约束主键约束：IDENTITYIDENTITY属性属性：1 1、用来保证在数据表中用来保证在数据表中输入有效的数据值输入有效的数据值。方方法法定义字段的数据类型定义字段的数据类型、设置字段非空设置字段非空检查约束检查约束默认约束默认约束默认规则默认规
32、则默认值默认值。对输入的数据值进行检查，满足条件可以对输入的数据值进行检查，满足条件可以保存在数据表中，保存在数据表中，反之反之则拒绝接受。则拒绝接受。惟一性约束惟一性约束：规则是对存储在数据表中的数据值的规定和限制。规则是对存储在数据表中的数据值的规定和限制。设置参照完整性是为了保证数据表之间设置参照完整性是为了保证数据表之间的数据保持一致，的数据保持一致，通过在两个数据表之通过在两个数据表之间的主键和外键之间或惟一键和外键之间的主键和外键之间或惟一键和外键之间建立外键约束来实现。间建立外键约束来实现。约束特点约束特点:直接设置于表内直接设置于表内,不需编不需编程程;只能进行比较简单的操只能
33、进行比较简单的操作作;第63页/共120页触发器作用触发器可以用于SQL Server 2000约束、默认值和规则的完整性检查；和CHECK约束相比较，触发器是特殊的存储过程，由于是编写的程序,可以强制实现更加复杂的数据完整性，而且可以引用其他表中的字段。注：简单的工作应尽量用约束来完成，这样容易设置及维护，执行效率也比较好。只有当它无法满足需要时考虑使用触发器。第64页/共120页补：触发器的“特异功能”检查所作的更改是否允许（更多样更复杂的检查：同时检查多个数据表、用IFELSE等来作更弹性的检查）；自定义错误信息（不是原来的固定信息，而是返回自定义的错误信息）；进行其它相关数据的更改动作
34、第65页/共120页触发器分类 触发器分为两种，即AFTER触发器和INSTEAD OF触发器。AFTER触发器：这种类型的触发器将在数据变动（INSERT、UPDATE和DELETE操作）完成以后才被触发。可以对变动的数据进行检查，如果发现错误，将拒绝接受或回滚变动的数据。AFTER触发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。第66页/共120页INSTEAD OF触发器：这种类型的触发器将在数据变动以前被触发，并取代变动数据的操作（INSERT、UPDATE和DELETE操作），而去执行触发器定义的操作。在表或视图上，每个INSERT、UPDATE和DELETE语句
35、最多只可以定义一个INSTEAD OF触发器。第67页/共120页8.3 触发器8.3.1 创建触发器只能在当前数据库中创建触发器，创建触发器时可以引用其他数据库中的对象。只有表的所有者有权创建触发器，且不能将该权限转给其他用户。创建方法：使用企业管理器或者Transact-SQL语句来创建触发器。第68页/共120页1使用企业管理器创建触发器步骤如下：在企业管理器中展开BOOKS数据库，右击“图书基本信息表”，从弹出的快捷菜单中依次选择“所有任务”“管理触发器”命令。出现“触发器属性”对话框，如图所示。默认是新建一个触发器默认是新建一个触发器填上内容填上内容进行语法检查。进行语法检查。第69
36、页/共120页可在此查看或修改可在此查看或修改已建好的触发器的已建好的触发器的内容。内容。按此按钮可删除当前按此按钮可删除当前触发器。触发器。第70页/共120页（2）在“触发器属性”对话框中，在“名称”文本框中选择“新建”，然后在“文本”框中输入创建触发器的文本。当创建一个触发器时必须指定以下几项内容：1）触发器的名称；2）在其上定义触发器的表；3）触发器将何时激发；4）执行触发操作的编程语句。这里在“文本”框中输入以一文本：（功能：创建一个INSERT触发器）CREATE TRIGGER 触发器_图书信息表_插入 ON 图书基本信息表 FOR INSERTASDECLARE 提示 char
37、(30)SET 提示=你插入了一条新记录!PRINT 提示当在“图书基本信息表”中插入一条新记录时，触发该触发器，并给出“你插入了一条新记录！”的提示信息。第71页/共120页USE BOOKSGOInsert into 图书基本信息表(图书编码)Values(100009)GO例8-7 创建了“触发器_图书信息表_插入”后，查看向图书基本信息表中插入数据时，此触发器所完成的功能。第72页/共120页2使用Transact-SQL语句创建触发器创建触发器的语法：CREATE TRIGGER trigger_name ONtable|view WITH ENCRYPTION FOR|AFTER|
38、INSTEAD OF DELETE,INSERT ,UPDATE WITH APPEND NOT FOR REPLICATION第73页/共120页AS IF UPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.n sql_statement.n 第74页/共120页CREATE TRIGGER trigger_name ONtable|view WITH ENCRYPTION FOR|AFTER|IN
39、STEAD OF DELETE,INSERT ,UPDATE ASsql_statement.n 用于指定用于指定触发器的触发器的名称。名称。在所属数据在所属数据库中必须是唯一的。库中必须是唯一的。用于指定在其上用于指定在其上执行触发器的表或执行触发器的表或视图视图。（只有（只有INSTEAD OFINSTEAD OF触发器触发器才能设置于视图上。才能设置于视图上。用于加密用于加密syscommentssyscomments表中包含表中包含CREATE TRIGGERCREATE TRIGGER语句文本的语句文本的条目。条目。FOR/AFTERFOR/AFTER类触发器：类触发器：只有在数据只
40、有在数据表的操作都已正确完成后才会激活触表的操作都已正确完成后才会激活触发器；发器；INSTEAD INSTEAD OF类触发器：用触用触发器替代发器替代原来要执行的数据操作。原来要执行的数据操作。用于指定在表或视图上执行哪些数据修改语句时将激活用于指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。触发器的关键字。必须至少指定一个选项。如果指定的必须至少指定一个选项。如果指定的选项多于一个，需用逗号分隔这些选项。选项多于一个，需用逗号分隔这些选项。对于对于INSTEAD OFINSTEAD OF类限制三项最多只能有一个。而类限制三项最多只能有一个。而AFTERAFTER类无限制。类无
41、限制。用来定义触发器的内容。用来定义触发器的内容。第75页/共120页在创建触发器时，可以使用两个特殊的临时表：inserted表和deleted表，这两个表都存在于内存中。通过二表可知哪些数据已被新建、修改或删除。触发器可检查deleted表、inserted表及被修改的表。两个特殊的表：第76页/共120页在inserted表中存储着被INSERT和UPDATE语句影响的新的数据行。在执行INSERT或UPDATE语句时，新的数据行被添加到基本表中，同时这些数据行的备份 被复制到inserted临时表中。第77页/共120页在deleted表中存储着被DELETE和UPDATE语句影响的旧
42、数据行。在执行DELETE或UPDATE语句时，指定的数据行从基本表中删除，然 后 被 转 移 到 了d e l e t e d表 中。在 基 本 表 和d e l e t e d表 中一般不会存在相同的数据行。第78页/共120页一个UPDATE操作实际上是由一个DELETE操作和一个INSERT操作组成的。在执行UPDATE操作时，旧的数据行从基本表中转移到deleted表中，然后将新的数据行同时插入基本表和inserted表中。第79页/共120页总结InsertedInserted数据表数据表数据表数据表DeletedDeleted数据表数据表数据表数据表在插入时在插入时在插入时在插入
43、时存放要插入的记录存放要插入的记录存放要插入的记录存放要插入的记录在修改时在修改时在修改时在修改时存放要更新的记录存放要更新的记录存放要更新的记录存放要更新的记录存放更新前的旧记录存放更新前的旧记录存放更新前的旧记录存放更新前的旧记录在删除时在删除时在删除时在删除时存放被删除的旧记录存放被删除的旧记录存放被删除的旧记录存放被删除的旧记录若要检索若要检索DeletedDeleted和和InsertedInserted表中的所有记录：可使用表中的所有记录：可使用如下的语句：如下的语句：SELECT*FORM DeletedSELECT*FORM DeletedSELECT*FORM Inserte
44、dSELECT*FORM Inserted第80页/共120页补例：对于BOOKS数据库，如果在图书基本信息表中添加或更改数据，则向用户显示一条信息。第81页/共120页USE BOOKSGO-如果已经存在“显示信息_触发器”，则先删除此触发器IF EXISTS(SELECT name FROM sysobjects WHERE name=显示信息_触发器AND type=TR)DROP TRIGGER 显示信息_触发器GO-创建“显示信息_触发器”CREATE TRIGGER 显示信息_触发器ON 图书基本信息表FOR INSERT,UPDATEASPRINT 尊敬的用户：您添加或修改了一条
45、数据GO运行：运行：INSERT INTO INSERT INTO 图书基本信息表图书基本信息表(图书编码图书编码,书名书名,作者作者,定价定价)VALUES(100068,SQL SERVER VALUES(100068,SQL SERVER 数据库基础数据库基础,王鹏王鹏,28),28)第82页/共120页补例：创建一个AFTER触发器，要求实现以下功能：在图书基本信息表上创建一个插入类型的触发器“触发器_定价检查1”，当在定价字段中插入数据后，触发该触发器，检查定价的数据值是否大于0。第83页/共120页USE BOOKSGO-如果已经存在“触发器_定价检查1”，则先删除此触发器IF E
46、XISTS(SELECT name FROM sysobjects WHERE name=触发器_定价检查1 AND type=TR)DROP TRIGGER 触发器_定价检查1GO-创建“触发器_定价检查1”CREATE TRIGGER 触发器_定价检查1ON 图书基本信息表FOR INSERTASIF(SELECT 定价 FROM inserted)=0 BEGIN PRINT 输入的定价数据应该大于0!ROLLBACKENDGO数据回滚。在数据回滚。在sqlsql语句执行失败时，语句执行失败时，使用了此语句，使用了此语句，可以将数据回滚可以将数据回滚到到sqlsql语句执行前语句执行前的
47、状态。的状态。第84页/共120页例8-8 创建一个AFTER触发器，要求实现以下功能：在图书基本信息表上创建一个插入、更新类型的触发器“触发器_定价检查2”，当在定价字段中插入或修改数据后，触发该触发器，检查定价的数据值是否大于0。第85页/共120页USE BOOKSGO-如果已经存在“触发器_定价检查2”，则先删除此触发器IF EXISTS(SELECT name FROM sysobjects WHERE name=触发器_定价检查2 AND type=TR)DROP TRIGGER 触发器_定价检查2GO-创建“触发器_定价检查2”CREATE TRIGGER 触发器_定价检查2ON
48、 图书基本信息表FOR INSERT,UPDATEASIF UPDATE(定价)BEGIN PRINT AFTER触发器开始执行 DECLARE 定价 real SELECT 定价=(SELECT 定价 FROM inserted)IF 定价0 BEGIN update 图书借阅信息表 set 读者编号=读者编号_修改后 where 读者编号=读者编号_修改前 print 已经将图书借阅信息表中的读者编号为：+rtrim(读者编号_修改前)+的所有记录 的读者编号修改为：+rtrim(读者编号_修改后)+。end 第89页/共120页CREATE TRIGGER 触发器_级联修改图书借阅信息表
49、 ON 读者基本信息表FOR UPDATEASPRINT“触发器_级联修改图书借阅信息表”开始执行.DECLARE 读者编号_修改前 char(10)DECLARE 读者编号_修改后 char(10)PRINT 把读者基本信息表中修改前的记录的读者编号赋值给局部变量读者编号_修改前。SELECT 读者编号_修改前=读者编号 FROM DELETEDPRINT 把读者基本信息表中修改后的记录的读者编号赋值给局部变量读者编号_修改后。SELECT 读者编号_修改后=读者编号 FROM INSERTEDPRINT 开始查找并修改图书借阅信息表相关记录IF(SELECT COUNT(*)FROM 图书
50、借阅信息表 WHERE 读者编号=读者编号_修改前)0 BEGIN update 图书借阅信息表 set 读者编号=读者编号_修改后 where 读者编号=读者编号_修改前 print 已经将图书借阅信息表中的读者编号为：+rtrim(读者编号_修改前)+的所有记录 的读者编号修改为：+rtrim(读者编号_修改后)+。end 第90页/共120页运行：USE BOOKSGO-修改未借阅图书的诗编号update 读者基本信息表set 读者编号=JSJ05where 读者编号=JSJ04go 第91页/共120页update 读者基本信息表 set 读者编号=JSJ05 where 读者编号=J