SQL Server 数据库技术第8章 存储过程和触发器的创建与维护..ppt课件

第8章存储过程和触发器的创建与维护 本章学习目标 了解存储过程的概念 掌握创建 执行存储过程的方法 掌握查看和修改存储过程的方法 掌握删除存储过程的方法 了解触发器和一般存储过程的主要区别 掌握创建和查看触发器的方法 掌握修改和删除触发器的方法 8 1概述8 1 1存储过程概述 存储过程 就是将常用的或很复杂的工作 预先以SQL程序写好 然后指定一个程序名称保存起来 以后只要用EXCUTE指令来执行这个程序 即可完成该项工作 8 1 1存储过程概述 应该提倡多使用存储过程 原因有以下几点 1 实现了模块化编程 2 调用一次以后 相关信息就保存在内存中 下次调用时可以直接执行 3 存储过程可以接受输入参数并可以返回输出值 4 存储过程具有对数据库立即访问的功能 5 使用存储过程可以加快程序的运行速度 6 使用存储过程可以减少网络流量 7 使用存储过程可以提高数据库的安全性 8 1 1存储过程概述 存储过程分为两类 系统存储过程和用户自定义的存储过程 系统存储过程是由系统自动创建的 主要存储在master数据库中 一般以sp 为前缀 系统存储过程完成的功能主要是从系统表中获取信息 进行系统的各项设置 相关管理工作 用户自定义存储过程由用户创建并能完成某一特定功能的存储过程 会被加入所属数据库的存储过程项目中 并以对象的形式保存 8 2存储过程8 2 1创建存储过程 创建存储过程的三种方法 使用创建存储过程向导创建存储过程 使用企业管理器创建存储过程 使用Transact SQL语句中的CREATEPROCEDURE命令创建存储过程 1 使用创建存储过程向导创建存储过程 1 在企业管理器中 要创建存储过程的数据库 选择 工具 菜单中的 向导 菜单项 单击向导中 数据库 选项左边的加号 选中 创建存储过程向导 选项 2 单击 确定 按钮 出现 欢迎使用创建存储过程 对话框 如图所示 3 选择数据库对话框 选择存放存储过程的数据库 4 选择存储过程 对话框 选择存储过程中要针对哪些表做哪些操作 5 确认存储过程信息对话框 向导会针对每一个数据表的每一个操作 都产生单独的存储过程来处理 按此按钮可编辑选定项目的设置 6 编辑存储过程属性 对话框 更改存储过程的名称 按此按钮可直接修改SQL程序代码 executeinsert 读者基本信息表 1 67 女 fggg 7 9 2 使用SQLServer2000企业管理器创建存储过程 步骤如下 1 在企业管理器中 选择指定的服务器和数据库 右击要创建存储过程的数据库 在弹出的快捷菜单中依次选择 新建 存储过程 命令 2 在文本框中可以输入创建存储过程的T SQL语句 3 输入完毕可单击 检查语法 按钮 4 最后单击 确定 保存 默认的样板 CREATEPROCEDURE存储过程 读者信息ASSELECT读者编号 姓名 性别 所在部门from读者基本信息表 3 使用Transact SQL语句创建存储过程 可以使用Transact SQL语句中的CREATEPROCEDURE命令创建存储过程 只能在当前数据库中创建存储过程 3 使用Transact SQL语句创建存储过程 语法形式如下 CREATEPROC EDURE procedure name number parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASsql statement n procedure name 用于指定所要创建存储过程的名称 各参数的含义 parameter 过程中的参数 在CREATEPROCEDURE语句中可以声明一个或多个参数 执行时应提供相应的实在参数 data type 用于指定参数的数据类型 default 用于指定参数的默认值 常量 各参数的含义 parameterdata type AS 用于指定该存储过程要执行的操作 sql statement 是存储过程中包含的任意数目和类型的Transact SQL语句 各参数的含义 CREATEPROCEDURE 参数名 AS 例 从STUDENT数据库的三个表中查询 返回学生学号 姓名 课程名 成绩 学分 USESTUDENTIFEXISTS SELECTnameFROMsysobjectsWHEREname student info ANDtype P DROPPROCEDUREstudent infoGOCREATEPROCEDUREstudent infoASSelecta 学号 姓名 课程名 成绩 学分Fromstudenta scb coursecWHEREa 学号 b 学号Andb 课号 c 课号GO EXECUTEstudent info 简单的存储过程 练习 从STUDENT数据库的三个表中查询某人指定课程的成绩和学分 该存储过程接受与传递参数精确匹配的值 USESTUDENTIFEXISTS SELECTnameFROMsysobjectsWHEREname student info1 ANDtype P DROPPROCEDUREstudent info1GOCREATEPROCEDUREstudent info1 namechar 8 cnamechar 16 ASSelecta 学号 姓名 课程名 成绩 学分Fromstudenta scb coursecWHEREa 学号 b 学号Andb 课号 c 课号anda 姓名 nameandc 课程名 cnameGO 带参数的存储过程 EXECUTEstudent info1 王丽 计算机基础 EXECUTEstudent info1 name 王丽 cname 计算机基础 练习 从STUDENT数据库的三个表中返回指定学生的学号 姓名 所选课程名及该课的成绩 该存储过程在参数中可使用模式匹配 如果没提供参数 则使用预设的默认值 USESTUDENTIFEXISTS SELECTnameFROMsysobjectsWHEREname student info3 ANDtype P DROPPROCEDUREstudent info3GOCREATEPROCEDUREstudent info3 namevarchar 30 刘 ASSelecta 学号 姓名 课程名 成绩Fromstudenta scb coursecWHEREa 学号 b 学号Andb 课号 c 课号and姓名like nameGO 带参数的存储过程 EXECUTEstudent info3 参数使用默认值 EXECUTEstudent info3 王 传递给 name的实参为 王 EXECUTEstudent info3 王张 方括号 指定范围 a f 或集合 abcdef 中的任何单个字符 创建一个带有参数的存储过程 存储过程 读者借阅信息 该存储过程根据传入的读者编号 返回此人的基本信息及此人借阅的图书信息 例8 1 USEBOOKSIFEXISTS SELECTnameFROMsysobjectsWHEREname 存储过程 读者借阅信息 ANDtype P DROPPROCEDURE存储过程 读者借阅信息GOCREATEPROCEDURE存储过程 读者借阅信息 读者编号varchar 12 ASSelect读者基本信息表 读者编号 姓名 性别 所在部门 图书借阅信息表 图书编码 书名 借阅日期From图书基本信息表 图书借阅信息表 读者基本信息表WHERE读者基本信息表 读者编号 图书借阅信息表 读者编号and图书基本信息表 图书编码 图书借阅信息表 图书编码And读者基本信息表 读者编号 读者编号GO exec存储过程 读者借阅信息 JSJ03 如果student表中男生入学成绩的平均值高于女生入学成绩的平均值则显示 男生的入学成绩平均值高于女生的入学成绩平均值 否则显示 男生的入学成绩平均值低于女生的入学成绩平均值 补例1 建立判断男学生与女学生的平均入学成绩高低的存储过程 USEstudentGOCREATEPROCEDURE比较男女生入学成绩ASIf selectavg 入学成绩 FromstudentWhere性别 男 selectavg 入学成绩 FromstudentWhere性别 女 Print 男生的入学成绩平均值高于女生的入学成绩平均值 ElsePrint 男生的入学成绩平均值低于女生的入学成绩平均值 GO 参数传递技巧 执行存储过程时 若未指明参数名称 则必须依照存储过程所需的参数依次传过去 而且除非该参数指定有默认值 否则不可省略 补例2 CREATEPROCEDUREtest aint bint NULL cint 3ASSELECT a b cGO CREATEPROCEDUREtest aint bint NULL cint 3ASSELECT a b cGO EXECtestGOEXECtest1GOEXECtest1 defaultGOEXECtest1 default 5GOEXECtest1 2 5GO EXECtest 错误 第一个参数不可省略 GOEXECtest1 OK 第2 3参数用默认值 GOEXECtest1 default OK 可用default表示使用默认值 GOEXECtest1 default 5 OK GOEXECtest1 2 5 OK GO 也可以使用在存储过程中声明的参数名称 以 name value 格式来指明传入参数的对应位置 例 EXECtest c 5 b DEFAULT a 1 不用按顺序传了 判断正误 EXECtest1 c 2EXECtest c 2 1GOEXECtest c 5 EXECtest1 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 8 2 2管理存储过程 1 查看存储过程存储过程被创建之后 它的名字就存储在系统表sysobjects中 它的源代码存放在系统表syscomments中 可以使用企业管理器或系统存储过程来查看用户创建的存储过程 1 查看存储过程 在企业管理器中查看用户创建的存储过程的方法如下 1 在企业管理器中 打开指定的服务器和数据库项 并单击存储过程文件夹 此时在右边的窗格中就会显示出数据库中的所有存储过程 2 右击要查看的存储过程 从弹出的快捷菜单中选择 属性 命令 会弹出 存储过程属性 对话框 3 右击存储过程 存储过程 读者借阅信息 从弹出的快捷菜单中依次选择 所有任务 显示相关性 命令 会弹出相关对话框 显示与选择的存储过程有依赖关系的其他数据库对象的名称 如图所示 使用系统存储过程来查看用户创建的存储过程 可供使用的系统存储过程及其语法形式如下 1 sp help 用于显示存储过程的参数及其数据类型sp help objname name 参数name为要查看的存储过程的名称 例8 2使用系统存储过程查看 存储过程 读者借阅信息 的参数及其数据类型 程序清单如下 查看存储过程的参数及其数据类型USEBOOKSGOsp help存储过程 读者借阅信息GO 2 sp helptext 用于显示存储过程的源代码sp helptext objname name 参数name为要查看的存储过程的名称 例 使用系统存储过程查看 存储过程 读者借阅信息 的源代码 程序清单如下 查看存储过程的参数及其数据类型USEBOOKSGOsp helptext存储过程 读者借阅信息GO 3 sp depends 用于显示和存储过程相关的数据库对象sp depends objname object 参数object为要查看依赖关系的存储过程的名称 例8 3使用系统存储过程查看 存储过程 读者借阅信息 的相关的数据库对象 程序清单如下 查看相关的数据库对象USEBOOKSGOsp depends存储过程 读者借阅信息GO 4 sp stored procedures 用于返回当前数据库中的存储过程列表 sp stored procedures sp name name sp owner owner sp qualifier qualifier 用于指定返回目录信息的过程名 用于指定过程所有者的名称 用于指定过程限定符的名称 表示数据库名称 USEBOOKSExecsp stored proceduresGO USEBOOKSExecsp stored procedures存储过程 读者借阅信息 dbo booksGO USEBOOKSexecsp stored procedures 借 GO 2 修改存储过程定义 1 在企业管理器中 单击目录树中的存储过程图标 在窗口的右侧右击要修改的存储过程 从弹出的快捷菜单中选择 属性 命令 则会出现存储过程属性对话框 2 T SQL语句的修改存储过程 其语法形式如下 ALTERPROC EDURE procedure name number parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASsql statement n 简要格式 ALTERPROCEDURE 参数名 AS 练习 创建名为select students的存储过程 默认情况下 该过程可查询所有学生信息 再重新改为能检索计算机专业的学生信息的存储过程 USESTUDENTIFEXISTS SELECTnameFROMsysobjectsWHEREname select students ANDtype P DROPPROCEDUREselect studentsGOCREATEPROCEDUREselect studentsASSelect Fromstudentorderby学号GO 建立过程 ALTERPROCEDUREselect studentsASSelect Fromstudentwhere院系名称 计算机系 orderby学号GO 修改过程 3 重命名存储过程 1 在企业管理器中 右击要操作的存储过程名称 从弹出的快捷菜单中选择 重命名 命令 或者双击存储过程名称 当存储过程名称变成可输入状态时 就可以直接修改该存储过程的名称了 2 也可以使用系统存储过程sp rename修改存储过程的名称 其语法形式如下 sp rename原存储过程名称 新存储过程名称 8 2 3执行存储过程 在SQLServer2000中可以使用EXECUTE命令来直接执行存储过程 语法形式如下 EXEC UTE 存储过程名 参数 程序清单如下 USEBOOKSEXEC存储过程 读者信息或直接写存储过程的名称 USEBOOKSGO存储过程 读者信息 例8 4 执行前面创建的无参存储过程 存储过程 读者信息 存储过程 读者借阅信息 创建一个带有参数的存储过程 存储过程 读者借阅信息 该存储过程根据传入的读者编号 返回此人的基本信息及此人借阅的图书信息 条件 再运行一下 USEBOOKSIFEXISTS SELECTnameFROMsysobjectsWHEREname 存储过程 读者借阅信息 ANDtype P DROPPROCEDURE存储过程 读者借阅信息GOCREATEPROCEDURE存储过程 读者借阅信息 读者编号varchar 12 ASSelect读者基本信息表 读者编号 姓名 性别 所在部门 图书借阅信息表 图书编码 书名 借阅日期From图书基本信息表 图书借阅信息表 读者基本信息表WHERE读者基本信息表 读者编号 图书借阅信息表 读者编号and图书基本信息表 图书编码 图书借阅信息表 图书编码And读者基本信息表 读者编号 读者编号GO 例8 5执行有参存储过程 存储过程 读者借阅信息 该存储过程有一个输入参数 读者编号 程序清单如下 USEBOOKSGOEXECUTE存储过程 读者借阅信息 JSJ03 或 USEBOOKSGOEXECUTE存储过程 读者借阅信息 读者编号 JSJ03 8 2 4删除存储过程 1 使用企业管理器删除存储过程在企业管理器中 右击要删除的存储过程 从弹出的快捷菜单中选择 删除 命令 会弹出 除去对象 对话框 在该对话框中 单击 全部除去 按钮 即可完成删除操作 2 使用Transact SQL语句删除存储过程 删除存储过程也可以使用Transact SQL语言中的DROP命令 DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除 其语法形式如下 dropprocedure procedure n 指定过程名称 可删除多个过程 程序清单如下 USEBOOKSGODropprocedure存储过程 读者借阅信息GO 例8 6使用DROP命令删除存储过程 存储过程 读者借阅信息 8 1 2触发器概述 触发器是一种特殊类型的存储过程 一般的存储过程通过存储过程名称被直接调用 而触发器主要是通过事件进行触发而被执行 当数据表有INSERT UPDATE DELETE事件发生时 所设置的触发器即会自动被执行 以进行维护数据完整性 或其他数据处理工作 就是当表内容被更改时 会自动执行的存储过程 数据的完整性 实体完整性 域完整性 参照完整性 1 实体完整性要求数据表中的每一行在表中是惟一的实体 2 采用三种方式 主键约束 IDENTITY属性 1 用来保证在数据表中输入有效的数据值 方法 定义字段的数据类型 设置字段非空 检查约束 默认约束 默认规则 默认值 对输入的数据值进行检查 满足条件可以保存在数据表中 反之则拒绝接受 惟一性约束 规则是对存储在数据表中的数据值的规定和限制 设置参照完整性是为了保证数据表之间的数据保持一致 通过在两个数据表之间的主键和外键之间或惟一键和外键之间建立外键约束来实现 约束特点 直接设置于表内 不需编程 只能进行比较简单的操作 触发器作用 触发器可以用于SQLServer2000约束 默认值和规则的完整性检查 和CHECK约束相比较 触发器是特殊的存储过程 由于是编写的程序 可以强制实现更加复杂的数据完整性 而且可以引用其他表中的字段 注 简单的工作应尽量用约束来完成 这样容易设置及维护 执行效率也比较好 只有当它无法满足需要时考虑使用触发器 补 触发器的 特异功能 检查所作的更改是否允许 更多样更复杂的检查 同时检查多个数据表 用IF ELSE等来作更弹性的检查 自定义错误信息 不是原来的固定信息 而是返回自定义的错误信息 进行其它相关数据的更改动作 触发器分类 触发器分为两种 即AFTER触发器和INSTEADOF触发器 AFTER触发器 这种类型的触发器将在数据变动 INSERT UPDATE和DELETE操作 完成以后才被触发 可以对变动的数据进行检查 如果发现错误 将拒绝接受或回滚变动的数据 AFTER触发器只能在表上定义 在同一个数据表中可以创建多个AFTER触发器 INSTEADOF触发器 这种类型的触发器将在数据变动以前被触发 并取代变动数据的操作 INSERT UPDATE和DELETE操作 而去执行触发器定义的操作 在表或视图上 每个INSERT UPDATE和DELETE语句最多只可以定义一个INSTEADOF触发器 8 3触发器8 3 1创建触发器 只能在当前数据库中创建触发器 创建触发器时可以引用其他数据库中的对象 只有表的所有者有权创建触发器 且不能将该权限转给其他用户 创建方法 使用企业管理器或者Transact SQL语句来创建触发器 1 使用企业管理器创建触发器 步骤如下 在企业管理器中展开BOOKS数据库 右击 图书基本信息表 从弹出的快捷菜单中依次选择 所有任务 管理触发器 命令 出现 触发器属性 对话框 如图所示 默认是新建一个触发器 填上内容 进行语法检查 可在此查看或修改已建好的触发器的内容 按此按钮可删除当前触发器 2 在 触发器属性 对话框中 在 名称 文本框中选择 新建 然后在 文本 框中输入创建触发器的文本 当创建一个触发器时必须指定以下几项内容 1 触发器的名称 2 在其上定义触发器的表 3 触发器将何时激发 4 执行触发操作的编程语句 这里在 文本 框中输入以一文本 功能 创建一个INSERT触发器 CREATETRIGGER触发器 图书信息表 插入ON图书基本信息表FORINSERTASDECLARE 提示char 30 SET 提示 你插入了一条新记录 PRINT 提示 当在 图书基本信息表 中插入一条新记录时 触发该触发器 并给出 你插入了一条新记录 的提示信息 USEBOOKSGOInsertinto图书基本信息表 图书编码 Values 100009 GO 例8 7创建了 触发器 图书信息表 插入 后 查看向图书基本信息表中插入数据时 此触发器所完成的功能 2 使用Transact SQL语句创建触发器 创建触发器的语法 CREATETRIGGERtrigger nameON table view WITHENCRYPTION FOR AFTER INSTEADOF DELETE INSERT UPDATE WITHAPPEND NOTFORREPLICATION AS IFUPDATE column AND OR UPDATE column n IF COLUMNS UPDATED bitwise operator updated bitmask comparison operator column bitmask n sql statement n CREATETRIGGERtrigger nameON table view WITHENCRYPTION FOR AFTER INSTEADOF DELETE INSERT UPDATE ASsql statement n 用于指定触发器的名称 在所属数据库中必须是唯一的 用于指定在其上执行触发器的表或视图 只有INSTEADOF触发器才能设置于视图上 用于加密syscomments表中包含CREATETRIGGER语句文本的条目 FOR AFTER类触发器 只有在数据表的操作都已正确完成后才会激活触发器 INSTEADOF类触发器 用触发器替代原来要执行的数据操作 用于指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字 必须至少指定一个选项 如果指定的选项多于一个 需用逗号分隔这些选项 对于INSTEADOF类限制三项最多只能有一个 而AFTER类无限制 用来定义触发器的内容 在创建触发器时 可以使用两个特殊的临时表 inserted表和deleted表 这两个表都存在于内存中 通过二表可知哪些数据已被新建 修改或删除 触发器可检查deleted表 inserted表及被修改的表 两个特殊的表 在inserted表中存储着被INSERT和UPDATE语句影响的新的数据行 在执行INSERT或UPDATE语句时 新的数据行被添加到基本表中 同时这些数据行的备份被复制到inserted临时表中 在deleted表中存储着被DELETE和UPDATE语句影响的旧数据行 在执行DELETE或UPDATE语句时 指定的数据行从基本表中删除 然后被转移到了deleted表中 在基本表和deleted表中一般不会存在相同的数据行 一个UPDATE操作实际上是由一个DELETE操作和一个INSERT操作组成的 在执行UPDATE操作时 旧的数据行从基本表中转移到deleted表中 然后将新的数据行同时插入基本表和inserted表中 总结 若要检索Deleted和Inserted表中的所有记录 可使用如下的语句 SELECT FORMDeletedSELECT FORMInserted 补例 对于BOOKS数据库 如果在图书基本信息表中添加或更改数据 则向用户显示一条信息 USEBOOKSGO 如果已经存在 显示信息 触发器 则先删除此触发器IFEXISTS SELECTnameFROMsysobjectsWHEREname 显示信息 触发器 ANDtype TR DROPTRIGGER显示信息 触发器GO 创建 显示信息 触发器 CREATETRIGGER显示信息 触发器ON图书基本信息表FORINSERT UPDATEASPRINT 尊敬的用户 您添加或修改了一条数据 GO 运行 INSERTINTO图书基本信息表 图书编码 书名 作者 定价 VALUES 100068 SQLSERVER数据库基础 王鹏 28 补例 创建一个AFTER触发器 要求实现以下功能 在图书基本信息表上创建一个插入类型的触发器 触发器 定价检查1 当在定价字段中插入数据后 触发该触发器 检查定价的数据值是否大于0 USEBOOKSGO 如果已经存在 触发器 定价检查1 则先删除此触发器IFEXISTS SELECTnameFROMsysobjectsWHEREname 触发器 定价检查1 ANDtype TR DROPTRIGGER触发器 定价检查1GO 创建 触发器 定价检查1 CREATETRIGGER触发器 定价检查1ON图书基本信息表FORINSERTASIF SELECT定价FROMinserted 0BEGINPRINT 输入的定价数据应该大于0 ROLLBACKENDGO 数据回滚 在sql语句执行失败时 使用了此语句 可以将数据回滚到sql语句执行前的状态 例8 8创建一个AFTER触发器 要求实现以下功能 在图书基本信息表上创建一个插入 更新类型的触发器 触发器 定价检查2 当在定价字段中插入或修改数据后 触发该触发器 检查定价的数据值是否大于0 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值 USEBOOKSGOPRINT 在图书基本信息表中插入记录时触发器的执行结果 PRINT INSERTINTO图书基本信息表 图书编码 书名 作者 定价 VALUES 100068 SQLSERVER数据库基础 王鹏 28 INSERTINTO图书基本信息表 图书编码 书名 作者 定价 VALUES 100078 SQLSERVER学习指南 李东方 35 GOPRINT 在图书基本信息表中修改记录时触发器的执行结果 PRINT UPDATE图书基本信息表SET定价 29WHERE图书编码 100005 UPDATE图书基本信息表SET定价 65WHERE图书编码 100006 GO 例8 9创建一个AFTER触发器 要求如下 在 读者基本信息表 上创建一个修改类型的触发器 触发器 级联修改图书借阅信息表 当在 读者基本信息表 中修改某个记录的读者编号后 触发该触发器 在 图书借阅信息表 中查找此人是否借阅了图书 如果在图书借阅信息表中存在此人的借阅信息 则级联修改此人的读者编号 使之与读者基本信息表中修改后的读者编号一致 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 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 运行 USEBOOKSGO 修改未借阅图书的诗编号update读者基本信息表set读者编号 JSJ05 where读者编号 JSJ04 go update读者基本信息表set读者编号 JSJ05 where读者编号 JSJ04 DELETED INSERTED 图书借阅信息表中有无读者编号为 JSJ0 的 修改图书借阅信息表中 JSJ0 为 JSJ05 例8 9流程分析图 USEBOOKSGO 修改已借阅图书的读者编号update读者基本信息表set读者编号 JJX02 where读者编号 JJX01 go 运行 8 3 2管理触发器 1 查看触发器企业管理器方法如下 1 查看触发器定义信息 在企业管理器中 展开选定数据库 并右 击其中的某个数据表 从弹出的快捷菜单中依次选择 所有任务 管理触发器 命令 会打开触发器属性对话框 在 对象 下拉列表框中可以选择要查看的数据库对象名称 左边的页框中会显示依赖于该对象的其他对象 右边的页框中显示的是该对象依赖的其他对象 2 查看与触发器有依赖关系的其他数据库对象 右击图书基本信息表 从弹出的快捷菜单中依次选择 所有任务 显示相关性 命令 会出现相关性对话框 使用系统存储过程sp help sp helptext和sp depends可分别查看触发器的不同信息 sp help 用于查看触发器的一般信息 如触发器的名称 属性 类型和创建时间 格式 sp help 触发器名称 例 sp help触发器 定价检查2 使用系统存储过程查看触发器 sp helptext 用于查看触发器的正文信息 sp helptext 触发器名称 例 sp helptext触发器 定价检查2 sp depends 用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器 sp depends 触发器名称 sp depends 表名 例 sp depends触发器 定价检查2sp depends读者基本信息表 例8 10使用系统存储过程查看 触发器 级联修改图书借阅信息表 的一般信息 程序清单如下 USEBOOKSGOsp help 触发器 级联修改图书借阅信息表 GO 例8 11使用系统存储过程查看 触发器 级联修改图书借阅信息表 涉及的数据表的相关信息 程序清单如下 USEBOOKSGOsp depends 触发器 级联修改图书借阅信息表 GO 2 修改触发器 修改触发器包括修改触发器的正文和修改触发器的名称 展开选定数据库 右击某一个数据表 从弹出的快捷菜单中依次选择 所有任务 管理触发器 命令 出现如图所示对话框 在名称选项框中选择要修改的触发器的名称 然后在文本框中修改触发器的SQL语句 单击 检查语法 按钮 可以检查语法是否正确 2 修改触发器 使用Transact SQL语句中的altertrigger命令来修改触发器正文 altertrigger命令的语法形式如下 ALTERTRIGGERtrigger nameON table view WITHENCRYPTION FOR AFTER INSTEADOF DELETE INSERT UPDATE NOTFORREPLICATION ASsql statement n FOR AFTER INSTEADOF INSERT UPDATE NOTFORREPLICATION AS IFUPDATE column AND OR UPDATE column n IF COLUMNS UPDATED bitwise operator updated bitmask comparison operator column bitmask n sql statement n 简要格式 ALTERTRIGGERtrigger nameON table view WITHENCRYPTION FOR AFTER INSTEADOF DELETE INSERT UPDATE ASsql statement n 2 利用系统存储过程修改触发器名称 可以使用系统存储过程sp rename来修改触发器的名称 sp rename命令的语法形式如下 sp renameoldname newname 8 3 3删除触发器 只有触发器所有者才有权删除触发器 删除已创建的触发器有三种方法 直接删除触发器所在的数据表使用企业管理器删除触发器使用Transact SQL语句删除触发器 1 直接删除触发器所在的数据表 如果触发器所在的数据表已经不再使用了 可以直接删除此数据表 删除数据表时 SQLServer2000将会自动删除与该表相关的所有触发器 2 使用企业管理器删除触发器 在企业管理器中 右击要删除的触发器所在的数据表 从弹出的快捷菜单中依次选择 所有任务 管理触发器 命令 会出现触发器属性对话框 在 名称 选项框中选择要删除的触发器 然后单击 删除 按钮 即可删除该触发器 3 使用Transact SQL语句删除触发器 使用Transact SQL语句中的DROPTRIGGER命令也可以删除指定的一个或多个触发器 DROPTRIGGER trigger n USEBOOKSGODROPTRIGGER触发器 图书信息表 插