Oracle11g触发器操作

1、第9章 触发器操作 主编：刘志成 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 本章学习导航本章学习导航 本章学习导航本章学习导航 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 本章学习要点本章学习要点 （1）触发器的）触发器的基础知识基础知识。 （2）:OLD和和:NEW变量。变量。 （3）SQL Developer创建、修改、查看、编译创建、修改、查看、编译 和删除触发器。和删除触发器。 （4）PL/SQL创建、修改、查看、编译和删除创建、修改、查看、编译和删除 触发器。触发器。 （5）触发器的）触发器的功能及使用功能及使用。 （6）

2、eBuy电子商城中的电子商城中的典型触发器典型触发器 建议课时：建议课时：8课时课时 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 9.1 触发器概述 触发器简介触发器简介 触发器是一种特殊的存储过程，它与数据表紧密联系，用于保护表中的触发器是一种特殊的存储过程，它与数据表紧密联系，用于保护表中的 数据，当一个定义了特定类型触发器的基表执行插入、修改或删除表中数据，当一个定义了特定类型触发器的基表执行插入、修改或删除表中 数据的操作时，将自动触发触发器中定义的操作，以实现数据的一致性数据的操作时，将自动触发触发器中定义的操作，以实现数据的一致性 和完整性。和完整性。

3、 触发器拥有比数据库本身标准的功能更精细和更复杂的数据控制能力。触发器拥有比数据库本身标准的功能更精细和更复杂的数据控制能力。 触发器具有以下的作用：触发器具有以下的作用： （1）在）在安全性安全性方面，触发器可以基于数据库的值使用户具有操作数据方面，触发器可以基于数据库的值使用户具有操作数据 库的某种权利。库的某种权利。 （2）在）在审计审计方面，触发器可以跟踪用户对数据库的操作。方面，触发器可以跟踪用户对数据库的操作。 （3）实现）实现复杂的数据完整性规则复杂的数据完整性规则。 （4）实现复杂的非标准的数据库相关完整性规则。触发器可以对数据）实现复杂的非标准的数据库相关完整性规则。触发器可

4、以对数据 库中相关的表进行库中相关的表进行连环更新连环更新。 （5）同步实时同步实时地复制表中的数据。地复制表中的数据。 （6）自动计算数据值自动计算数据值，如果数据的值达到了一定的要求，则进行特定，如果数据的值达到了一定的要求，则进行特定 的处理。例如，如果商品的数量低于的处理。例如，如果商品的数量低于5，则立即给管理人员发送库存报，则立即给管理人员发送库存报 警信息。警信息。 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 9.1 触发器概述 触发器类型触发器类型 1触发器的功能触发器的功能 （1）DML触发器触发器 当对表进行当对表进行DML操作时触发，可以在操

5、作时触发，可以在DML操作前或操作后触发。操作前或操作后触发。 （2）替代触发器）替代触发器 是是Oracle用来替换所使用的实际语句而执行的触发器。用来替换所使用的实际语句而执行的触发器。 （3）系统触发器）系统触发器 在在Oracle数据库系统的事件（数据库系统的事件（Oracle系统的启动与关闭等）中进行触发。系统的启动与关闭等）中进行触发。 （4）用户事件触发器）用户事件触发器 指与数据库定义语句或用户的登录指与数据库定义语句或用户的登录/注销等事件相关的触发器。注销等事件相关的触发器。 2触发事件触发事件 按触发事件的不同，触发器可以分为插入型（按触发事件的不同，触发器可以分为插入型

6、（INSERT）、更新型）、更新型 （UPDATE）和删除型（）和删除型（DELETE）触发器。）触发器。 对于插入型触发器，当触发器所在的表发生插入操作时，触发器将自动对于插入型触发器，当触发器所在的表发生插入操作时，触发器将自动 触发执行。对于更新型触发器，当触发器所在的表发生更新操作时，触触发执行。对于更新型触发器，当触发器所在的表发生更新操作时，触 发器将自动触发执行。同样，对于删除型触发器，当触发器所在的表发发器将自动触发执行。同样，对于删除型触发器，当触发器所在的表发 生删除操作时，触发器也将自动触发执行。生删除操作时，触发器也将自动触发执行。 OracleOracle数据库管理与

