使用数据库触发器

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

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

3、70 20061200601158110 2006120083100440 出库流水号出库流水号出库商品出库商品ID出库数量出库数量 2006120058115730 2006120060115860 2006120083100455 库存表库存表GOODS 商品出库表商品出库表OUTGOODS 商品入库表商品入库表INGOODS 每天有商品的入库和出库，每天有商品的入库和出库， 如何确定商品的库存量？如何确定商品的库存量？ CISSST Oracle 10g数据库管理课程 概述 使用数据库触发器 由于入库操作或者出库操作都可能影响到商品的库存量， 所以可以考虑在入库表或者出库表增加一个触发器

4、，当对入库表 或者出库表做DML操作（INSERT、UPDATE、DELETE） 操作时，都会激活入库表或者出库表上的触发器， 进而通过触发器中的逻辑修改库存表中的库存量。 要完整实现这一过程，需要理解两个概念： 1、数据库中的事务处理 2、JAVA JDBC调用过程 CISSST Oracle 10g数据库管理课程 最常见的数据库触发器用法是： 为DDL和DML操作进行审计 为强制执行复杂验证规则，防止错误的或不一致的数据输入到数据 库中。 当特定行为发生时，执行与其相关的行为。 强制执行复杂的数据完整性关系。在某些情况下，如父记录更新以 后子记录上要执行级联更新操作等。如果不使用数据库触发

5、器，将 无法公开的指定这种完整新的约束关系。 自动生成派生值。 处理系统事件。 概述 使用数据库触发器 CISSST Oracle 10g数据库管理课程 数据库触发器是一种响应数据库事件而执行的存储过程。该事件被称 为触发器事件，它可以为下面任意一个事件： DML操作 一个系统事件，如数据库STARTUP、SHUTDOWN、 SERVERERROR 一个用户事件，如LOGON、LOGOFF 触发器事件是在执行触发器语句时进行初始化的。触发器事件在数据 库启动与关闭或者用户登录与登出时也能进行初始化。 PL/SQL触发器：种类与定义 CISSST Oracle 10g数据库管理课程 触发器的种类

6、 触发器的种类： DML INSTEAD-OF 系统和用户事件触发器 CISSST Oracle 10g数据库管理课程 触发器的组成 触发事件：即在何种情况下触发TRIGGER; 例如：INSERT, UPDATE, DELETE。 触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还 是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。 触发器本身：即该TRIGGER 被触发之后的目的和意图，正是触发器 本身要做的事情。 例如：PL/SQL 块。 触发频率：说明触发器内定义的动作被执行的次数。即语句级 (STATEMENT)触发器和行级(ROW)触发器。

7、语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器 只执行一次； 行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响 的每一行数据，触发器都单独执行一次。 CISSST Oracle 10g数据库管理课程 例子： 触发器的定义 CREATE OR REPLACETRIGGER trigger_name BEFORE|AFTER trigger_event ON table_reference FOR EACH ROW WHEN trigger_condition trigger_body; CISSST Oracle 10g数据库管理课程 定义DML触发器 CRE

8、ATE OR REPLACE TRIGGER ai_org_trig AFTER INSERT ON org_tab FOR EACH ROW BEGIN UPDATE sec_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; 触发器事件 触发器主体 CISSST Oracle 10g数据库管理课程 触发器的任何部分都不能接受参数，但是它可

9、以包含一个可选的声明部分 和异常处理部分。BEGINEND部分可以包含SQL和PL/SQL语句，和对 PL/SQL过程和函数的调用。 触发器的主体可以是一个单独的调用PL/SQL过程或函数的CALL语句，或 在PL/SQL中发布的Java存储过程。 触发器的定义 CISSST Oracle 10g数据库管理课程 有两种类型的DML触发器： ROW级触发器 STATEMENT级触发器 ROW级触发器和STATEMENT级触发器 CISSST Oracle 10g数据库管理课程 ROW级触发器和STATEMENT级触发器 ROW级触发器级触发器STATEMENT触发器触发器 触发器语句每影响一行就

10、点火一次只为触发器语句点火一次 如果触发器事件没有影响到行，就不 点火 哪怕触发器事件没有影响到任何记录， 也会点火 可以指定BEFORE和AFTER可以指定BEFORE和AFTER 触发器主体可以访问行数据触发器主体不可以访问行数据 正受触发事件的影响正受触发事件的影响 AFTER ROW触发器锁定行数据不锁定行 CISSST Oracle 10g数据库管理课程 该子句定义的是正在被触发事件修改的数据是怎样在触发器的内部使 用的。默认时，在ROW级触发器中，正被修改的数据在触发器内部是 可用的，可以通过两种相关标识（：NEW和：OLD）进行引用。 这些相关的标识把当前行的数据保存为一个类型为

