使用数据库触发器

1、CISSSTOracle 10g数据库管理课程授课教师授课教师: : 原炜斌原炜斌QQQQ：448920091448920091使用数据库触发器CISSSTOracle 10g数据库管理课程目标（Objectives）： 触发器种类与定义 只读视图、可更新视图 Instead-Of触发器 新的数据库触发器使用数据库触发器CISSSTOracle 10g数据库管理课程数据库触发器是存储在数据库中、根据发生的事件而执行的一种存储子程序。它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参

2、数。该事件可以是一个DDL操作，如对象的创建、修改或删除；也可以是一个DML操作，如表或视图上的一个INSERT、UPDATE或DELETE操作；也可以是系统事件，如数据库启动和关闭、登录和登出；还可以是一个用户事件，如模式登录和登出。数据库触发器使您能够执行多种函数。概述使用数据库触发器CISSSTOracle 10g数据库管理课程概述使用数据库触发器商品商品ID库存数量库存数量库存预警量库存预警量1004500101157761011584810入库流水号入库流水号入库商品入库商品ID入库数量入库数量200612005811587020061200601158110200612008310

3、0440出库流水号出库流水号出库商品出库商品ID出库数量出库数量200612005811573020061200601158602006120083100455库存表库存表GOODS商品出库表商品出库表OUTGOODS商品入库表商品入库表INGOODS每天有商品的入库和出库，每天有商品的入库和出库，如何确定商品的库存量？如何确定商品的库存量？CISSSTOracle 10g数据库管理课程概述使用数据库触发器由于入库操作或者出库操作都可能影响到商品的库存量，所以可以考虑在入库表或者出库表增加一个触发器，当对入库表或者出库表做DML操作（INSERT、UPDATE、DELETE）操作时，都会激活入

4、库表或者出库表上的触发器，进而通过触发器中的逻辑修改库存表中的库存量。要完整实现这一过程，需要理解两个概念：1、数据库中的事务处理2、JAVA JDBC调用过程CISSSTOracle 10g数据库管理课程最常见的数据库触发器用法是： 为DDL和DML操作进行审计 为强制执行复杂验证规则，防止错误的或不一致的数据输入到数据 库中。 当特定行为发生时，执行与其相关的行为。 强制执行复杂的数据完整性关系。在某些情况下，如父记录更新以 后子记录上要执行级联更新操作等。如果不使用数据库触发器，将 无法公开的指定这种完整新的约束关系。 自动生成派生值。 处理系统事件。概述使用数据库触发器CISSSTOr

5、acle 10g数据库管理课程数据库触发器是一种响应数据库事件而执行的存储过程。该事件被称为触发器事件，它可以为下面任意一个事件： DML操作 一个系统事件，如数据库STARTUP、SHUTDOWN、 SERVERERROR 一个用户事件，如LOGON、LOGOFF触发器事件是在执行触发器语句时进行初始化的。触发器事件在数据库启动与关闭或者用户登录与登出时也能进行初始化。PL/SQL触发器：种类与定义CISSSTOracle 10g数据库管理课程触发器的种类触发器的种类： DML INSTEAD-OF 系统和用户事件触发器CISSSTOracle 10g数据库管理课程触发器的组成 触发事件：即

6、在何种情况下触发TRIGGER; 例如：INSERT, UPDATE, DELETE。 触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。 触发器本身：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。 语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次； 行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行

7、数据，触发器都单独执行一次。CISSSTOracle 10g数据库管理课程例子：触发器的定义CREATE OR REPLACETRIGGER trigger_name BEFORE|AFTER trigger_event ON table_reference FOR EACH ROW WHEN trigger_condition trigger_body; CISSSTOracle 10g数据库管理课程定义DML触发器CREATE OR REPLACE TRIGGER ai_org_trigAFTER INSERT ON org_tabFOR EACH ROWBEGIN UPDATE sec_

8、hrc_audit 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;触发器事件触发器主体CISSSTOracle 10g数据库管理课程触发器的任何部分都不能接受参数，但是它可以包含一个可选的声明部分和异常处理部分。BEGINEND部分可以包含SQL和PL/SQL语句，和对PL/SQL过程和函数的调用。触发器的主体可以是一个单独的调用PL/SQL过程或函数的CAL