7、应用实例教程数据库管理与应用实例教程 9.1 触发器概述 触发器类型触发器类型 3触发时间触发时间 根据指定的事件和触发器执行的先后次序，触发器可以分为根据指定的事件和触发器执行的先后次序，触发器可以分为BEFORE型型 和和AFTER型触发器。型触发器。 如果在指定的事件（如果在指定的事件（INSERT、UPDATE或者或者DELETE）之前执行触发）之前执行触发 器，这类触发器称为器，这类触发器称为BEFORE触发器；若在指定的事件之后执行触发器，触发器；若在指定的事件之后执行触发器， 则称这类触发器为则称这类触发器为AFTER触发器。触发器。 4触发级别触发级别 根据触发级别的不同，触发

8、器可以分为根据触发级别的不同，触发器可以分为行触发器行触发器和和语句触发器语句触发器。 对于行触发器，受触发事件影响的每一行都将引发触发器的执行；而对对于行触发器，受触发事件影响的每一行都将引发触发器的执行；而对 于语句触发器，触发事件只触发一次，即使有若干行受触发事件的影响，于语句触发器，触发事件只触发一次，即使有若干行受触发事件的影响， 也只执行一次触发操作。也只执行一次触发操作。 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 9.1 触发器概述 :OLD:OLD变量和变量和:NEW:NEW变量变量 在在Oracle系统中，每个触发器被系统中，每个触发器被DML

9、操作触发时，会产生两个特殊的变操作触发时，会产生两个特殊的变 量量:OLD和和:NEW，分别代表某数据记录行在修改前和修改后的值。这两，分别代表某数据记录行在修改前和修改后的值。这两 个变量都是系统变量，由个变量都是系统变量，由Oracle系统管理，存储在内存中，不允许用户系统管理，存储在内存中，不允许用户 直接对其进行修改。直接对其进行修改。:OLD和和:NEW变量的结构总是与执行变量的结构总是与执行DML操作的表操作的表 的结构相同的结构相同。当触发器工作完成以后，这两个变量也随之消失。这两个。当触发器工作完成以后，这两个变量也随之消失。这两个 变量的值是变量的值是只读只读的，即用户不能向

10、这两个变量写入内容，但可以引用变的，即用户不能向这两个变量写入内容，但可以引用变 量中的数据。量中的数据。 :OLD变量变量用于存储用于存储DELETE和和UPDATE操作操作所影响的行的副本。当执行所影响的行的副本。当执行 DELETE或或UPDATE操作时，行从触发表中被删除，并传输到操作时，行从触发表中被删除，并传输到:OLD变量变量 中。中。 :NEW变量用于存储变量用于存储INSERT和和UPDATE操作所影响的行的副本。当执行操作所影响的行的副本。当执行 INSERT或或UPDATE操作时，新行被同时添加到操作时，新行被同时添加到:NEW变量和触发表变量和触发表 中，中，:NEW变

11、量中的行即为触发表中新行的副本。变量中的行即为触发表中新行的副本。 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 9.1 触发器概述 :OLD:OLD变量和变量和:NEW:NEW变量变量 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 课堂案例1使用SQL Developer管理触发器 学习使用SQL Developer创建触发器的方法和基本步骤，掌握触发器的 执行时机 。 案例学习目标案例学习目标 SQL Developer创建触发器、验证触发器的作用 。 案例知识要点案例知识要点 OracleOracle数据库管理与应用实例教程数据库管

12、理与应用实例教程 课堂案例1使用SQL Developer管理触发器 案例完成步骤案例完成步骤- -创建触发器创建触发器 1创建触发器创建触发器 （1）在SQL Developer中右击Triggers选项，从快捷菜单 中选择“New Trigger”项，将开始创建触发器。 （2）在打开的“Create Trigger”对话框中，输入 TR_ADDUSER作为新建触发器的名称；选择触发器类型 （Trigger Type）为TABLE，并选择触发表为客户表 USERS。然后选择触发器类别信息为After、语句级别 （Statement Level）和插入型（Insert）；其余采用默认 值 。

