使用数据库触发器.ppt

《使用数据库触发器.ppt》由会员分享，可在线阅读，更多相关《使用数据库触发器.ppt（45页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

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

3、量出库数量200612005811573020061200601158602006120083100455库存表库存表GOODS商品出库表商品出库表OUTGOODS商品入库表商品入库表INGOODS每天有商品的入库和出库，每天有商品的入库和出库，如何确定商品的库存量？如何确定商品的库存量？Oracle10g数据库管理课程CISSST概述使用数据库触发器由于入库操作或者出库操作都可能影响到商品的库存量，所以可以考虑在入库表或者出库表增加一个触发器，当对入库表或者出库表做DML操作（INSERT、UPDATE、DELETE）操作时，都会激活入库表或者出库表上的触发器，进而通过触发器中的逻辑修改库存

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

5、。该事件被称为触发器事件，它可以为下面任意一个事件：DML操作一个系统事件，如数据库STARTUP、SHUTDOWN、SERVERERROR一个用户事件，如LOGON、LOGOFF触发器事件是在执行触发器语句时进行初始化的。触发器事件在数据库启动与关闭或者用户登录与登出时也能进行初始化。PL/SQL触发器：种类与定义Oracle10g数据库管理课程CISSST触发器的种类触发器的种类：DMLINSTEAD-OF系统和用户事件触发器Oracle10g数据库管理课程CISSST触发器的组成触发事件：即在何种情况下触发TRIGGER;例如：INSERT,UPDATE,DELETE。触发时间：即该TR

6、IGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。触发器本身：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL 块。触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。Oracle10g数据库管理课程CISSST例子：触发器的定义CREATEORREPLA

7、CETRIGGERtrigger_nameBEFORE|AFTERtrigger_eventONtable_referenceFOREACHROWWHENtrigger_conditiontrigger_body;Oracle10g数据库管理课程CISSST定义DML触发器CREATE OR REPLACE TRIGGER ai_org_trigAFTER INSERT ON org_tabFOR EACH ROWBEGIN UPDATE sec_hrc_audit SET num_rows=num_rows+1 WHERE hrc_code=:NEW.hrc_code;IF(SQL%NOTF

8、OUND)THEN INSERT INTO sec_hrc_audit VALUES(:NEW.hrc_code,1);END IF;END;触发器事件触发器主体Oracle10g数据库管理课程CISSST触发器的任何部分都不能接受参数，但是它可以包含一个可选的声明部分和异常处理部分。BEGINEND部分可以包含SQL和PL/SQL语句，和对PL/SQL过程和函数的调用。触发器的主体可以是一个单独的调用PL/SQL过程或函数的CALL语句，或在PL/SQL中发布的Java存储过程。触发器的定义Oracle10g数据库管理课程CISSST有两种类型的DML触发器：ROW级触发器STATEMENT

9、级触发器ROW级触发器和STATEMENT级触发器Oracle10g数据库管理课程CISSSTROW级触发器和STATEMENT级触发器ROW级触发器级触发器STATEMENT触发器触发器触发器语句每影响一行就点火一次只为触发器语句点火一次如果触发器事件没有影响到行，就不点火哪怕触发器事件没有影响到任何记录，也会点火可以指定BEFORE和AFTER可以指定BEFORE和AFTER触发器主体可以访问行数据触发器主体不可以访问行数据正受触发事件的影响正受触发事件的影响AFTERROW触发器锁定行数据不锁定行Oracle10g数据库管理课程CISSST该子句定义的是正在被触发事件修改的数据是怎样在触

10、发器的内部使用的。默认时，在ROW级触发器中，正被修改的数据在触发器内部是可用的，可以通过两种相关标识（：NEW和：OLD）进行引用。这些相关的标识把当前行的数据保存为一个类型为table_name%ROWTYPE的记录，其中table_name是定义了DML触发器的数据库表的名称。在触发器内部引用NEW和OLD应该使用冒号（:）。只能对ROW级触发器使用REFERENING子句。如果在STATEMENT级触发器中使用了REFERENING子句，将会产生编译错误。REFERENCING子句Oracle10g数据库管理课程CISSST:NEW和:OLD的值对于INSERT、UPDATE和DELE