9、L语句，或在PL/SQL中发布的Java存储过程。触发器的定义CISSSTOracle 10g数据库管理课程有两种类型的DML触发器： ROW级触发器 STATEMENT级触发器ROW级触发器和STATEMENT级触发器CISSSTOracle 10g数据库管理课程ROW级触发器和STATEMENT级触发器ROW级触发器级触发器STATEMENT触发器触发器触发器语句每影响一行就点火一次只为触发器语句点火一次如果触发器事件没有影响到行，就不点火哪怕触发器事件没有影响到任何记录，也会点火可以指定BEFORE和AFTER可以指定BEFORE和AFTER触发器主体可以访问行数据触发器主体不可以访问行

10、数据正受触发事件的影响正受触发事件的影响AFTER ROW触发器锁定行数据不锁定行CISSSTOracle 10g数据库管理课程该子句定义的是正在被触发事件修改的数据是怎样在触发器的内部使用的。默认时，在ROW级触发器中，正被修改的数据在触发器内部是可用的，可以通过两种相关标识（：NEW和：OLD）进行引用。这些相关的标识把当前行的数据保存为一个类型为table_name%ROWTYPE的记录，其中table_name是定义了DML触发器的数据库表的名称。在触发器内部引用NEW和OLD应该使用冒号（:）。只能对ROW级触发器使用REFERENING子句。如果在STATEMENT级触发器中使用了

11、REFERENING子句，将会产生编译错误。REFERENCING子句CISSSTOracle 10g数据库管理课程:NEW和:OLD的值对于INSERT、UPDATE和DELETE触发事件来说是不同的。在INSERT语句中，只定义:NEW。对于UPDATE，:NEW和:OLD都是可用的。在DELETE中，只定义了:OLD。REFERENCING子句CISSSTOracle 10g数据库管理课程WHEN子句定义所有要为触发器主体执行而定义的WHERE条件。该WHERE条件适用于正被触发事件影响的行。如果该条件的一行的值为TRUE，触发器主体就会执行。WHEN子句后紧接着FOR EACH ROW

12、子句。在WHEN子句中引用OLD和NEW时，没有使用冒号（:）。WHEN子句CISSSTOracle 10g数据库管理课程判断insert操作 INSERTING判断update操作UPDATEING判断delete操作DELETEING判断DML操作CISSSTOracle 10g数据库管理课程INSTEAD OF触发器是Oracle用来替换所使用的实际语句而执行的触发器。Instead ofInstead of触发器执行时，激发它的触发器执行时，激发它的DMLDML语句不执行。语句不执行。Instead Instead ofof触发器是行级触发器。触发器是行级触发器。为什么要使用为什么要使

13、用INSTEAD OFINSTEAD OF触发器？触发器？INSTEAD OF触发器CISSSTOracle 10g数据库管理课程1、更新视图时2、修改对象视图、嵌套表，以及其他使用了DISTINCT、GROUP BY、CONNECT BY、START WITH、以及COUNT（）和SUM（）这样的聚合函数的视图。INSTEAD OF触发器CISSSTOracle 10g数据库管理课程实例讲解：建立一个emp表的副本：empcopy，建立一个dept表的副本deptcopyINSTEAD OF触发器create table empcopy asselect * from emp;create