13、教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 课堂案例1使用SQL Developer管理触发器 案例完成步骤案例完成步骤- -创建触发器创建触发器 （3）触发器设置完成后，单击）触发器设置完成后，单击“确定确定”按钮，在按钮，在 PL/SQL编辑窗口中补充创建触发器的编辑窗口中补充创建触发器的PL/SQL代码代码 。 （4）触发器编译成功后，在）触发器编译成功后，在PL/SQL编辑窗口中输入以编辑窗口中输入以 下下PL/SQL代码：代码： INSERT INTO Users VALUES(00,trigger,普通普通, tri

14、gger ); （5）按）按F9键执行该键执行该PL/SQL语句，激发触发器的执行语句，激发触发器的执行 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 课堂案例1使用SQL Developer管理触发器 案例完成步骤案例完成步骤- -查看触发器查看触发器 在在SQL Developer的左边的树型结构中，展开的左边的树型结构中，展开Triggers节节 点后选择需要查看的触发器点后选择需要查看的触发器tr_AddUser，将在右边栏内，将在右边栏内 显示显示tr_AddUser触发器的信息，如触发器的定义语句和触发器的信息，如触发

15、器的定义语句和 详细描述等详细描述等 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 课堂案例1使用SQL Developer管理触发器 案例完成步骤案例完成步骤- -修改触发器修改触发器 （1）在SQL Developer中，右键单击Triggers选项中的触 发器tr_AddUser，从快捷菜单中选择“Edit”项 。 （2）在打开的触发器编辑窗口中，修改触发器的定义 PL/SQL语句 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 课堂案例1使用SQL Develo

16、per管理触发器 案例完成步骤案例完成步骤- -删除触发器删除触发器 （1）在）在SQL Developer中，右击中，右击Triggers选项中的待删选项中的待删 除触发器除触发器tr_AddUser，从快捷菜单中选择，从快捷菜单中选择“Drop Trigger”项。项。 （2）在打开的）在打开的“Drop Trigger”对话框中，在对话框中，在Prompts （提示）选项卡内显示了待删除触发器的所有者和名称，（提示）选项卡内显示了待删除触发器的所有者和名称， 并提示用户是否需要删除该触发器。并提示用户是否需要删除该触发器。 （3）单击）单击“应用应用”按钮，将打开按钮，将打开Confir

17、mation对话框，对话框， 以提示用户该触发器已经被删除。以提示用户该触发器已经被删除。 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 课堂案例2使用PL/SQL管理触发器 学习使用PL/SQL语句中的CREATE TRIGGER语句的用法 。 案例学习目标案例学习目标 CREATE TRIGGER创建触发器、验证触发器的作用 。 案例知识要点案例知识要点 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 课堂案例2使用PL/SQL管理触发器 案例完成步骤案例完成步骤- -创建触发器创建触发器 1创建触发

18、器创建触发器 【例2-1】为用户方案SCOTT的客户表CUSTOMERS创建 插入型触发器，当添加新客户信息时，显示“欢迎新会员注 册！”。 INSERT INTO SCOTT.Customers VALUES(C2002,trigger,触发器,女,to_date(1999-04- 14,yyyy-mm-dd)湖南长沙市 ,4100010731- 8888888,123456,6666,你出生在哪里, 湖南长沙,普通); 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教

19、程 课堂案例2使用PL/SQL管理触发器 案例完成步骤案例完成步骤- -创建触发器创建触发器 【例2-2】为用户方案SCOTT的客户表CUSTOMERS创建 删除型触发器，当删除会员信息时，显示“用户已被 删除！”的信息。 CREATE OR REPLACE TRIGGER tr_DelCUST BEFORE DELETE ON SCOTT.CUSTOMERS FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(待删除的记录为:); DBMS_OUTPUT.PUT_LINE(:OLD.c_NAME|用户 已被删除!); END tr_DelCUST; DELETE