11、TE触发事件来说是不同的。在INSERT语句中，只定义:NEW。对于UPDATE，:NEW和:OLD都是可用的。在DELETE中，只定义了:OLD。REFERENCING子句Oracle10g数据库管理课程CISSSTWHEN子句定义所有要为触发器主体执行而定义的WHERE条件。该WHERE条件适用于正被触发事件影响的行。如果该条件的一行的值为TRUE，触发器主体就会执行。WHEN子句后紧接着FOREACHROW子句。在WHEN子句中引用OLD和NEW时，没有使用冒号（:）。WHEN子句Oracle10g数据库管理课程CISSST判断insert操作INSERTING判断update操作UPD

12、ATEING判断delete操作DELETEING判断DML操作Oracle10g数据库管理课程CISSSTINSTEAD OF触发器是Oracle用来替换所使用的实际语句而执行的触发器。Instead ofInstead of触发器执行时，激发它的触发器执行时，激发它的DMLDML语句不执行。语句不执行。Instead ofInstead of触发器是行级触发器。触发器是行级触发器。为什么要使用为什么要使用INSTEAD OFINSTEAD OF触发器？触发器？INSTEADOF触发器Oracle10g数据库管理课程CISSST1、更新视图时2、修改对象视图、嵌套表，以及其他使用了DISTI

13、NCT、GROUPBY、CONNECTBY、STARTWITH、以及COUNT（）和SUM（）这样的聚合函数的视图。INSTEADOF触发器Oracle10g数据库管理课程CISSST实例讲解：建立一个emp表的副本：empcopy，建立一个dept表的副本deptcopyINSTEADOF触发器create table empcopy asselect*from emp;create table deptcopy asselect*from dept;alter table deptcopyadd(mgrno number(4),depttype number(4);Oracle10g数据库

14、管理课程CISSST实例讲解：INSTEADOF触发器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);Oracle

15、10g数据库管理课程CISSST实例讲解：INSTEADOF触发器 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);直接插值会报错！需要建立触发器！直接插值会报错！需要建立触发器！Oracle10g数据库管理课程CISSST实例讲解：INSTEADOF触发器 create or

16、 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;Oracle10g数据库管理课程CISSSTDML触发器都是在DML事件上被激活，而系统触发器可以在两种不同的系统事

17、件上被激活（DDL事件和数据库事件）。DDL事件包括：Create、Alter、Drop 等数据库事件包括：服务器的启动或者关闭，用户的登录或者退出等，以及服务器的错误等。创建系统触发器create or replace trigger trigger_namebefore|afterddl_event_list|database_event_liston database|on schemawhen clausetrigger_body;Oracle10g数据库管理课程CISSST创建系统触发器事件事件允许时机允许时机说说 明明启动启动之后之后实例启动时激发实例启动时激发关闭关闭之前之前实例

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

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