11、 table_name%ROWTYPE的记录，其中table_name是定义了DML触发器 的数据库表的名称。 在触发器内部引用NEW和OLD应该使用冒号（:）。 只能对ROW级触发器使用REFERENING子句。如果在STATEMENT级 触发器中使用了REFERENING子句，将会产生编译错误。 REFERENCING子句 CISSST Oracle 10g数据库管理课程 :NEW和:OLD的值对于INSERT、UPDATE和DELETE触发事件来说是 不同的。在INSERT语句中，只定义:NEW。对于UPDATE，:NEW 和:OLD都是可用的。在DELETE中，只定义了:OLD。 RE

12、FERENCING子句 CISSST Oracle 10g数据库管理课程 WHEN子句定义所有要为触发器主体执行而定义的WHERE条件。该 WHERE条件适用于正被触发事件影响的行。如果该条件的一行的值 为TRUE，触发器主体就会执行。 WHEN子句后紧接着FOR EACH ROW子句。 在WHEN子句中引用OLD和NEW时，没有使用冒号（:）。 WHEN子句 CISSST Oracle 10g数据库管理课程 判断insert操作 INSERTING 判断update操作UPDATEING 判断delete操作DELETEING 判断DML操作 CISSST Oracle 10g数据库管理课程

13、 INSTEAD OF触发器是Oracle用来替换所使用的实际语句而执行的触发 器。Instead ofInstead of触发器执行时，激发它的触发器执行时，激发它的DMLDML语句不执行。语句不执行。Instead Instead ofof触发器是行级触发器。触发器是行级触发器。 为什么要使用为什么要使用INSTEAD OFINSTEAD OF触发器？触发器？ INSTEAD OF触发器 CISSST Oracle 10g数据库管理课程 1、更新视图时 2、修改对象视图、嵌套表，以及其他使用了DISTINCT、GROUP BY、 CONNECT BY、START WITH、以及COUNT（

14、）和SUM（）这样的 聚合函数的视图。 INSTEAD OF触发器 CISSST Oracle 10g数据库管理课程 实例讲解： 建立一个emp表的副本：empcopy，建立一个dept表的副本deptcopy INSTEAD OF触发器 create table empcopy as select * from emp; create table deptcopy as select * from dept; alter table deptcopy add (mgrno number(4),depttype number(4); CISSST Oracle 10g数据库管理课程 实例讲解：

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

16、racle 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) values(CLARK,SALES,DALLAS); 直接插值会报错！需要建立触发器！直接插值会报错！需要建立触发器！ CISSST Oracle 10g数据库管理课程 实例讲解： INSTEAD

17、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=:new.dname and loc=:new.loc; end; CISSST Oracle 10g数据库管理课程 DML触发器都是在DML事件

18、上被激活，而系统触发器可以在两种不同的 系统事件上被激活（DDL事件和数据库事件）。 DDL事件包括：Create 、 Alter 、Drop 等 数据库事件包括：服务器的启动或者关闭，用户的登录或者退出等， 以及服务器的错误等。 创建系统触发器 create or replace trigger trigger_name before | after ddl_event_list | database_event_list on database | on schema when clause trigger_body; CISSST Oracle 10g数据库管理课程 创建系统触发器 事件

19、事件允许时机允许时机说说 明明 启动启动之后之后实例启动时激发实例启动时激发 关闭关闭之前之前 实例关闭时激发，如果数据库异常关闭，则该事件也许不会激实例关闭时激发，如果数据库异常关闭，则该事件也许不会激 发。发。 服务器错误服务器错误之后之后只要错误发生就激发只要错误发生就激发 登录登录之后之后在用户成功链接数据库后激发在用户成功链接数据库后激发 注销注销之前之前在用户注销开始时激发在用户注销开始时激发 创建创建之前，之后之前，之后在创建模式对象之前或之后激发在创建模式对象之前或之后激发 删除删除之前，之后之前，之后在撤销模式对象之前或之后激发在撤销模式对象之前或之后激发 更改更改之前，之后

20、之前，之后在更改模式对象之前或之后激发在更改模式对象之前或之后激发 CISSST Oracle 10g数据库管理课程 1、数据库的触发器不管触发事件何时发生都将被激活；而模式触发器 只有在指定的模式触发事件发生时才会激活。 2、关键字database和schema决定了系统触发器的类型。 3、如果没有关键字schema来说明模式，则触发器为默认模式。 4、系统触发器可以使用when子句来指定触发器激活条件。然而，对于 每一种系统触发器所指定的条件类型有如下限制： * startup和shoudown触发器不能带有任何条件限制 servererror触发器可以使用错误代码来测试并检查用户标识或者

21、用 户名 logon和logoff触发器可以是使用userid和username测试来检查用户 标识或用户名 DDL触发器可以检查正在修改对象的名称和类型 创建系统触发器 CISSST Oracle 10g数据库管理课程 系统对于使用触发器要访问的表和列有一些限制。 首先，需要了解几个基本的概念： 约束表：由于引用完整性约束而需要从中读取数据的表。 触发表：对于触发器而言，就是触发器为之定义的表。 变异表：就是当前DML操作所影响的表。 关于变异表的问题讨论 CISSST Oracle 10g数据库管理课程 创建一个表registered_emp create table registered

