Oracle_PLSQL_编程语法详解-触发器

-65-65第八章触发器触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。§8.1触发器类型触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。§8.1.1DML触发器ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。§8.1.2替代触发器由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE8专门为进行视图操作的一种处理方法。§8.1.3系统触发器ORACLE8i提供了第三种类型的触发器叫系统触发器。它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。触发器组成:触发事件：即在何种情况下触发TRIGGER;例如：INSERT,UPDATE,DELETE。触发时间：即该TRIGGER是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER的操作顺序。触发器本身：即该TRIGGER被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL块。触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。-66-66§8.2创建触发器创建触发器的一般语法是:CREATE[ORREPLACE]TRIGGERtrigger_name{BEFORE|AFTER}{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}ON[schema.]table_name[REFERENCING{OLD[AS]old|NEW[AS]new|PARENTasparent}][FOREACHROW][WHENcondition]trigger_body;其中：BEFORE和AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。FOREACHROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOREACHROW选项时，BEFORE和AFTER触发器为语句触发器，而INSTEADOF触发器则为行触发器。REFERENCING子句说明相关名称，在行触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。WHEN子句说明触发约束条件。Condition为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL函数。WHEN子句指定的触发约束条件只能用在BEFORE和AFTER行触发器中，不能用在INSTEADOF行触发器和其它类型的触发器中。当一个基表被修改(INSERT,UPDATE,DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。每张表最多可建立12种类型的触发器，它们是:BEFOREINSERTBEFOREINSERTFOREACHROWAFTERINSERTAFTERINSERTFOREACHROWBEFOREUPDATEBEFOREUPDATEFOREACHROW-67-67AFTERUPDATEAFTERUPDATEFOREACHROWBEFOREDELETEBEFOREDELETEFOREACHROWAFTERDELETEAFTERDELETEFOREACHROW§8.2.1触发器触发次序1.执行BEFORE语句级触发器;2.对与受语句影响的每一行：执行BEFORE行级触发器执行DML语句执行AFTER行级触发器3.执行AFTER语句级触发器§8.2.2创建DML触发器触发器名与过程名和包的名字不一样，它是单独的名字空间，因而触发器名可以和表或过程有相同的名字，但在一个模式中触发器名不能相同。触发器的限制CREATETRIGGER语句文本的字符长度不能超过32KB；触发器体内的SELECT语句只能为SELECT…INTO…结构，或者为定义游标所使用的SELECT语句。触发器中不能使用数据库事务控制语句COMMIT;ROLLBACK,SVAEPOINT语句；由触发器所调用的过程或函数也不能使用数据库事务控制语句；触发器中不能使用LONG,LONGRAW类型；触发器内可以参照LOB类型列的列值，但不能通过:NEW修改LOB列中的数据；问题：当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值.实现::NEW修饰符访问操作完成后列的值:OLD修饰符访问操作完成前列的值特性INSERTUPDATEDELETEOLDNULL有效有效NEW有效有效NULL例1:建立一个触发器,当职工表emp表被删除一条记录时，把被删除记录写到职工表删除日志表中去。CREATETABLEemp_hisASSELECT*FROMEMPWHERE1=2;CREATEORREPLACETRIGGERdel_emp-68-68BEFOREDELETEONscott.empFOREACHROWBEGIN--将修改前数据插入到日志记录表del_emp,以供监督使用。INSERTINTOemp_his(deptno,empno,ename,job,mgr,sal,comm,hiredate)VALUES(:old.deptno,:old.empno,:old.ename,:old.job,:old.mgr,:old.sal,:old.comm,:old.hiredate);END;DELETEempWHEREempno=7788;DROPTABLEemp_his;DROPTRIGGERdel_emp;§8.2.3创建替代(INSTEADOF)触发器创建触发器的一般语法是:CREATE[ORREPLACE]TRIGGERtrigger_nameINSTEADOF{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}ON[schema.]view_name[REFERENCING{OLD[AS]old|NEW[AS]new|PARENTasparent}][FOREACHROW][WHENcondition]trigger_body;其中：BEFORE和AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。INSTEADOF选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEADOF触发器，而不能对表、模式和数据库建立INSTEADOF触发器。FOREACHROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOREACHROW选项时，BEFORE和AFTER触发器为语句触发器，而INSTEADOF触发器则为行触发器。REFERENCING子句说明相关名称，在行触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。WHEN子句说明触发约束条件。Condition为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL函数。WHEN子句指定的触发约束条件只能用在BEFORE和AFTER行触发器中，不能用在INSTEADOF行触发器和其它类型的-69-69触发器中。INSTEAD_OF用于对视图的DML触发，由于视图有可能是由多个表进行联结(join)而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新，例如下面情况：CREATEORREPLACEVIEWemp_viewASSELECTdeptno,count(*)total_employeer,sum(sal)total_salaryFROMempGROUPBYdeptno;在此视图中直接删除是非法：SQLDELETEFROMemp_viewWHEREdeptno=10;DELETEFROMemp_viewWHEREdeptno=10*ERROR位于第1行:ORA-01732:此视图的数据操纵操作非法但是我们可以创建INSTEAD_OF触发器来为DELETE操作执行所需的处理，即删除EMP表中所有基准行：CREATEORREPLACETRIGGERemp_view_deleteINSTEADOFDELETEONemp_viewFOREACHROWBEGINDELETEFROMempWHEREdeptno=:old.deptno;ENDemp_view_delete;DELETEFROMemp_viewWHEREdeptno=10;DROPTRIGGERemp_view_delete;DROPVIEWemp_view;§8.2.3创建系统事件触发器ORACLE8i提供的系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE、ALTER及DROP等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。创建系统触发器的语法如下：创建触发器的一般语法是:CREATEORREPLACETRIGGER[sachema.]trigger_name{BEFORE|AFTER}{ddl_event_list|database_event_list}ON{DATABASE|[schema.]SCHEMA}[WHEN_clause]trigger_body;-70-70其中:ddl_event_list：一个或多个DDL事件，事件间用OR分开；database_event_list：一个或多个数据库事件，事件间用OR分开；系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器,默认时为当前用户模式。当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTERDATABASETRIGGER权限。下面给出系统触发器的种类和事件出现的时机（前或后）：事件允许的时机说明启动STARTUP之后实例启动时激活关闭SHUTDOWN之前实例正常关闭时激活服务器错误SERV