第9章触发器操作 主编 刘志成 本章学习导航 本章学习导航 本章学习要点 1 触发器的基础知识 2 OLD和 NEW变量 3 SQLDeveloper创建 修改 查看 编译和删除触发器 4 PL SQL创建 修改 查看 编译和删除触发器 5 触发器的功能及使用 6 eBuy电子商城中的典型触发器建议课时 8课时 9 1触发器概述 触发器简介 触发器是一种特殊的存储过程 它与数据表紧密联系 用于保护表中的数据 当一个定义了特定类型触发器的基表执行插入 修改或删除表中数据的操作时 将自动触发触发器中定义的操作 以实现数据的一致性和完整性 触发器拥有比数据库本身标准的功能更精细和更复杂的数据控制能力 触发器具有以下的作用 1 在安全性方面 触发器可以基于数据库的值使用户具有操作数据库的某种权利 2 在审计方面 触发器可以跟踪用户对数据库的操作 3 实现复杂的数据完整性规则 4 实现复杂的非标准的数据库相关完整性规则 触发器可以对数据库中相关的表进行连环更新 5 同步实时地复制表中的数据 6 自动计算数据值 如果数据的值达到了一定的要求 则进行特定的处理 例如 如果商品的数量低于5 则立即给管理人员发送库存报警信息 9 1触发器概述 触发器类型 1 触发器的功能 1 DML触发器当对表进行DML操作时触发 可以在DML操作前或操作后触发 2 替代触发器是Oracle用来替换所使用的实际语句而执行的触发器 3 系统触发器在Oracle数据库系统的事件 Oracle系统的启动与关闭等 中进行触发 4 用户事件触发器指与数据库定义语句或用户的登录 注销等事件相关的触发器 2 触发事件按触发事件的不同 触发器可以分为插入型 INSERT 更新型 UPDATE 和删除型 DELETE 触发器 对于插入型触发器 当触发器所在的表发生插入操作时 触发器将自动触发执行 对于更新型触发器 当触发器所在的表发生更新操作时 触发器将自动触发执行 同样 对于删除型触发器 当触发器所在的表发生删除操作时 触发器也将自动触发执行 9 1触发器概述 触发器类型 3 触发时间根据指定的事件和触发器执行的先后次序 触发器可以分为BEFORE型和AFTER型触发器 如果在指定的事件 INSERT UPDATE或者DELETE 之前执行触发器 这类触发器称为BEFORE触发器 若在指定的事件之后执行触发器 则称这类触发器为AFTER触发器 4 触发级别根据触发级别的不同 触发器可以分为行触发器和语句触发器 对于行触发器 受触发事件影响的每一行都将引发触发器的执行 而对于语句触发器 触发事件只触发一次 即使有若干行受触发事件的影响 也只执行一次触发操作 9 1触发器概述 OLD变量和 NEW变量 在Oracle系统中 每个触发器被DML操作触发时 会产生两个特殊的变量 OLD和 NEW 分别代表某数据记录行在修改前和修改后的值 这两个变量都是系统变量 由Oracle系统管理 存储在内存中 不允许用户直接对其进行修改 OLD和 NEW变量的结构总是与执行DML操作的表的结构相同 当触发器工作完成以后 这两个变量也随之消失 这两个变量的值是只读的 即用户不能向这两个变量写入内容 但可以引用变量中的数据 OLD变量用于存储DELETE和UPDATE操作所影响的行的副本 当执行DELETE或UPDATE操作时 行从触发表中被删除 并传输到 OLD变量中 NEW变量用于存储INSERT和UPDATE操作所影响的行的副本 当执行INSERT或UPDATE操作时 新行被同时添加到 NEW变量和触发表中 NEW变量中的行即为触发表中新行的副本 9 1触发器概述 OLD变量和 NEW变量 课堂案例1 使用SQLDeveloper管理触发器 学习使用SQLDeveloper创建触发器的方法和基本步骤 掌握触发器的执行时机 案例学习目标 SQLDeveloper创建触发器 验证触发器的作用 案例知识要点 课堂案例1 使用SQLDeveloper管理触发器 案例完成步骤 创建触发器 添加标题文字 1 创建触发器 1 在SQLDeveloper中右击Triggers选项 从快捷菜单中选择 NewTrigger 项 将开始创建触发器 2 在打开的 CreateTrigger 对话框中 输入TR ADDUSER作为新建触发器的名称 选择触发器类型 TriggerType 为TABLE 并选择触发表为客户表USERS 然后选择触发器类别信息为After 语句级别 StatementLevel 和插入型 Insert 其余采用默认值 教师演示讲解 课堂案例1 使用SQLDeveloper管理触发器 案例完成步骤 创建触发器 添加标题文字 3 触发器设置完成后 单击 确定 按钮 在PL SQL编辑窗口中补充创建触发器的PL SQL代码 4 触发器编译成功后 在PL SQL编辑窗口中输入以下PL SQL代码 INSERTINTOUsersVALUES 00 trigger 普通 trigger 5 按F9键执行该PL SQL语句 激发触发器的执行 教师演示讲解 课堂案例1 使用SQLDeveloper管理触发器 案例完成步骤 查看触发器 添加标题文字 在SQLDeveloper的左边的树型结构中 展开Triggers节点后选择需要查看的触发器tr AddUser 将在右边栏内显示tr AddUser触发器的信息 如触发器的定义语句和详细描述等 教师演示讲解 课堂案例1 使用SQLDeveloper管理触发器 案例完成步骤 修改触发器 添加标题文字 1 在SQLDeveloper中 右键单击Triggers选项中的触发器tr AddUser 从快捷菜单中选择 Edit 项 2 在打开的触发器编辑窗口中 修改触发器的定义PL SQL语句 教师演示讲解 课堂案例1 使用SQLDeveloper管理触发器 案例完成步骤 删除触发器 添加标题文字 1 在SQLDeveloper中 右击Triggers选项中的待删除触发器tr AddUser 从快捷菜单中选择 DropTrigger 项 2 在打开的 DropTrigger 对话框中 在Prompts 提示 选项卡内显示了待删除触发器的所有者和名称 并提示用户是否需要删除该触发器 3 单击 应用 按钮 将打开Confirmation对话框 以提示用户该触发器已经被删除 教师演示讲解 课堂案例2 使用PL SQL管理触发器 学习使用PL SQL语句中的CREATETRIGGER语句的用法 案例学习目标 CREATETRIGGER创建触发器 验证触发器的作用 案例知识要点 课堂案例2 使用PL SQL管理触发器 案例完成步骤 创建触发器 添加标题文字 1 创建触发器 例2 1 为用户方案SCOTT的客户表CUSTOMERS创建插入型触发器 当添加新客户信息时 显示 欢迎新会员注册 INSERTINTOSCOTT CustomersVALUES C2002 trigger 触发器 女 to date 1999 04 14 yyyy mm dd 430202198604141006 湖南长沙市 410001 13313313333 0731 8888888 amy 123456 6666 你出生在哪里 湖南长沙 普通 教师演示讲解 课堂案例2 使用PL SQL管理触发器 案例完成步骤 创建触发器 添加标题文字 例2 2 为用户方案SCOTT的客户表CUSTOMERS创建删除型触发器 当删除会员信息时 显示 用户已被删除 的信息 CREATEORREPLACETRIGGERtr DelCUSTBEFOREDELETEONSCOTT CUSTOMERSFOREACHROWBEGINDBMS OUTPUT PUT LINE 待删除的记录为 DBMS OUTPUT PUT LINE OLD c NAME 用户已被删除 ENDtr DelCUST DELETECUSTOMERSWHEREc D C2002 教师演示讲解 课堂案例2 使用PL SQL管理触发器 案例完成步骤 创建触发器 添加标题文字 例2 3 实现eBuy电子商城系统的日志操作功能 语句级触发器 1 创建语句级触发器的测试表 该语句应该在创建触发器的语句之前运行 CREATETABLESYSLOG WHOVARCHAR2 30 OPER DATEDATE 该表用来保存对eBuy系统进行操作的日志 2 创建语句级触发器CREATEORREPLACETRIGGERtr DeleteUserAFTERDELETEONUsersBEGININSERTINTOSYSLOG WHO OPER DATE VALUES USER SYSDATE END 教师演示讲解 课堂案例2 使用PL SQL管理触发器 案例完成步骤 创建触发器 添加标题文字 该触发器用来实现在Users表中删除一条记录时 在SYSLOG表中添加一条包含当前操作用户 USER表示当前用户 和当前操作日期 SYSDATE表示当前日期 的记录 3 测试触发器tr DeleteUser通过删除Users表中的一条记录 用户名称为 存储过程 来测试触发器的功能 DELETEFROMUsersWHEREu Name 存储过程 SELECT FROMSyslog 教师演示讲解 课堂案例2 使用PL SQL管理触发器 案例完成步骤 创建触发器 添加标题文字 例2 4 通过临时表将Users表中删除的记录进行临时保存 行级触发器 1 创建行级触发器的测试表CREATETABLEuserdelASSELECT FROMUsersWHEREu Name amy 2 创建行级触发器CREATEORREPLACETRIGGERtr DeleteUser2AFTERDELETEONUsersFOREACHROWBEGININSERTINTOuserdelALUES OLD u ID OLD u Name OLD u Type OLD u Password END 3 测试触发器tr DeleteUser和tr DeleteUser2DELETEFROMUsersWHEREu Name amy SELECT FROMuserdel SELECT FROMsyslog 教师演示讲解 课堂案例2 使用PL SQL管理触发器 案例完成步骤 查看触发器 添加标题文字 例2 5 查看用户方案SCOTT中所有触发器的名称 触发类型 触发事件 所有者和触发表等信息 SELECTTRIGGER NAME触发器名 TRIGGER TYPE触发类型 TRIGGERING EVENT触发事件 TABLE NAME触发表FROMUSER TRIGGERS 教师演示讲解 课堂案例2 使用PL SQL管理触发器 案例完成步骤 修改和删除触发器 添加标题文字 3 修改触发器修改触发器和修改视图类似 虽然Oracle也提供ALTERTRIGGER命令 但它只用于重新编译或者验证现有触发器 如果需要修改触发器的定义 则仍然使用CREATEORREPLACETRIGGER命令 4 删除触发器Oracle的PL SQL语句提供了DROPTRIGGER命令来删除触发器 其基本语法格式为 DROPTRIGGER 用户方案 触发器名 例2 6 删除用户方案SCOTT的触发器tr ADDCUST 实现该删除任务的DROPTRIGGER命令如下 DROPTRIGGERSCOTT tr ADDCUST 教师演示讲解 9 3其他类型触发器 替代触发器 添加标题文字 INSTEADOF触发器主要用来对另一个表或者视图进行DML操作 例3 1 已经创建了查询商品信息的视图VW GOODSINFO 视图结构包含商品编号 商品名称 商品类别和详细描述 对于视图VWGOODSINFO 考虑使用更新语句 以期更新视图 达到更新基表中数据的目的 1 定义视图CREATEORREPLACEVIEWSCOTT VW GOODSINFOASSELECT