oracleDML触发器

ORACLE10g数据库管理、应用与开发1主讲人：毛应爽第10章触发器第10章触发器ORACLE10g数据库管理、应用与开发2第10章触发器10.1触发器概述10.2DML触发器10.3替代触发器10.4系统触发器10.5用户事件触发器10.6管理触发器ORACLE10g数据库管理、应用与开发31、概念触发器是在事件发生时隐式地自动运行的PL/SQL程序块，不能接收参数，不能被调用。2、触发器的构成触发器名称触发器的触发事件触发器限制条件触发器主体10.1触发器概述ORACLE10g数据库管理、应用与开发43、创建触发器的语法格式CREATE[ORREPLACE]TRIGGERtrigger_name--触发器的触发事件{BEFORE|AFTER|INSTEADOF}triggering_event[WHENtrigger_condition]--限制条件[FOREACHROW]--行级触发trigger_body;--语句体10.1触发器概述ORACLE10g数据库管理、应用与开发5说明：trigger_name是触发器的名称，triggering_event说明了激发触发器的事件（也可能包括特殊的表或视图），trigger_body是触发器的代码。注意：如果在WHEN子句中指定trigger_condition的话，则首先对该条件求值。触发器主体只有在该条件为真值时才运行。10.1触发器概述ORACLE10g数据库管理、应用与开发6第10章触发器10.1触发器概述10.2DML触发器10.3替代触发器10.4系统事件触发器10.5用户事件触发器10.6管理触发器ORACLE10g数据库管理、应用与开发710.2DML触发器DML触发器是针对某个表进行DML操作时触发的。语法格式：CREATE[ORREPLACE]TRIGGERtrigger_name{BEFORE|AFTER|}{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}ON{table_name|view_name}[REFERENCING{OLD[AS]old_name|NEW[AS]new_name}][FOREACHROW][WHENtrigger_condition]trigger_body;ORACLE10g数据库管理、应用与开发8在编写触发器源代码之前，必须先确定其触发时间、触发事件及触发器的类型。DML触发器类型行级触发器语句级触发器DML触发器触发事件表更新表插入表删除DML触发器触发时间（时机）BEFOREAFTER10.2DML触发器ORACLE10g数据库管理、应用与开发9触发事件（如INSERT、UPDATE、DELETE等）触发器脚本触发时机BEFORE（事件）001GREENMED98.1002REDMED1241003REDSMALL100.1004BLUELARGE54.9005REDMED124.1partnocolorsizeweight触发对象表BEFORE触发器ORACLE10g数据库管理、应用与开发10触发器事件（如INSERT、UPDATE、DELETE等）触发器脚本触发时机AFTER（事件）001GREENMED98.1002REDMED1241003REDSMALL100.1004BLUELARGE54.9005REDMED124.1partnocolorsizeweight触发对象表AFTER触发器ORACLE10g数据库管理、应用与开发11语句级触发器与行级触发器的区别：触发器脚本（1次）语句级触发器脚本行级行级行级触发器脚本触发器脚本（n次）10.2DML触发器ORACLE10g数据库管理、应用与开发1210.2DML触发器1、语句级触发器语句级触发器在每个数据修改语句执行后只调用一次，而不管这一操作将影响到多少行。例1：创建一个orderdetails_tablelog表并建立一个AFTER触发器，用于记录是哪些用户删除了orderdetails表中的数据及删除的时间。CREATETABLEorderdetails_tablelog(whovarchar2(40),oper_datedate,operatevarchar2(100));ORACLE10g数据库管理、应用与开发1310.2DML触发器CREATEORREPLACETRIGGERdele_orderdetailsAFTERdeleteONorderdetailsBEGININSERTINTOorderdetails_tablelogVALUES(user,sysdate,’delete’);END;注：在该触发器被触发后，尽管一次删除多条记录，但触发器只执行一次插入操作。查看触发器的定义文本使用数据字典user_sourceORACLE10g数据库管理、应用与开发1410.2DML触发器2、行级触发器行级触发器是按触发语句所处理的行激发的,可以引用受到影响的行值。创建触发器时采用关键字FOREACHROW这种访问是通过两个相关的标识符实现的:old：用于存放未进行修改前的数据。:new：用于存放进行修改后的数据。ORACLE10g数据库管理、应用与开发1510.2DML触发器例2:创建一个orderdetails_Rowlog表并建立一个AFTER触发器，用于记录是哪些用户删除了在什么时间删除了orderdetails表中的哪些行数据。CREATETABLEorderdetails_rowLog1(whovarchar2(40),oper_datetimestamp,operatevarchar2(100),orderidnumber,productidnumber);ORACLE10g数据库管理、应用与开发1610.2DML触发器CREATEORREPLACETRIGGERdeleRow_orderdetailsAFTERdeleteONorderdetailsFOREACHROWBEGININSERTINTOorderdetails_rowLogVALUES(user,sysdate,’delete’,:old.orderid,:old.productid);END;ORACLE10g数据库管理、应用与开发1710.2DML触发器例3:创建一个BEFORE触发器,使得在向ORDERS表中插入记录之前对ShippedDate字段进行检测,要求其值不允许为周六或周日,发货时间应在8-18点之间。否则将提示错误’发货时间应为工作时间’。ORACLE10g数据库管理、应用与开发1810.2DML触发器CREATEORREPLACETRIGGERsecure_shippeddateBEFOREINSERTONordersForeachrowBEGINIF(TO_CHAR(:new.shippeddate,’DY’)in(‘SAT’,’SUN’))OR(to_number(TO_CHAR(:new.shippeddate,’HH24’))NOTBETWEEN8AND18)THENRAISE_APPLICATION_ERROR(-20500,’发货时间应为工作时间’);ENDIF;END;ORACLE10g数据库管理、应用与开发19使用“:old”和“:new”应注意：在BEFORE类型行级触发器和AFTER类型行级触发器中使用这些标识符。在语句级触发器中不要使用这些标识符。在PL/SQL语句或SQL语句中，这些标识符前加上冒号（:）来引用它们。在行级触发器的WHEN条件中使用该标识符时，前面不要加冒号（:）.在BEFORE触发器中不能修改“:old”，在AFTER触发器中不能修改“:new”10.2DML触发器ORACLE10g数据库管理、应用与开发20触发时机BEFORE后，:new的值还没有被插入到表里，因此:new可以修改，而此时，:old值是表中没有被修改的值，因此不能被修改触发器脚本触发时机BEFORE（事件）001GREENMED98.1002REDMED1241003REDSMALL100.1004BLUELARGE54.9005REDMED124.1partnocolorsizeweight触发对象表:new:oldORACLE10g数据库管理、应用与开发21触发器事件（如INSERT、UPDATE、DELETE等）触发器脚本:new:old触发时机AFTER后，:new的值已经被插入到表里，因此:new不能修改001GREENMED98.1002REDMED1241003REDSMALL100.1004BLUELARGE54.9005REDMED124.1partnocolorsizeweight触发时机AFTER（事件）ORACLE10g数据库管理、应用与开发2210.2DML触发器例句4：用触发器实现在用户添加数据时,使用序列生成的数字,为表的主键提供自动编号的键值。--创建表CREATETABLEtemp_table(temp_idnumber,namevarchar2(30));--创建序列CREATESEQUENCEincrIDSTARTWITH10INCREMENTBY5NOMAXVALUECACHE10;ORACLE10g数据库管理、应用与开发2310.2DML触发器--创建触发器以提供自动编号的键值CREATEORREPLACETRIGGERincr_triggerBEFOREINSERTONtemp_tableFOREACHROWDECLAREnext_nonumber;BEGINSELECTincrID.nextvalINTOnext_noFROMDUAL;:new.temp_id:=next_no;END;ORACLE10g数据库管理、应用与开发24当一个触发器中的触发事件中既有删除、更新又有插入时，如何判断触发器是因哪个事件而动作？在IF语句中使用触发器谓词（INSERTING、UPDATING、DELETING）10.2DML触发器3、多条件触发形式:CREATEORREPLACETRIGGER…BEFOREinsertORupdateORdeleteON…BEGINIFINSERTINGTHEN…ENDIF;IFDELETINGTHEN…ENDIF;IFUPDATINGTHEN…ENDIF;End;ORACLE10g数据库管理、应用与开发25例句5：用触发器增强参照完整性约束。当orders表的orderid发生变化时，orderdetails表的相关行也跟着进行适当的修改。CREATEORREPLACETRIGGERcascade_updateordersAFTERupdateORdeleteONordersFOREACHROWBEGINIFupdatingTHENUPDATEorderdetailsSETorderdetails.orderid=:new.orderidWHEREorderdetails.orderid=:old.orderid;ENDIF;IFdeletingTHENDELETEFROMorderdetailsWHEREorderdetails.orderid=:old.orderid;ENDIF;END;10.2DML触发器ORACLE10g数据库管理、应用与开发2610.2DML触发器4、级联触发器把一个数据库触发器的动作与另一个触发器联系起来，使之触发另一个触发器。例5:创建3个表A、B、C，在表A上设置一个INSERT触发器，用于向表B添加一条记录，在表B上设置一个INSERT触发器，用于向表C添加一条记录，，在表C上设置一个INSERT触发器，用于对Ａ表中的所有记录进行更新(+10）1)创建A、B、C三张表CREATETABLEA(AIDnumber);CREATETABLEB(BIDnumber);CREATETABLEC(CIDnumber);ORACLE10g数据库管理、应用与开发2710.2DML