14、table deptcopy asselect * from dept;alter table deptcopyadd (mgrno number(4),depttype number(4);CISSSTOracle 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 mg

15、rno=7782 where deptno=40;create table projecttab( prjlevel number(4), projno number(4), respdept number(2) );CISSSTOracle 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(ena

16、me,dname,loc) values(CLARK,SALES,DALLAS);直接插值会报错！需要建立触发器！直接插值会报错！需要建立触发器！CISSSTOracle 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 dep

17、tcopy set mgrno=v_empno where dname=:new.dname and loc=:new.loc; end;CISSSTOracle 10g数据库管理课程DML触发器都是在DML事件上被激活，而系统触发器可以在两种不同的系统事件上被激活（DDL事件和数据库事件）。DDL事件包括：Create 、 Alter 、Drop 等数据库事件包括：服务器的启动或者关闭，用户的登录或者退出等，以及服务器的错误等。创建系统触发器create or replace trigger trigger_namebefore | afterddl_event_list | databas

18、e_event_liston database | on schemawhen clausetrigger_body;CISSSTOracle 10g数据库管理课程创建系统触发器事件事件允许时机允许时机说说 明明启动启动之后之后实例启动时激发实例启动时激发关闭关闭之前之前实例关闭时激发，如果数据库异常关闭，则该事件也许不会激实例关闭时激发，如果数据库异常关闭，则该事件也许不会激发。发。服务器错误服务器错误之后之后只要错误发生就激发只要错误发生就激发登录登录之后之后在用户成功链接数据库后激发在用户成功链接数据库后激发注销注销之前之前在用户注销开始时激发在用户注销开始时激发创建创建之前，之后之前，

19、之后在创建模式对象之前或之后激发在创建模式对象之前或之后激发删除删除之前，之后之前，之后在撤销模式对象之前或之后激发在撤销模式对象之前或之后激发更改更改之前，之后之前，之后在更改模式对象之前或之后激发在更改模式对象之前或之后激发CISSSTOracle 10g数据库管理课程1、数据库的触发器不管触发事件何时发生都将被激活；而模式触发器只有在指定的模式触发事件发生时才会激活。2、关键字database和schema决定了系统触发器的类型。3、如果没有关键字schema来说明模式，则触发器为默认模式。4、系统触发器可以使用when子句来指定触发器激活条件。然而，对于每一种系统触发器所指定的条件类型

20、有如下限制：* startup和shoudown触发器不能带有任何条件限制 servererror触发器可以使用错误代码来测试并检查用户标识或者用户名 logon和logoff触发器可以是使用userid和username测试来检查用户标识或用户名 DDL触发器可以检查正在修改对象的名称和类型 创建系统触发器CISSSTOracle 10g数据库管理课程系统对于使用触发器要访问的表和列有一些限制。首先，需要了解几个基本的概念： 约束表：由于引用完整性约束而需要从中读取数据的表。 触发表：对于触发器而言，就是触发器为之定义的表。 变异表：就是当前DML操作所影响的表。关于变异表的问题讨论CISS

21、STOracle 10g数据库管理课程创建一个表registered_empcreate table registered_emp( empno number(5) not null, deptno 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) )

22、关于变异表的问题讨论CISSSTOracle 10g数据库管理课程表registered_emp添加了两个外键，分别引用了EMP表的empno，和DEPT表的deptno。这样这样EMPEMP和和DEPTDEPT表就称为表就称为registered_empregistered_emp表的约束表。表的约束表。由于存在引用完整性约束，如果对EMP表或者DEPT表做DML操作，那么registered_emp表在操作期间也是变异的。触发器体中的语句不能进行下述操作：1、读取或修改任何触发语句的变异表，这也包括触发表本身。2、读取或修改触发表中的约束表的主关键字，唯一关键字或外部关键字的列。除此之外的

23、其它列可以修改。上述限制适用与所有的行级触发器，只有当DELETE CASCADE操作而激发语句触发器时，它们才适用于语句级触发器。关于变异表的问题讨论CISSSTOracle 10g数据库管理课程问题：在在EMPEMP表中，每个部门的员工名额限制在表中，每个部门的员工名额限制在6 6个。需要通过对个。需要通过对EMPEMP表进行表进行INSERTINSERT操作或者操作或者UPDATEUPDATE操作来实现。操作来实现。关于变异表的问题讨论CISSSTOracle 10g数据库管理课程建立触发器关于变异表的问题讨论create or replace trigger LimitEmployee

24、sbefore 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_maxemp) then raise_application_error(-20000,员工数量超过名额限制); end if;end;CISSSTOracle 10g数据库管理课程执行下述操

25、作关于变异表的问题讨论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 执行过程中出错执行过程中出错由于触发器查询了自己的触发表，所以报了由于触发器查询了自己的触发表，所以报了ORA-4091错误。错误。另外，

26、另外，ORA-4091错误是在触发器激活时触发的，而不是在创建是触发的。错误是在触发器激活时触发的，而不是在创建是触发的。CISSSTOracle 10g数据库管理课程如何解决这个问题？分析：EMP表仅仅对于行级触发器是变异的，这意味着我们不能在行级触发器中对该表进行查询，然而在表级触发器中是可以的。是不是只要将刚是不是只要将刚才的行级触发器改为表级触发器就可以了？才的行级触发器改为表级触发器就可以了？也不行！也不行！因为在触发器体内部需要根据部门编号判断是否该部门已经有6个人了，即需要在触发器体内通过:new.deptno引用DML语句中的部门编号，而表级触发器中不能使用:new.deptn