20、CUSTOMERS WHERE c_D = C2002; 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 课堂案例2使用PL/SQL管理触发器 案例完成步骤案例完成步骤- -创建触发器创建触发器 【例2-3】实现eBuy电子商城系统的日志操作功能（语句级 触发器）。 （1）创建语句级触发器的测试表）创建语句级触发器的测试表(该语句应该在创建触发器 的语句之前运行) CREATE TABLE SYSLOG( WHO VARCHAR2(30), OPER_DATE DATE); 该表用来保存对eBuy系统进行操作的日志。 （2）创建语句

21、级触发器）创建语句级触发器 CREATE OR REPLACE TRIGGER tr_DeleteUser AFTER DELETE ON Users BEGIN INSERT INTO SYSLOG(WHO,OPER_DATE) VALUES(USER,SYSDATE); END; 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 课堂案例2使用PL/SQL管理触发器 案例完成步骤案例完成步骤- -创建触发器创建触发器 该触发器用来实现在该触发器用来实现在Users表中删除一条记录时，在表中删除一条记录时，在 SYSLOG表中添加一

22、条包含当前操作用户（表中添加一条包含当前操作用户（USER表示当前表示当前 用户）和当前操作日期（用户）和当前操作日期（SYSDATE表示当前日期）的记录。表示当前日期）的记录。 （3）测试触发器）测试触发器tr_DeleteUser 通过删除通过删除Users表中的一条记录（用户名称为表中的一条记录（用户名称为“存储过程存储过程”） 来测试触发器的功能。来测试触发器的功能。 DELETE FROM Users WHERE u_Name=存储过程存储过程; SELECT * FROM Syslog; 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理

23、与应用实例教程 课堂案例2使用PL/SQL管理触发器 案例完成步骤案例完成步骤- -创建触发器创建触发器 【例例2-4】通过临时表将通过临时表将Users表中删除的记录进行临时保存（行级触发器）。表中删除的记录进行临时保存（行级触发器）。 （1）创建行级触发器的测试表）创建行级触发器的测试表 CREATE TABLE userdel AS SELECT * FROM Users WHERE u_Name=amy; （2）创建行级触发器）创建行级触发器 CREATE OR REPLACE TRIGGER tr_DeleteUser2 AFTER DELETE ON Users FOR EACH

24、ROW BEGIN INSERT INTO userdel ALUES(:OLD.u_ID,:OLD.u_Name,:OLD.u_Type,:OLD.u_Password); END; （3）测试触发器）测试触发器tr_DeleteUser和和tr_DeleteUser2 DELETE FROM Users WHERE u_Name=amy; SELECT * FROM userdel; SELECT * FROM syslog; 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 课堂案例2使用PL/SQL管理触发器 案例完成步骤案例

25、完成步骤- -查看触发器查看触发器 【例例2-5】查看用户方案查看用户方案SCOTT中所有触发器的名称、触发中所有触发器的名称、触发 类型、触发事件、所有者和触发表等信息。类型、触发事件、所有者和触发表等信息。 SELECTTRIGGER_NAME 触发器名触发器名, TRIGGER_TYPE 触发类型触发类型, TRIGGERING_EVENT 触发事件触发事件, TABLE_NAME 触发表触发表 FROMUSER_TRIGGERS; 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 课堂案例2使用PL/SQL管理触发器 案例完成

26、步骤案例完成步骤- -修改和删除触发器修改和删除触发器 3修改触发器修改触发器 修改触发器和修改视图类似，虽然修改触发器和修改视图类似，虽然Oracle也提供也提供ALTER TRIGGER命令，但它只用于重新编译或者验证现有触发器。命令，但它只用于重新编译或者验证现有触发器。 如果需要修改触发器的定义，则仍然使用如果需要修改触发器的定义，则仍然使用CREATE OR REPLACE TRIGGER命令。命令。 4删除触发器删除触发器 Oracle的的PL/SQL语句提供了语句提供了DROP TRIGGER命令来删除命令来删除 触发器，其基本语法格式为：触发器，其基本语法格式为： DROP T