22、_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) ) 关于变异表的问题讨论 CISSST Oracle 10g数据库管理课程 表registered_emp添加了两个外键，分

23、别引用了EMP表的empno，和 DEPT表的deptno。 这样这样EMPEMP和和DEPTDEPT表就称为表就称为registered_empregistered_emp表的约束表。表的约束表。 由于存在引用完整性约束，如果对EMP表或者DEPT表做DML操作，那么 registered_emp表在操作期间也是变异的。 触发器体中的语句不能进行下述操作： 1、读取或修改任何触发语句的变异表，这也包括触发表本身。 2、读取或修改触发表中的约束表的主关键字，唯一关键字或外部关键 字的列。除此之外的其它列可以修改。 上述限制适用与所有的行级触发器，只有当DELETE CASCADE操作而激 发语

24、句触发器时，它们才适用于语句级触发器。 关于变异表的问题讨论 CISSST Oracle 10g数据库管理课程 问题： 在在EMPEMP表中，每个部门的员工名额限制在表中，每个部门的员工名额限制在6 6个。需要通过对个。需要通过对EMPEMP表进行表进行 INSERTINSERT操作或者操作或者UPDATEUPDATE操作来实现。操作来实现。 关于变异表的问题讨论 CISSST Oracle 10g数据库管理课程 建立触发器 关于变异表的问题讨论 create or replace trigger LimitEmployees before insert or update of deptno

25、 on emp for each row declare 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; CISSST Oracle 10g数据库管理课程 执行下述操作 关于变异表的问题讨论 SQL update emp

26、 set deptno=20 where empno=7782; update emp set deptno=20 where empno=7782 ORA-04091: 表表 SCOTT.EMP 发生了变化，触发器发生了变化，触发器/函数不能读函数不能读 ORA-06512: 在在SCOTT.LIMITEMPLOYEES, line 5 ORA-04088: 触发器触发器 SCOTT.LIMITEMPLOYEES 执行过程中出错执行过程中出错 由于触发器查询了自己的触发表，所以报了由于触发器查询了自己的触发表，所以报了ORA-4091错误。错误。 另外，另外，ORA-4091错误是在触发器激

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

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

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

30、ype 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; CISSST Oracle 10g数据库管理课程 问题的解决： 关于变异表的问题讨论 create or replace trigger RLimitEmployees before insert or update of deptno on emp for each row begin EmpData.v_

31、Numentries:=EmpData.v_Numentries+1; EmpData.v_Deptno(EmpData.v_Numentries):=:new.deptno; EmpData.v_Empno(EmpData.v_Numentries):=:new.empno; end; CISSST Oracle 10g数据库管理课程 问题的解决： 关于变异表的问题讨论 create or replace trigger SLimitEmployees after insert or update of deptno on emp declare v_maxEmp constant numb

32、er:=6; v_currentEmp number; v_empno emp.empno%type; v_deptno emp.deptno%type; begin for v_LoopIndex in 1.EmpData.v_numentries loop v_empno:=EmpData.v_empno(v_LoopIndex); v_deptno:=EmpData.v_deptno(v_LoopIndex); select count(*) into v_currentEmp from emp where deptno=v_deptno; if v_CurrentEmpv_MaxEmp

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

34、量放在包中。 2 2、在上述程序中使用了计数器变量、在上述程序中使用了计数器变量EmpData.v_NumEntriesEmpData.v_NumEntries，当其所在的包初，当其所在的包初 次创建时，该变量被初始化为次创建时，该变量被初始化为0.0.然后，该变量的值由行级触发器修改，语句级然后，该变量的值由行级触发器修改，语句级 触发器对该变量进行引用并在处理结束后将该变量复位为触发器对该变量进行引用并在处理结束后将该变量复位为0 0。这么做是很有必。这么做是很有必 要的，以便本次会话的下一个操作时要的，以便本次会话的下一个操作时UPDATEUPDATE语句才会具有正确的值。语句才会具有正

35、确的值。 3 3、可以使用数据库表而不使用、可以使用数据库表而不使用PL/SQLPL/SQL表。但是不推荐这种技术，因为发出表。但是不推荐这种技术，因为发出 UPDATEUPDATE的同时进行的多个会话之间可能相互干扰。封装的同时进行的多个会话之间可能相互干扰。封装PL/SQLPL/SQL表在会话中是唯表在会话中是唯 一的，因此它避免了这个问题。一的，因此它避免了这个问题。 关于变异表的问题讨论 CISSST Oracle 10g数据库管理课程 alter trigger disable; 这意味着数据库知道触发器存在，但是不会激活它。 alter tirgger enable; 激活触发器 启用和禁用触发器 CISSST Oracle 10g数据库管理课程 触发器可以采用PL/SQL编写