《使用数据库触发器.ppt》由会员分享，可在线阅读，更多相关《使用数据库触发器.ppt（45页珍藏版）》请在三一办公上搜索。
1、Oracle 10g数据库管理课程,授课教师:原炜斌QQ：448920091,使用数据库触发器,Oracle 10g数据库管理课程,目标（Objectives）：触发器种类与定义 只读视图、可更新视图 Instead-Of触发器 新的数据库触发器,使用数据库触发器,Oracle 10g数据库管理课程,数据库触发器是存储在数据库中、根据发生的事件而执行的一种存储子程序。它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。该事件可以是一个DDL操作，如对象的创建、修改或删除；也可以是
2、一个DML操作，如表或视图上的一个INSERT、UPDATE或DELETE操作；也可以是系统事件，如数据库启动和关闭、登录和登出；还可以是一个用户事件，如模式登录和登出。数据库触发器使您能够执行多种函数。,概述,使用数据库触发器,Oracle 10g数据库管理课程,概述,使用数据库触发器,库存表GOODS,商品出库表OUTGOODS,商品入库表INGOODS,每天有商品的入库和出库，如何确定商品的库存量？,Oracle 10g数据库管理课程,概述,使用数据库触发器,由于入库操作或者出库操作都可能影响到商品的库存量，所以可以考虑在入库表或者出库表增加一个触发器，当对入库表或者出库表做DML操作（
3、INSERT、UPDATE、DELETE）操作时，都会激活入库表或者出库表上的触发器，进而通过触发器中的逻辑修改库存表中的库存量。要完整实现这一过程，需要理解两个概念：1、数据库中的事务处理2、JAVA JDBC调用过程,Oracle 10g数据库管理课程,最常见的数据库触发器用法是：为DDL和DML操作进行审计 为强制执行复杂验证规则，防止错误的或不一致的数据输入到数据 库中。当特定行为发生时，执行与其相关的行为。强制执行复杂的数据完整性关系。在某些情况下，如父记录更新以 后子记录上要执行级联更新操作等。如果不使用数据库触发器，将 无法公开的指定这种完整新的约束关系。自动生成派生值。处理系统
4、事件。,概述,使用数据库触发器,Oracle 10g数据库管理课程,数据库触发器是一种响应数据库事件而执行的存储过程。该事件被称为触发器事件，它可以为下面任意一个事件：DML操作 一个系统事件，如数据库STARTUP、SHUTDOWN、SERVERERROR 一个用户事件，如LOGON、LOGOFF触发器事件是在执行触发器语句时进行初始化的。触发器事件在数据库启动与关闭或者用户登录与登出时也能进行初始化。,PL/SQL触发器：种类与定义,Oracle 10g数据库管理课程,触发器的种类,触发器的种类：DML INSTEAD-OF 系统和用户事件触发器,Oracle 10g数据库管理课程,触发器
5、的组成,触发事件：即在何种情况下触发TRIGGER;例如：INSERT,UPDATE,DELETE。触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。触发器本身：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL 块。触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一
6、行数据，触发器都单独执行一次。,Oracle 10g数据库管理课程,例子：,触发器的定义,CREATE OR REPLACETRIGGER trigger_name BEFORE|AFTER trigger_event ON table_reference FOR EACH ROW WHEN trigger_condition trigger_body;,Oracle 10g数据库管理课程,定义DML触发器,CREATE OR REPLACE TRIGGER ai_org_trigAFTER INSERT ON org_tabFOR EACH ROWBEGIN UPDATE sec_hrc_a
7、udit SET num_rows=num_rows+1 WHERE hrc_code=:NEW.hrc_code;IF(SQL%NOTFOUND)THEN INSERT INTO sec_hrc_audit VALUES(:NEW.hrc_code,1);END IF;END;,触发器事件,触发器主体,Oracle 10g数据库管理课程,触发器的任何部分都不能接受参数，但是它可以包含一个可选的声明部分和异常处理部分。BEGINEND部分可以包含SQL和PL/SQL语句，和对PL/SQL过程和函数的调用。触发器的主体可以是一个单独的调用PL/SQL过程或函数的CALL语句，或在PL/SQL中发
8、布的Java存储过程。,触发器的定义,Oracle 10g数据库管理课程,有两种类型的DML触发器：ROW级触发器 STATEMENT级触发器,ROW级触发器和STATEMENT级触发器,Oracle 10g数据库管理课程,ROW级触发器和STATEMENT级触发器,Oracle 10g数据库管理课程,该子句定义的是正在被触发事件修改的数据是怎样在触发器的内部使用的。默认时，在ROW级触发器中，正被修改的数据在触发器内部是可用的，可以通过两种相关标识（：NEW和：OLD）进行引用。这些相关的标识把当前行的数据保存为一个类型为table_name%ROWTYPE的记录，其中table_name是
9、定义了DML触发器的数据库表的名称。在触发器内部引用NEW和OLD应该使用冒号（:）。只能对ROW级触发器使用REFERENING子句。如果在STATEMENT级触发器中使用了REFERENING子句，将会产生编译错误。,REFERENCING子句,Oracle 10g数据库管理课程,:NEW和:OLD的值对于INSERT、UPDATE和DELETE触发事件来说是不同的。在INSERT语句中，只定义:NEW。对于UPDATE，:NEW和:OLD都是可用的。在DELETE中，只定义了:OLD。,REFERENCING子句,Oracle 10g数据库管理课程,WHEN子句定义所有要为触发器主体执行
10、而定义的WHERE条件。该WHERE条件适用于正被触发事件影响的行。如果该条件的一行的值为TRUE，触发器主体就会执行。WHEN子句后紧接着FOR EACH ROW子句。在WHEN子句中引用OLD和NEW时，没有使用冒号（:）。,WHEN子句,Oracle 10g数据库管理课程,判断insert操作 INSERTING判断update操作UPDATEING判断delete操作DELETEING,判断DML操作,Oracle 10g数据库管理课程,INSTEAD OF触发器是Oracle用来替换所使用的实际语句而执行的触发器。Instead of触发器执行时，激发它的DML语句不执行。Inste
11、ad of触发器是行级触发器。为什么要使用INSTEAD OF触发器？,INSTEAD OF触发器,Oracle 10g数据库管理课程,1、更新视图时2、修改对象视图、嵌套表，以及其他使用了DISTINCT、GROUP BY、CONNECT BY、START WITH、以及COUNT（）和SUM（）这样的聚合函数的视图。,INSTEAD OF触发器,Oracle 10g数据库管理课程,实例讲解：建立一个emp表的副本：empcopy，建立一个dept表的副本deptcopy,INSTEAD OF触发器,create table empcopy asselect*from emp;create
12、table deptcopy asselect*from dept;alter table deptcopyadd(mgrno number(4),depttype number(4);,Oracle 10g数据库管理课程,实例讲解：,INSTEAD OF触发器,update deptcopy set mgrno=7539 where deptno=10;update deptcopy set mgrno=7566 where deptno=20;update deptcopy set mgrno=7698 where deptno=30;update deptcopy set mgrno=7
13、782 where deptno=40;create table projecttab(prjlevel number(4),projno number(4),respdept number(2);,Oracle 10g数据库管理课程,实例讲解：,INSTEAD OF触发器,create or replace view mgr_info as select e.ename,d.dname,d.loc from empcopy e,deptcopy d where e.empno=d.mgrno;-testing-insert into mgr_info(ename,dname,loc)valu
14、es(CLARK,SALES,DALLAS);,直接插值会报错！需要建立触发器！,Oracle 10g数据库管理课程,实例讲解：,INSTEAD OF触发器,create or replace trigger mgrinfoinsert instead of insert on mgr_info declare v_empno empcopy.empno%type;begin select empno into v_empno from empcopy where ename=:new.ename;update deptcopy set mgrno=v_empno where dname=:n
15、ew.dname and loc=:new.loc;end;,Oracle 10g数据库管理课程,DML触发器都是在DML事件上被激活，而系统触发器可以在两种不同的系统事件上被激活（DDL事件和数据库事件）。DDL事件包括：Create、Alter、Drop 等数据库事件包括：服务器的启动或者关闭，用户的登录或者退出等，以及服务器的错误等。,创建系统触发器,create or replace trigger trigger_namebefore|afterddl_event_list|database_event_liston database|on schemawhen clausetrig
16、ger_body;,Oracle 10g数据库管理课程,创建系统触发器,Oracle 10g数据库管理课程,1、数据库的触发器不管触发事件何时发生都将被激活；而模式触发器只有在指定的模式触发事件发生时才会激活。2、关键字database和schema决定了系统触发器的类型。3、如果没有关键字schema来说明模式，则触发器为默认模式。4、系统触发器可以使用when子句来指定触发器激活条件。然而，对于每一种系统触发器所指定的条件类型有如下限制：*startup和shoudown触发器不能带有任何条件限制 servererror触发器可以使用错误代码来测试并检查用户标识或者用户名 logon和lo
17、goff触发器可以是使用userid和username测试来检查用户标识或用户名 DDL触发器可以检查正在修改对象的名称和类型,创建系统触发器,Oracle 10g数据库管理课程,系统对于使用触发器要访问的表和列有一些限制。首先，需要了解几个基本的概念：约束表：由于引用完整性约束而需要从中读取数据的表。触发表：对于触发器而言，就是触发器为之定义的表。变异表：就是当前DML操作所影响的表。,关于变异表的问题讨论,Oracle 10g数据库管理课程,创建一个表registered_empcreate table registered_emp(empno number(5)not null,dept
18、no number(2),constraint rs_deptno check(deptno in(10,20,30,40,50),constraint rs_deptno_fk foreign key(deptno)references dept(deptno),constraint rs_empno_fk foreign key(empno)references emp(empno),关于变异表的问题讨论,Oracle 10g数据库管理课程,表registered_emp添加了两个外键，分别引用了EMP表的empno，和DEPT表的deptno。这样EMP和DEPT表就称为register
19、ed_emp表的约束表。由于存在引用完整性约束，如果对EMP表或者DEPT表做DML操作，那么registered_emp表在操作期间也是变异的。触发器体中的语句不能进行下述操作：1、读取或修改任何触发语句的变异表，这也包括触发表本身。2、读取或修改触发表中的约束表的主关键字，唯一关键字或外部关键字的列。除此之外的其它列可以修改。上述限制适用与所有的行级触发器，只有当DELETE CASCADE操作而激发语句触发器时，它们才适用于语句级触发器。,关于变异表的问题讨论,Oracle 10g数据库管理课程,问题：在EMP表中，每个部门的员工名额限制在6个。需要通过对EMP表进行INSERT操作或者
20、UPDATE操作来实现。,关于变异表的问题讨论,Oracle 10g数据库管理课程,建立触发器,关于变异表的问题讨论,create or replace trigger LimitEmployeesbefore insert or update of deptno on empfor each rowdeclare v_Maxemp constant number:=6;v_currentemp number;begin select count(*)into v_currentemp from emp where deptno=:new.deptno;if(v_currentemp+1v_m
21、axemp)then raise_application_error(-20000,员工数量超过名额限制);end if;end;,Oracle 10g数据库管理课程,执行下述操作,关于变异表的问题讨论,SQL update emp set deptno=20 where empno=7782;update emp set deptno=20 where empno=7782ORA-04091:表 SCOTT.EMP 发生了变化，触发器/函数不能读ORA-06512:在SCOTT.LIMITEMPLOYEES,line 5ORA-04088:触发器 SCOTT.LIMITEMPLOYEES 执
22、行过程中出错由于触发器查询了自己的触发表，所以报了ORA-4091错误。另外，ORA-4091错误是在触发器激活时触发的，而不是在创建是触发的。,Oracle 10g数据库管理课程,如何解决这个问题？分析：EMP表仅仅对于行级触发器是变异的，这意味着我们不能在行级触发器中对该表进行查询，然而在表级触发器中是可以的。是不是只要将刚才的行级触发器改为表级触发器就可以了？也不行！因为在触发器体内部需要根据部门编号判断是否该部门已经有6个人了，即需要在触发器体内通过:new.deptno引用DML语句中的部门编号，而表级触发器中不能使用:new.deptno。怎么办？,关于变异表的问题讨论,Oracl
23、e 10g数据库管理课程,问题的解决：定义两个触发器，一个行级触发器，一个表级触发器。在行级触发器中，记录:new.deptno的值，但我们不查询EMP表，在表级触发器中，通过记录的deptno的值，进行查询统计，实施业务规则。如果记录:new.deptno的值？可以使用包的内部PL/SQL表来记录。这样，可以在每次更新时保存多个值，而且每个会话都得到自己的包变量的实例，因此，我们没有必要担心由于不同的会话同时进行的更新。,关于变异表的问题讨论,Oracle 10g数据库管理课程,问题的解决：,关于变异表的问题讨论,create or replace package EmpData is ty
24、pe t_deptno is table of emp.deptno%type index by binary_integer;type t_empno is table of emp.empno%type index by binary_integer;v_deptno t_deptno;v_empno t_empno;v_numentries binary_integer:=0;end EmpData;,Oracle 10g数据库管理课程,问题的解决：,关于变异表的问题讨论,create or replace trigger RLimitEmployeesbefore insert or
25、update of deptno on empfor each rowbegin EmpData.v_Numentries:=EmpData.v_Numentries+1;EmpData.v_Deptno(EmpData.v_Numentries):=:new.deptno;EmpData.v_Empno(EmpData.v_Numentries):=:new.empno;end;,Oracle 10g数据库管理课程,问题的解决：,关于变异表的问题讨论,create or replace trigger SLimitEmployeesafter insert or update of dept
26、no on empdeclarev_maxEmp constant number:=6;v_currentEmp number;v_empno emp.empno%type;v_deptno emp.deptno%type;beginfor v_LoopIndex in 1.EmpData.v_numentries loopv_empno:=EmpData.v_empno(v_LoopIndex);v_deptno:=EmpData.v_deptno(v_LoopIndex);select count(*)into v_currentEmp from empwhere deptno=v_dep
27、tno;if v_CurrentEmpv_MaxEmp thenraise_application_error(-20000,部门编号为:|v_deptno|的部门员工数量超标);end if;end loop;EmpData.v_numentries:=0;end;,Oracle 10g数据库管理课程,对问题的再思考？1、由于PL/SQL表是位于包中，所以这些表对于行级触发器和语句级触发器都是可见的，确保变量的全局性的唯一方法是把要定义的全局变量放在包中。2、在上述程序中使用了计数器变量EmpData.v_NumEntries，当其所在的包初次创建时，该变量被初始化为0.然后，该变量的值由行
28、级触发器修改，语句级触发器对该变量进行引用并在处理结束后将该变量复位为0。这么做是很有必要的，以便本次会话的下一个操作时UPDATE语句才会具有正确的值。3、可以使用数据库表而不使用PL/SQL表。但是不推荐这种技术，因为发出UPDATE的同时进行的多个会话之间可能相互干扰。封装PL/SQL表在会话中是唯一的，因此它避免了这个问题。,关于变异表的问题讨论,Oracle 10g数据库管理课程,alter trigger disable;这意味着数据库知道触发器存在，但是不会激活它。alter tirgger enable;激活触发器,启用和禁用触发器,Oracle 10g数据库管理课程,触发器可
29、以采用PL/SQL编写，但是它不能够COMMIT或者ROLLBACK当前的事务处理。Oracle不允许在触发器中进行COMMIT和ROLLBACK。由于DDL语句要进行隐式COMMIT，所以它们也不允许在触发器中使用。,事务处理和触发器,Oracle 10g数据库管理课程,desc user_tirggers;select trigger_name from user_triggers where table_name=EMPLOYEESand table_owner=HR;select trigger_type,triggering_event,when_clause,trigger_bod
30、y from user_triggers where trigger_name=SECURE_EMPLOYEES;,从数据词典中获取触发器信息,Oracle 10g数据库管理课程,1、触发器名称2、触发器体中不能发出任何的事务控制语句，如：commit、rollback等。同时触发器调用的任何过程或者函数中不能包含有事务控制语句。触发器体不能声明任何LONG或者LONG RAW类型变量。而且:new和:old也不能指向为之定义的表的LONG或者LONG RAW类型的列。触发器体可以引用和使用LOB列，但是不能修改该列的值。这个限制也适用于对象列。3、触发器体:触发器体必须是PL/SQL语句，可
31、以包含CALL语句，被调用的过程可以是PL/SQL存储过程，也可以是C语言或者JAVA例程的包装器。,触发器的限制,Oracle 10g数据库管理课程,4、触发器权限：触发器所有者必须对触发器所引用的对象具有必要的对象权限。由于触发器是已经编译的对象，因此这些权限都必须直接授予，不能通过中间角色给予。与触发器相关的系统权限-CREATE TRIGGER-CREATE ANY TRIGGER-ALTER ANY TRIGGER-DROP ANY TRIGGER-ADMINISTER DATABASE TRIGGER,触发器的限制,Oracle 10g数据库管理课程,触发器小结,触发器可以用于很多目的：它们是一种非常优秀地控制用户表中数据输入和维护的方式，可以让数据建模者确保输入的数据被正确格式化。它们可以让我们将业务规则直接与我们的数据进行绑定，阻止用户规避这些规则。它们能够作为安全特性。可以对它们进行编码以确保只有特定用户才能够修改用户数据。它们可以帮助我们对我们的数据所进行的操作，操作发生时间，以及在数据库或者特定模式的对象上进行处理的时间进行日志记录。它们能够用于指导Oracle如何控制复杂的用户定义数据类型。,