27、RIGGER 用户方案用户方案.触发器名触发器名; 【例例2-6】删除用户方案删除用户方案SCOTT的触发器的触发器tr_ADDCUST。 实现该删除任务的实现该删除任务的DROP TRIGGER命令如下：命令如下： DROP TRIGGER SCOTT. tr_ADDCUST; 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 9.3 其他类型触发器 替代触发器替代触发器 INSTEAD OF触发器主要用来对另一个表或者视图进行DML 操作。 【例3-1】已经创建了查询商品信息的视图 VW_GOODSINFO，视图结构包含商品编号、

28、商品名称、 商品类别和详细描述。对于视图VWGOODSINFO，考虑使 用更新语句，以期更新视图，达到更新基表中数据的目的。 （1）定义视图）定义视图 CREATE OR REPLACE VIEW SCOTT.VW_GOODSINFO AS SELECT g_ID, g_NAME, t_NAME,g_PRICE FROM SCOTT.GOODS G JOIN SCOTT.TYPES T ON G.t_ID = T.t_ID; 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 9.3 其他类型触发器 替代触发器替代触发器 （2）更新视图

29、数据）更新视图数据 对于视图对于视图VWGOODSINFO，考虑使用更新语句，以期更新，考虑使用更新语句，以期更新 视图，达到更新基表中数据的目的。相应的视图，达到更新基表中数据的目的。相应的PL/SQL语句如语句如 下所示：下所示： UPDATE SCOTT.VW_GOODSINFO SET g_NAME = 三星三星SGH- C888,t_NAME = 电脑产品电脑产品 WHERE g_ID = 010006; 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 9.3 其他类型触发器 替代触发器替代触发器 （3）创建）创建INST

30、EAD OF触发器触发器 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 9.3 其他类型触发器 系统事件触发器系统事件触发器 系统事件触发器是指由数据库系统事件触发的数据库触发器。系统事件触发器是指由数据库系统事件触发的数据库触发器。 数据库系统事件通常包括以下几种：数据库系统事件通常包括以下几种： l 数据库的启动（数据库的启动（STARTUP）；）； l 数据库的关闭（数据库的关闭（SHUTDOWN）；）； l 数据库服务器出错（数据库服务器出错（SERVERERROR）。）。 【例例3-2】创建数据库启动后记录启动时间的系统

31、事件触发器。创建数据库启动后记录启动时间的系统事件触发器。 （1）创建系统事件触发器测试表）创建系统事件触发器测试表 CREATE TABLE dblog(op_date timestamp); （2）创建系统事件触发器）创建系统事件触发器 CREATE OR REPLACE TRIGGER tr_StartDB AFTER STARTUP ON DATABASE BEGIN INSERT INTO DBLOG VALUES(SYSDATE); END; 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 9.3 其他类型触发器 用户事

32、件触发器用户事件触发器 用户事件触发器指与数据库定义语句或用户的登录用户事件触发器指与数据库定义语句或用户的登录/注销等事注销等事 件相关的触发器。这些事件包括以下语句，并且可以规定触发件相关的触发器。这些事件包括以下语句，并且可以规定触发 时间时间BEFORE或或AFTER。 【例3-3】创建保存系统操作日志的用户事件触发器。 create or replace TRIGGER SCOTT.TR_DDL AFTER LOGON ON SCHEMA BEGIN INSERT INTO SCOTT.SYSLOG VALUES(USER,SYSDATE); END; 教教 师师 演演 示示 讲讲 解解 OracleOracle数据库管理与应用实例教程数据库管理与应用实例教程 9.4 eBuy中的典型触发器 插入型触发器插入型触发器 【例4-1】在用户方案SCOTT的商品表GOODS中插入数据记录时需要进行如下检查：当试图 往GOODS表中插入在商品类别表TYPES中并不存在的商品类别编号t_ID时，返回一条