oracleDML触发器.ppt

ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 1 主讲人：毛应爽 第10章 触发器 第10章 触发器 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 2 第第1010章章 触发器触发器 10.1 触发器概述 10.2 DML触发器 10.3 替代触发器 10.4 系统触发器 10.5 用户事件触发器 10.6 管理触发器 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 3 1 1、概念、概念 触发器是在事件发生时触发器是在事件发生时隐式地自动隐式地自动运行的运行的PL/SQLPL/SQL程程 序块，序块，不能接收参数不能接收参数，不能被调用不能被调用。 2 2、触发器的构成、触发器的构成 触发器名称触发器名称 触发器的触发事件触发器的触发事件 触发器限制条件触发器限制条件 触发器主体触发器主体 10.1 10.1 触发器概述触发器概述 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 4 3 3、创建触发器的语法格式、创建触发器的语法格式 CREATE OR REPLACE TRIGGERCREATE OR REPLACE TRIGGER trigger_nametrigger_name -触发器的触发事件触发器的触发事件 BEFOREBEFORE | | AFTERAFTER | | INSTEAD OFINSTEAD OF triggering_eventtriggering_event WHENWHEN trigger_condition trigger_condition -限制条件限制条件 FOR EACH ROWFOR EACH ROW -行级触发行级触发 trigger_body; trigger_body; -语句体语句体 10.1 10.1 触发器概述触发器概述 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 5 说明：说明： trigger_nametrigger_name是触发器的名称，是触发器的名称， triggering_eventtriggering_event说明了激发触发器的事件（也可说明了激发触发器的事件（也可 能包括特殊的表或视图），能包括特殊的表或视图）， trigger_bodytrigger_body是触发器的代码。是触发器的代码。 注意：注意：如果在如果在WHENWHEN子句中指定子句中指定trigger_conditiontrigger_condition的的 话话话话，则则则则首先首先对该对该对该对该 条件求条件求值值值值。触触发发发发器主体只有在器主体只有在该该该该条条 件件为为为为真真值时值时值时值时 才运行。才运行。 10.1 10.1 触发器概述触发器概述 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 6 第第1010章章 触发器触发器 10.1 触发器概述 10.2 DML触发器 10.3 替代触发器 10.4 系统事件触发器 10.5 用户事件触发器 10.6 管理触发器 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 7 10.2 DML10.2 DML触发器触发器 DMLDML触发器是针对某个表进行触发器是针对某个表进行DMLDML操作时触发的。操作时触发的。 语法格式：语法格式： CREATE OR REPLACE TRIGGERCREATE OR REPLACE TRIGGER trigger_name trigger_name BEFOREBEFORE | | AFTERAFTER | | INSERTINSERT| |DELETEDELETE| |UPDATEUPDATE OFOF column,columncolumn,column ONON table_name|view_name table_name|view_name REFERENCINGREFERENCING OLDOLD ASold_name| ASold_name|NEWNEW AS AS new_namenew_name FOR EACH ROWFOR EACH ROW WHENWHEN trigger_condition trigger_condition trigger_body;trigger_body; ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 8 在编写触发器源代码之前，必须先确定其触发时间、 触发事件及触发器的类型。 DML 触发器类型类型 行级触发器 语句级触发器 DML 触发器 触发事件事件 表更新表更新 表插入表插入 表删除表删除 DMLDML触发器触发器 触发时间（时机）触发时间（时机） BEFOREBEFOREAFTERAFTER 10.2 DML10.2 DML触发器触发器 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 9 触发事件 （如INSERT、 UPDATE、 DELETE等） 触发器 脚本 触发时机 BEFORE （事件） 001001GREENGREENMEDMED 98.1 98.1 002002REDREDMEDMED12411241 003003REDREDSMALLSMALL100.1100.1 004004BLUEBLUELARGELARGE 54.9 54.9 005005REDREDMEDMED124.1124.1 partnocolorsizeweight 触发对象表触发对象表 BEFOREBEFORE触发器触发器 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 10 触发器事件（ 如INSERT、 UPDATE、 DELETE等） 触发器 脚本 触发时机 AFTER （事件） 001GREENMED 98.1 002REDMED1241 003REDSMALL100.1 004BLUELARGE 54.9 005REDMED124.1 partnocolorsizeweight 触发对象表触发对象表 AFTERAFTER触发器触发器 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 11 语句级触发器与行级触发器的区别： 触发器脚本（ 1次） 语句级 触发器 脚本 行级 行级 行级 触发器 脚本 触发器脚本 （n次） 10.2 DML10.2 DML触发器触发器 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 12 10.2 DML10.2 DML触发器触发器 1、语句级触发器 语句级触发器在每个数据修改语句执行后只调用一次，而 不管这一操作将影响到多少行。 例1：创建一个orderdetails_tablelog表并建立一个 AFTER触发器，用于记录是哪些用户删除了orderdetails表 中的数据及删除的时间。 CREATE TABLE orderdetails_tablelog (who varchar2(40), oper_date date, operate varchar2(100); ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 13 10.2 DML10.2 DML触发器触发器 CREATE OR REPLACE TRIGGER dele_orderdetails AFTER delete ON orderdetails BEGIN INSERT INTO orderdetails_tablelog VALUES(user,sysdate,delete); END; 注：在该触发器被触发后，尽管一次删除多条记录 ，但触发器只执行一次插入操作。 查看触发器的定义文本使用查看触发器的定义文本使用 数据字典数据字典user_sourceuser_source ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 14 10.2 DML10.2 DML触发器触发器 2 2、行级触发器、行级触发器 行级触发器是按触发语句行级触发器是按触发语句所处理的行激发的所处理的行激发的, ,可以可以 引用受到影响的行值。创建触发器时采用关键字引用受到影响的行值。创建触发器时采用关键字 FOR EACH ROWFOR EACH ROW 这种访问是通过两个相关的标识符实现的这种访问是通过两个相关的标识符实现的 :old:old ：用于存放：用于存放未进行修改前未进行修改前的数据。的数据。 :new:new ：用于存放进行：用于存放进行修改后的数据。修改后的数据。 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 15 10.2 DML10.2 DML触发器触发器 例2:创建一个orderdetails_Rowlog表并建立一个AFTER 触发器，用于记录是哪些用户删除了在什么时间删除了 orderdetails表中的哪些行数据。 CREATE TABLE orderdetails_rowLog1 (who varchar2(40), oper_date timestamp, operate varchar2(100), orderid number, productid number); ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 16 10.2 DML10.2 DML触发器触发器 CREATE OR REPLACE TRIGGER deleRow_orderdetails AFTER delete ON orderdetails FOR EACH ROW BEGIN INSERT INTO orderdetails_rowLog VALUES(user,sysdate,delete,:old.orderid, :ductid); END; ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 17 10.2 DML10.2 DML触发器触发器 例3:创建一个BEFORE触发器,使得在向ORDERS表中 插入记录之前对ShippedDate字段进行检测,要求其值不 允许为周六或周日,发货时间应在8-18点之间。否则将提 示错误发货时间应为工作时间。 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 18 10.2 DML10.2 DML触发器触发器 CREATE OR REPLACE TRIGGER secure_shippeddate BEFORE INSERT ON orders For each row BEGINBEGIN IF (TO_CHAR(:new.shippeddate,DY) in (SAT,SUN)OR (to_number(TO_CHAR(:new.shippeddate,HH24) NOT BETWEEN 8 AND 18) THEN RAISE_APPLICATION_ERROR(-20500,发货时间应为工作时间 ); END IF; END; ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 19 使用使用“ “:old”:old”和和“ “:new”:new”应注意：应注意： 在BEFORE类型行级触发器和AFTER类型行级触发器中 使用这些标识符。 在语句级触发器中不要使用这些标识符。 在PL/SQL语句或SQL语句中，这些标识符前加上冒号（: ）来引用它们。 在行级触发器的WHEN条件中使用该标识符时，前面不要 加冒号（:）. 在BEFORE触发器中不能修改“:old”，在AFTER触发器 中不能修改“:new” 10.2 DML10.2 DML触发器触发器 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 20 触发时机BEFORE后， :new的值还没有被插入到表里， 因此:new可以修 改，而此时， :old值是表中没有被修改的值，因此不能被修改 触发器 脚本 触发时机 BEFORE （事件） 001GREENMED 98.1 002REDMED1241 003REDSMALL100.1 004BLUELARGE 54.9 005REDMED124.1 partnocolorsizeweight 触发对象表 :new :old ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 21 触发器事件（ 如INSERT、 UPDATE、 DELETE等） 触发器 脚本 :new :old 触发时机AFTER后， :new的值已经被插入到表里， 因此:new不能修改 001GREENMED 98.1 002REDMED1241 003REDSMALL100.1 004BLUELARGE 54.9 005REDMED124.1 partnocolorsizeweight 触发时机 AFTER （事件） ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 22 10.2 DML10.2 DML触发器触发器 例句例句4 4：用触发器实现在用户添加数据时：用触发器实现在用户添加数据时, ,使用序列使用序列 生成的数字生成的数字, ,为表的主键提供自动编号的键值。为表的主键提供自动编号的键值。 -创建表创建表 CREATE TABLECREATE TABLE temp_table temp_table (temp_id number,name varchar2(30);(temp_id number,name varchar2(30); -创建序列创建序列 CREATE SEQUENCECREATE SEQUENCE incrID incrID START WITH 10START WITH 10 INCREMENT BY 5 INCREMENT BY 5 NOMAXVALUENOMAXVALUE CACHE 10;CACHE 10; ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 23 10.2 DML10.2 DML触发器触发器 -创建触发器以提供自动编号的键值 CREATE OR REPLACE TRIGGER incr_trigger BEFORE INSERT ON temp_table FOR EACH ROW DECLARE next_no number; BEGIN SELECT incrID.nextval INTO next_no FROM DUAL; :new.temp_id:= next_no; END; ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 24 当一个触发器中的触发事 件中既有删除、更新又有 插入时，如何判断触发器 是因哪个事件而动作？ 在IF语句中使用触发器谓词（INSERTING、UPDATING、 DELETING） 10.2 DML10.2 DML触发器触发器 3、多条件触发形式: CREATE OR REPLACE TRIGGERCREATE OR REPLACE TRIGGER BEFORE insert OR update OR delete ON BEGIN IF INSERTING THEN END IF; IF DELETING THEN END IF; IF UPDATING THEN END IF; End; ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 25 例句5：用触发器增强参照完整性约束。当orders表的 orderid发生变化时，orderdetails表的相关行也跟着进行适 当的修改。 CREATE OR REPLACE TRIGGER cascade_updateorders AFTER update OR delete ON orders FOR EACH ROW BEGIN IF updating THEN UPDATE orderdetails SET orderdetails .orderid=:new.orderid WHERE orderdetails .orderid=:old.orderid; END IF; IF deleting THEN DELETE FROM orderdetails WHERE orderdetails .orderid=:old.orderid; END IF; END; 10.2 DML10.2 DML触发器触发器 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 26 10.2 DML10.2 DML触发器触发器 4 4、级联触发器、级联触发器 把把一个数据库触发器的动作一个数据库触发器的动作与与另一个触发器联系起来，另一个触发器联系起来，使使 之之触发另一个触发器触发另一个触发器。 例例5:5:创建创建3 3个表个表A A、B B、C C，在表，在表A A上设置一个上设置一个INSERTINSERT触发触发 器，用于向表器，用于向表B B添加一条记录，在表添加一条记录，在表B B 上设置一个上设置一个INSERTINSERT触触 发器，用于向表发器，用于向表C C添加一条记录，添加一条记录， ，在表，在表C C 上设置一个上设置一个 INSERTINSERT触发器，用于对表中的所有记录进行更新触发器，用于对表中的所有记录进行更新(+10(+10） 1)1) 创建创建A A、B B、C C三张表三张表 CREATE TABLE A(AID number);CREATE TABLE A(AID number); CREATE TABLE B(BID number); CREATE TABLE B(BID number); CREATE TABLE C(CID number); CREATE TABLE C(CID number); ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 27 10.2 DML10.2 DML触发器触发器 -在表A上创建INSERT触发器 CREATE OR REPLACE TRIGGER insert_a AFTER insert ON A BEGIN INSERT INTO b VALUES(1); END; -在表B上创建INSERT触发器 CREATE OR REPLACE TRIGGER insert_b AFTER insert ON B BEGIN INSERT INTO c VALUES(2); END; ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 28 10.2 DML10.2 DML触发器触发器 -在表C上创建INSERT触发器 CREATE OR REPLACE TRIGGER insert_a AFTER insert ON C BEGIN UPDATE a SET aid=aid+10; End; -测试，向A表插入数据5 INSERT INTO A VALUES(5); ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 29 5 5、限制触发器的执行、限制触发器的执行WHENWHEN子句来保证，当某些子句来保证，当某些 行满足一定条件时，在该行上行级触发器才被触发。行满足一定条件时，在该行上行级触发器才被触发。 它它只适用于行级触发器只适用于行级触发器 在在WHENWHEN的条件中，的条件中，oldold和和newnew或在或在REFERENCINGREFERENCING 子句中为子句中为oldold和和newnew起的起的别名，都不加冒号（：）别名，都不加冒号（：）。 10.2 DML10.2 DML触发器触发器 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 30 例例6:6:在在productsproducts表上创建一个触发器表上创建一个触发器, ,用于在更新产品用于在更新产品 单价时，如果是类别为的产品则打九五折，其他的产单价时，如果是类别为的产品则打九五折，其他的产 品价格按需要进行更新。品价格按需要进行更新。 CREATE OR REPLACE TRIGGERCREATE OR REPLACE TRIGGER update_prodprice update_prodprice BEFORE UPDATE OFBEFORE UPDATE OF unitpriceunitprice ON ON products products FOR EACH ROWFOR EACH ROW WHEN (new.categoryid=2)WHEN (new.categoryid=2) BEGINBEGIN :new.unitprice:=:old.unitprice*0.95;:new.unitprice:=:old.unitprice*0.95; ENDEND; ; 10.2 DML10.2 DML触发器触发器 ORACLE 10g ORACLE 10g 数据库数据库 管理、应用与开发管理、应用与开发 31 测试语句: UPDATE products SET unitprice=unitprice+10 WHERE productid=3; UPDATE products SET unitprice=unitprice+10 WHERE productid=1; 10.2 DML10.2 DML触发器触发器 ORACLE 10g ORAC