27、o。怎么办？怎么办？关于变异表的问题讨论CISSSTOracle 10g数据库管理课程问题的解决：定义两个触发器，一个行级触发器，一个表级触发器。定义两个触发器，一个行级触发器，一个表级触发器。在行级触发器中，记录在行级触发器中，记录:new.deptno:new.deptno的值，但我们不查询的值，但我们不查询EMPEMP表，表，在表级触发器中，通过记录的在表级触发器中，通过记录的deptnodeptno的值，进行查询统计，实施业务的值，进行查询统计，实施业务规则。规则。如果记录如果记录:new.deptno:new.deptno的值？可以使用包的内部的值？可以使用包的内部PL/SQLPL/

28、SQL表来记录。这表来记录。这样，可以在每次更新时保存多个值，而且每个会话都得到自己的包变样，可以在每次更新时保存多个值，而且每个会话都得到自己的包变量的实例，因此，我们没有必要担心由于不同的会话同时进行的更新。量的实例，因此，我们没有必要担心由于不同的会话同时进行的更新。关于变异表的问题讨论CISSSTOracle 10g数据库管理课程问题的解决：关于变异表的问题讨论create or replace package EmpData is type t_deptno is table of emp.deptno%type index by binary_integer; type t_emp

29、no 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;CISSSTOracle 10g数据库管理课程问题的解决：关于变异表的问题讨论create or replace trigger RLimitEmployeesbefore insert or update of deptno on empfor each rowbegin EmpData.v_Numentries:=EmpDat

30、a.v_Numentries+1; EmpData.v_Deptno(EmpData.v_Numentries):=:new.deptno; EmpData.v_Empno(EmpData.v_Numentries):=:new.empno;end;CISSSTOracle 10g数据库管理课程问题的解决：关于变异表的问题讨论create or replace trigger SLimitEmployeesafter insert or update of deptno on empdeclarev_maxEmp constant number:=6;v_currentEmp number;v

31、_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_deptno;if v_CurrentEmpv_MaxEmp thenraise_application_error(-20000,部

32、门编号为部门编号为: |v_deptno| 的部门员工数量超标的部门员工数量超标);end if;end loop;EmpData.v_numentries:=0;end;CISSSTOracle 10g数据库管理课程对问题的再思考？1 1、由于、由于PL/SQLPL/SQL表是位于包中，所以这些表对于行级触发器和语句级触发器都表是位于包中，所以这些表对于行级触发器和语句级触发器都是可见的，确保变量的全局性的唯一方法是把要定义的全局变量放在包中。是可见的，确保变量的全局性的唯一方法是把要定义的全局变量放在包中。2 2、在上述程序中使用了计数器变量、在上述程序中使用了计数器变量EmpData.v

33、_NumEntriesEmpData.v_NumEntries，当其所在的包初，当其所在的包初次创建时，该变量被初始化为次创建时，该变量被初始化为0.0.然后，该变量的值由行级触发器修改，语句级然后，该变量的值由行级触发器修改，语句级触发器对该变量进行引用并在处理结束后将该变量复位为触发器对该变量进行引用并在处理结束后将该变量复位为0 0。这么做是很有必。这么做是很有必要的，以便本次会话的下一个操作时要的，以便本次会话的下一个操作时UPDATEUPDATE语句才会具有正确的值。语句才会具有正确的值。3 3、可以使用数据库表而不使用、可以使用数据库表而不使用PL/SQLPL/SQL表。但是不推荐这种技术，因为发出表。但是不推荐这种技术，因为发出UPDATEUPDATE的同时进行的多个会话之间可能相互干扰。封装的同时进行的多个会话之间可能相互干扰。封装PL/SQLPL/SQL表在会话中是唯表在会话中是唯一的，因此它避免了这个问题。一的，因此它避免了这个问题。关于变异表的问题讨论CISSSTOracle 10g数据库管理课程alter trigger disable;这意味着数据库知道触发器存在，但是不会激活它。alter tirgger enable;激活触发器启用和禁用触发器CISSSTOracle 10g数据库管理课程触发器可以采用PL/SQL