使用数据库触发器.ppt

Oracle10g数据库管理课程,授课教师:原炜斌QQ：448920091,使用数据库触发器,Oracle10g数据库管理课程,目标（Objectives）：触发器种类与定义只读视图、可更新视图Instead-Of触发器新的数据库触发器,使用数据库触发器,Oracle10g数据库管理课程,数据库触发器是存储在数据库中、根据发生的事件而执行的一种存储子程序。它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。该事件可以是一个DDL操作，如对象的创建、修改或删除；也可以是一个DML操作，如表或视图上的一个INSERT、UPDATE或DELETE操作；也可以是系统事件，如数据库启动和关闭、登录和登出；还可以是一个用户事件，如模式登录和登出。数据库触发器使您能够执行多种函数。,概述,使用数据库触发器,Oracle10g数据库管理课程,概述,使用数据库触发器,库存表GOODS,商品出库表OUTGOODS,商品入库表INGOODS,每天有商品的入库和出库，如何确定商品的库存量？,Oracle10g数据库管理课程,概述,使用数据库触发器,由于入库操作或者出库操作都可能影响到商品的库存量，所以可以考虑在入库表或者出库表增加一个触发器，当对入库表或者出库表做DML操作（INSERT、UPDATE、DELETE）操作时，都会激活入库表或者出库表上的触发器，进而通过触发器中的逻辑修改库存表中的库存量。要完整实现这一过程，需要理解两个概念：1、数据库中的事务处理2、JAVAJDBC调用过程,Oracle10g数据库管理课程,最常见的数据库触发器用法是：为DDL和DML操作进行审计为强制执行复杂验证规则，防止错误的或不一致的数据输入到数据库中。当特定行为发生时，执行与其相关的行为。强制执行复杂的数据完整性关系。在某些情况下，如父记录更新以后子记录上要执行级联更新操作等。如果不使用数据库触发器，将无法公开的指定这种完整新的约束关系。自动生成派生值。处理系统事件。,概述,使用数据库触发器,Oracle10g数据库管理课程,数据库触发器是一种响应数据库事件而执行的存储过程。该事件被称为触发器事件，它可以为下面任意一个事件：DML操作一个系统事件，如数据库STARTUP、SHUTDOWN、SERVERERROR一个用户事件，如LOGON、LOGOFF触发器事件是在执行触发器语句时进行初始化的。触发器事件在数据库启动与关闭或者用户登录与登出时也能进行初始化。,PL/SQL触发器：种类与定义,Oracle10g数据库管理课程,触发器的种类,触发器的种类：DMLINSTEAD-OF系统和用户事件触发器,Oracle10g数据库管理课程,触发器的组成,触发事件：即在何种情况下触发TRIGGER;例如：INSERT,UPDATE,DELETE。触发时间：即该TRIGGER是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER的操作顺序。触发器本身：即该TRIGGER被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL块。触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。,Oracle10g数据库管理课程,例子：,触发器的定义,CREATEORREPLACETRIGGERtrigger_nameBEFORE|AFTERtrigger_eventONtable_referenceFOREACHROWWHENtrigger_conditiontrigger_body;,Oracle10g数据库管理课程,定义DML触发器,CREATEORREPLACETRIGGERai_org_trigAFTERINSERTONorg_tabFOREACHROWBEGINUPDATEsec_hrc_auditSETnum_rows=num_rows+1WHEREhrc_code=:NEW.hrc_code;IF(SQL%NOTFOUND)THENINSERTINTOsec_hrc_auditVALUES(:NEW.hrc_code,1);ENDIF;END;,触发器事件,触发器主体,Oracle10g数据库管理课程,触发器的任何部分都不能接受参数，但是它可以包含一个可选的声明部分和异常处理部分。BEGINEND部分可以包含SQL和PL/SQL语句，和对PL/SQL过程和函数的调用。触发器的主体可以是一个单独的调用PL/SQL过程或函数的CALL语句，或在PL/SQL中发布的Java存储过程。,触发器的定义,Oracle10g数据库管理课程,有两种类型的DML触发器：ROW级触发器STATEMENT级触发器,ROW级触发器和STATEMENT级触发器,Oracle10g数据库管理课程,ROW级触发器和STATEMENT级触发器,Oracle10g数据库管理课程,该子句定义的是正在被触发事件修改的数据是怎样在触发器的内部使用的。默认时，在ROW级触发器中，正被修改的数据在触发器内部是可用的，可以通过两种相关标识（：NEW和：OLD）进行引用。这些相关的标识把当前行的数据保存为一个类型为table_name%ROWTYPE的记录，其中table_name是定义了DML触发器的数据库表的名称。在触发器内部引用NEW和OLD应该使用冒号（:）。只能对ROW级触发器使用REFERENING子句。如果在STATEMENT级触发器中使用了REFERENING子句，将会产生编译错误。,REFERENCING子句,Oracle10g数据库管理课程,:NEW和:OLD的值对于INSERT、UPDATE和DELETE触发事件来说是不同的。在INSERT语句中，只定义:NEW。对于UPDATE，:NEW和:OLD都是可用的。在DELETE中，只定义了:OLD。,REFERENCING子句,Oracle10g数据库管理课程,WHEN子句定义所有要为触发器主体执行而定义的WHERE条件。该WHERE条件适用于正被触发事件影响的行。如果该条件的一行的值为TRUE，触发器主体就会执行。WHEN子句后紧接着FOREACHROW子句。在WHEN子句中引用OLD和NEW时，没有使用冒号（:）。,WHEN子句,Oracle10g数据库管理课程,判断insert操作INSERTING判断update操作UPDATEING判断delete操作DELETEING,判断DML操作,Oracle10g数据库管理课程,INSTEADOF触发器是Oracle用来替换所使用的实际语句而执行的触发器。Insteadof触发器执行时，激发它的DML语句不执行。Insteadof触发器是行级触发器。为什么要使用INSTEADOF触发器？,INSTEADOF触发器,Oracle10g数据库管理课程,1、更新视图时2、修改对象视图、嵌套表，以及其他使用了DISTINCT、GROUPBY、CONNECTBY、STARTWITH、以及COUNT（）和SUM（）这样的聚合函数的视图。,INSTEADOF触发器,Oracle10g数据库管理课程,实例讲解：建立一个emp表的副本：empcopy，建立一个dept表的副本deptcopy,INSTEADOF触发器,createtableempcopyasselect*fromemp;createtabledeptcopyasselect*fromdept;altertabledeptcopyadd(mgrnonumber(4),depttypenumber(4);,Oracle10g数据库管理课程,实例讲解：,INSTEADOF触发器,updatedeptcopysetmgrno=7539wheredeptno=10;updatedeptcopysetmgrno=7566wheredeptno=20;updatedeptcopysetmgrno=7698wheredeptno=30;updatedeptcopysetmgrno=7782wheredeptno=40;createtableprojecttab(prjlevelnumber(4),projnonumber(4),respdeptnumber(2);,Oracle10g数据库管理课程,实例讲解：,INSTEADOF触发器,createorreplaceviewmgr_infoasselecte.ename,d.dname,d.locfromempcopye,deptcopydwheree.empno=d.mgrno;-testing-insertintomgr_info(ename,dname,loc)values(CLARK,SALES,DALLAS);,直接插值会报错！需要建立触发器！,Oracle10g数据库管理课程,实例讲解：,INSTEADOF触发器,createorreplacetriggermgrinfoinsertinsteadofinsertonmgr_infodeclarev_empnoempcopy.empno%type;beginselectempnointov_empnofromempcopywhereename=:new.ename;updatedeptcopysetmgrno=v_empnowheredname=:new.dnameandloc=:new.loc;end;,Oracle10g数据库管理课程,DML触发器都是在DML事件上被激活，而系统触发器可以在两种不同的系统事件上被激活（DDL事件和数据库事件）。DDL事件包括：Create、Alter、Drop等数据库事件包括：服务器的启动或者关闭，用户的登录或者退出等，以及服务器的错误等。,创建系统触发器,createorreplacetriggertrigger_namebefore|afterddl_event_list|database_event_listondatabase|onschemawhenclausetrigger_body;,Oracle10g数据库管理课程,创建系统触发器,Oracle10g数据库管理课程,1、数据库的触发器不管触发事件何时发生都将被激活；而模式触发器只有在指定的模式触发事件发生时才会激活。2、关键字database和schema决定了系统触发器的类型。3、如果没有关键字schema来说明模式，则触发器为默认模式。4、系统触发器可以使用when子句来指定触发器激活条件。然而，对于每一种系统触发器所指定的条件类型有如下限制：*startup和shoudown触发器不能带有任何条件限制servererror触发器可以使用错误代码来测试并检查用户标识或者用户名logon和logoff触发器可以是使用userid和username测试来检查用户标识或用户名DDL触发器可以检查正在修改对象的名称和类型,创建系统触发器,Oracle10g数据库管理课程,系统对于使用触发器要访问的表和列有一些限制。首先，需要了解几个基本的概念：约束表：由于引用完整性约束而需要从中读取数据的表。触发表：对于触发器而言，就是触发器为之定义的表。变异表：就是当前DML操作所影响的表。,关于变异表的问题讨论,Oracle10g数据库管理课程,创建一个表registered_empcreatetableregistered_emp(empnonumber(5)notnull,deptnonumber(2),constraintrs_deptnocheck(deptnoin(10,20,30,40,50),constraintrs_deptno_fkforeignkey(deptno)referencesdept(deptno),constraintrs_empno_fkforeignkey(empno)referencesemp(empno),关于变异表的问题讨论,Oracle10g数据库管理课程,表registered_emp添加了两个外键，分别引用了EMP表的empno，和DEPT表的deptno。这样EMP和DEPT表就称为registered_emp表的约束表。由于存在引用完整性约束，如果对EMP表或者DEPT表做DML操作，那么registered_emp表在操作期间也是变异的。触发器体中的语句不能进行下述操作：1、读取或修改任何触发语句的变异表，这也包括触发表本身。2、读取或修改触发表中的约束表的主关键字，唯一关键字或外部关键字的列。除此之外的其它列可以修改。上述限制适用与所有的行级触发器，只有当DELETECASCADE操作而激发语句触发器时，它们才适用于语句级触发器。,关于变异表的问题讨论,Oracle10g数据库管理课程,问题：在EMP表中，每个部门的员工名额限制在6个。需要通过对EMP表进行INSERT操作或者UPDATE操作来实现。,关于变异表的问题讨论,Oracle10g数据库管理课程,建立触发器,关于变异表的问题讨论,createorreplacetriggerLimitEmployeesbeforeinsertorupdateofdeptnoonempforeachrowdeclarev_Maxempconstantnumber:=6;v_currentempnumber;beginselectcount(*)intov_currentempfromempwheredeptno=:new.deptno;if(v_currentemp+1v_maxemp)thenraise_application_error(-20000,员工数量超过名额限制);endif;end;,Oracle10g数据库管理课程,执行下述操作,关于变异表的问题讨论,SQLupdateempsetdeptno=20whereempno=7782;updateempsetdeptno=20whereempno=7782ORA-04091:表SCOTT.EMP发生了变化，触发器/函数不能读ORA-06512:在SCOTT.LIMITEMPLOYEES,line5ORA-04088:触发器SCOTT.LIMITEMPLOYEES执行过程中出错由于触发器查询了自己的触发表，所以报了ORA-4091错误。另外，ORA-4091错误是在触发器激活时触发的，而不是在创建是触发的。,Oracle10g数据库管理课程,如何解决这个问题？分析：EMP表仅仅对于行级触发器是变异的，这意味着我们不能在行级触发器中对该表进行查询，然而在表级触发器中是可以的。是不是只要将刚才的行级触发器改为表级触发器就可以了？也不行！因为在触发器体内部需要根据部门编号判断是否该部门已经有6个人了，即需要在触发器体内通过:new.deptno引用DML语句中的部门编号，而表级触发器中不能使用:new.deptno。怎么办？,关于变异表的问题讨论,Oracle10g数据库管理课程,问题的解决：定义两个触发器，一个行级触发器，一个表级触发器。在行级触发器中，记录:new.deptno的值，但我们不查询EMP表，在表级触发器中，通过记录的deptno的值，进行查询统计，实施业务规则。如果记录:new.deptno的值？可以使用包的内部PL/SQL表来记录。这样，可以在每次更新时保存多个值，而且每个会话都得到自己的包变量的实例，因此，我们没有必要担心由于不同的会话同时进行的更新。,关于变异表的问题讨论,Oracle10g数据库管理课程,问题的解决：,关于变异表的问题讨论,createorreplacepackageEmpDataistypet_deptnoistableofemp.deptno%typeindexbybinary_integer;typet_empnoistableofemp.empno%typeindexbybinary_integer;v_deptnot_deptno;v_empnot_empno;v_numentriesbinary_integer:=0;endEmpData;,Oracle10g数据库管理课程,问题的解决：,关于变异表的问题讨论,createorreplacetriggerRLimitEmployeesbeforeinsertorupdateofdeptnoonempforeachrowbeginEmpData.v_Numentries:=EmpData.v_Numentries+1;EmpData.v_Deptno(EmpData.v_Numentries):=:new.deptno;EmpData.v_Empno(EmpData.v_Numentries):=:new.empno;end;,Oracle10g数据库管理课程,问题的解决：,关于变异表的问题讨论,createorreplacetriggerSLimitEmployeesafterinsertorupdateofdeptnoonempdeclarev_maxEmpconstantnumber:=6;v_currentEmpnumber;v_empnoemp.empno%type;v_deptnoemp.deptno%type;beginforv_LoopIndexin1.EmpData.v_numentriesloopv_empno:=EmpData.v_empno(v_LoopIndex);v_deptno:=EmpData.v_deptno(v_LoopIndex);selectcount(*)intov_currentEmpfromempwheredeptno=v_deptno;ifv_CurrentEmpv_MaxEmpthenraise_application_error(-20000,部门编号为:|v_deptno|的部门员工数量超标);endif;endloop;EmpData.v_numentries:=0;end;,Oracle10g数据库管理课程,对问题的再思考？1、由于PL/SQL表是位于包中，所以这些表对于行级触发器和语句级触发器都是可见的，确保变量的全局性的唯一方法是把要定义的全局变量放在包中。2、在上述程序中使用了计数器变量EmpData.v_NumEntries，当其所在的包初次创建时，该变量被初始化为0.然后，该变量的值由行级触发器修改，语句级触发器对该变量进行引用并在处理结束后将该变量复位为0。这么做是很有必要的，以便本次会话的下一个操作时UPDATE语句才会具有正确的值。3、可以使用数据库表而不使用PL/SQL表。但是不推荐这种技术，因为发出UPDATE的同时进行的多个会话之间可能相互干扰。封装PL/SQL表在会话中是唯一的，因此它避免了这个问题。,关于变异表的问题讨论,Oracle10g数据库管理课程,altertriggerdisable;这意味着数据库知道触发器存在，但是不会激活它。altertirggerenable;激活触发器,启用和禁用触发器,Oracle10g数据库管理课程,触发器可以采用PL/SQL编写，但是它不能够COMMIT或者ROLLBACK当前的事务处理。Oracle不允许在触发器中进行COMMIT和ROLLBACK。由于DDL语句要进行隐式COMMIT，所以它们也不允许在触发器中使用。,事务处理和触发器,Oracle10g数据库管理课程,descuser_tirggers;selecttrigger_namef