20、象的名称和类型 创建系统触发器Oracle10g数据库管理课程CISSST系统对于使用触发器要访问的表和列有一些限制。首先，需要了解几个基本的概念：约束表：由于引用完整性约束而需要从中读取数据的表。触发表：对于触发器而言，就是触发器为之定义的表。变异表：就是当前DML操作所影响的表。关于变异表的问题讨论Oracle10g数据库管理课程CISSST创建一个表registered_empcreate table registered_emp(empno number(5)not null,deptno number(2),constraint rs_deptno check(deptno in(1

21、0,20,30,40,50),constraint rs_deptno_fk foreign key(deptno)references dept(deptno),constraint rs_empno_fk foreign key(empno)references emp(empno)关于变异表的问题讨论Oracle10g数据库管理课程CISSST表registered_emp添加了两个外键，分别引用了EMP表的empno，和DEPT表的deptno。这样这样EMPEMP和和DEPTDEPT表就称为表就称为registered_empregistered_emp表的约束表。表的约束表。由于存

22、在引用完整性约束，如果对EMP表或者DEPT表做DML操作，那么registered_emp表在操作期间也是变异的。触发器体中的语句不能进行下述操作：1、读取或修改任何触发语句的变异表，这也包括触发表本身。2、读取或修改触发表中的约束表的主关键字，唯一关键字或外部关键字的列。除此之外的其它列可以修改。上述限制适用与所有的行级触发器，只有当DELETE CASCADE操作而激发语句触发器时，它们才适用于语句级触发器。关于变异表的问题讨论Oracle10g数据库管理课程CISSST问题：在在EMPEMP表中，每个部门的员工名额限制在表中，每个部门的员工名额限制在6 6个。需要通过对个。需要通过对E

23、MPEMP表进行表进行INSERTINSERT操作或者操作或者UPDATEUPDATE操作来实现。操作来实现。关于变异表的问题讨论Oracle10g数据库管理课程CISSST建立触发器关于变异表的问题讨论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 wh

24、ere deptno=:new.deptno;if(v_currentemp+1v_maxemp)then raise_application_error(-20000,员工数量超过名额限制);end if;end;Oracle10g数据库管理课程CISSST执行下述操作关于变异表的问题讨论SQL update emp set deptno=20 where empno=7782;update emp set deptno=20 where empno=7782ORA-04091:表表 SCOTT.EMP 发生了变化，触发器发生了变化，触发器/函数不能读函数不能读ORA-06512:在在SCO

25、TT.LIMITEMPLOYEES,line 5ORA-04088:触发器触发器 SCOTT.LIMITEMPLOYEES 执行过程中出错执行过程中出错由于触发器查询了自己的触发表，所以报了由于触发器查询了自己的触发表，所以报了ORA-4091错误。错误。另外，另外，ORA-4091错误是在触发器激活时触发的，而不是在创建是触发的。错误是在触发器激活时触发的，而不是在创建是触发的。Oracle10g数据库管理课程CISSST如何解决这个问题？分析：EMP表仅仅对于行级触发器是变异的，这意味着我们不能在行级触发器中对该表进行查询，然而在表级触发器中是可以的。是不是只要将刚是不是只要将刚才的行级触

26、发器改为表级触发器就可以了？才的行级触发器改为表级触发器就可以了？也不行！也不行！因为在触发器体内部需要根据部门编号判断是否该部门已经有6个人了，即需要在触发器体内通过:new.deptno引用DML语句中的部门编号，而表级触发器中不能使用:new.deptno。怎么办？怎么办？关于变异表的问题讨论Oracle10g数据库管理课程CISSST问题的解决：定义两个触发器，一个行级触发器，一个表级触发器。定义两个触发器，一个行级触发器，一个表级触发器。在行级触发器中，记录在行级触发器中，记录:new.deptno:new.deptno的值，但我们不查询的值，但我们不查询EMPEMP表，表，在表级触

27、发器中，通过记录的在表级触发器中，通过记录的deptnodeptno的值，进行查询统计，实施业务的值，进行查询统计，实施业务规则。规则。如果记录如果记录:new.deptno:new.deptno的值？可以使用包的内部的值？可以使用包的内部PL/SQLPL/SQL表来记录。这样，表来记录。这样，可以在每次更新时保存多个值，而且每个会话都得到自己的包变量的可以在每次更新时保存多个值，而且每个会话都得到自己的包变量的实例，因此，我们没有必要担心由于不同的会话同时进行的更新。实例，因此，我们没有必要担心由于不同的会话同时进行的更新。关于变异表的问题讨论Oracle10g数据库管理课程CISSST问题

28、的解决：关于变异表的问题讨论create or replace package EmpData is type 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;Oracle10g数据库管理课程CISSST问题的解决：关于变异表的问题讨论crea

29、te or replace trigger RLimitEmployeesbefore insert or 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;Oracle10g数据库管理课程CISSST问题的解决：关于变异表的问题讨论create or replac

30、e trigger SLimitEmployeesafter insert or update of deptno 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);sele

31、ct count(*)into v_currentEmp from empwhere deptno=v_deptno;if v_CurrentEmpv_MaxEmp thenraise_application_error(-20000,部门编号为部门编号为:|v_deptno|的部门员工数量超标的部门员工数量超标);end if;end loop;EmpData.v_numentries:=0;end;Oracle10g数据库管理课程CISSST对问题的再思考？1 1、由于、由于PL/SQLPL/SQL表是位于包中，所以这些表对于行级触发器和语句级触发器都表是位于包中，所以这些表对于行级触发器

32、和语句级触发器都是可见的，确保变量的全局性的唯一方法是把要定义的全局变量放在包中。是可见的，确保变量的全局性的唯一方法是把要定义的全局变量放在包中。2 2、在上述程序中使用了计数器变量、在上述程序中使用了计数器变量EmpData.v_NumEntriesEmpData.v_NumEntries，当其所在的包初，当其所在的包初次创建时，该变量被初始化为次创建时，该变量被初始化为0.0.然后，该变量的值由行级触发器修改，语句级然后，该变量的值由行级触发器修改，语句级触发器对该变量进行引用并在处理结束后将该变量复位为触发器对该变量进行引用并在处理结束后将该变量复位为0 0。这么做是很有必。这么做是很

33、有必要的，以便本次会话的下一个操作时要的，以便本次会话的下一个操作时UPDATEUPDATE语句才会具有正确的值。语句才会具有正确的值。3 3、可以使用数据库表而不使用、可以使用数据库表而不使用PL/SQLPL/SQL表。但是不推荐这种技术，因为发出表。但是不推荐这种技术，因为发出UPDATEUPDATE的同时进行的多个会话之间可能相互干扰。封装的同时进行的多个会话之间可能相互干扰。封装PL/SQLPL/SQL表在会话中是唯表在会话中是唯一的，因此它避免了这个问题。一的，因此它避免了这个问题。关于变异表的问题讨论Oracle10g数据库管理课程CISSSTalter trigger disab

34、le;这意味着数据库知道触发器存在，但是不会激活它。alter tirgger enable;激活触发器启用和禁用触发器Oracle10g数据库管理课程CISSST触发器可以采用PL/SQL编写，但是它不能够COMMIT或者ROLLBACK当前的事务处理。Oracle不允许在触发器中进行COMMIT和ROLLBACK。由于DDL语句要进行隐式COMMIT，所以它们也不允许在触发器中使用。事务处理和触发器Oracle10g数据库管理课程CISSSTdescuser_tirggers;selecttrigger_namefromuser_triggerswheretable_name=EMPLOY

35、EESandtable_owner=HR;selecttrigger_type,triggering_event,when_clause,trigger_bodyfromuser_triggerswheretrigger_name=SECURE_EMPLOYEES;从数据词典中获取触发器信息Oracle10g数据库管理课程CISSST1、触发器名称2、触发器体中不能发出任何的事务控制语句，如：commit、rollback等。同时触发器调用的任何过程或者函数中不能包含有事务控制语句。触发器体不能声明任何LONG或者LONG RAW类型变量。而且:new和:old也不能指向为之定义的表的LONG

36、或者LONG RAW类型的列。触发器体可以引用和使用LOB列，但是不能修改该列的值。这个限制也适用于对象列。3、触发器体:触发器体必须是PL/SQL语句，可以包含CALL语句，被调用的过程可以是PL/SQL存储过程，也可以是C语言或者JAVA例程的包装器。触发器的限制Oracle10g数据库管理课程CISSST4、触发器权限：触发器所有者必须对触发器所引用的对象具有必要的对象权限。由于触发器是已经编译的对象，因此这些权限都必须直接授予，不能通过中间角色给予。与触发器相关的系统权限-CREATE TRIGGER-CREATE ANY TRIGGER-ALTER ANY TRIGGER-DROP

37、ANY TRIGGER-ADMINISTER DATABASE TRIGGER触发器的限制Oracle10g数据库管理课程CISSST触发器小结触发器可以用于很多目的：它们是一种非常优秀地控制用户表中数据输入和维护的方式，可以让数据建模者确保输入的数据被正确格式化。它们可以让我们将业务规则直接与我们的数据进行绑定，阻止用户规避这些规则。它们能够作为安全特性。可以对它们进行编码以确保只有特定用户才能够修改用户数据。它们可以帮助我们对我们的数据所进行的操作，操作发生时间，以及在数据库或者特定模式的对象上进行处理的时间进行日志记录。它们能够用于指导Oracle如何控制复杂的用户定义数据类型。Oracle10g数据库管理课程