PLSQL触发器分类.docx

资源描述

PLSQL触发器分类.docx

《PLSQL触发器分类.docx》由会员分享，可在线阅读，更多相关《PLSQL触发器分类.docx（15页珍藏版）》请在冰豆网上搜索。

PLSQL触发器分类.docx

PLSQL触发器分类

函数（function）可以在查询语句中直接使用

存储过程（procedure）无返回值，通过输出参数返回。

必须单独调用

Inout会用到中间变量，中间值不断变化会输入和输出

触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。

即触发器是当某个事件发生时自动地隐式运行。

并且，触发器不能接收参数。

所以运行触发器就叫触发或点火（firing）。

ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。

ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。

主要的触发器种类有三种：

ØDML触发器：

ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发

Ø替代触发器：

由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。

所以给出了替代触发器。

它就是ORACLE专门为进行视图操作的一种处理方法。

Ø系统触发器

它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。

触发器的组成主要有：

Ø触发事件：

即在何种情况下触发TRIGGER;例如：

INSERT,UPDATE,DELETE。

Ø触发时间：

即该TRIGGER是在触发事件发生之前（BEFORE）还是之后（AFTER）触发，也就是触发事件和该TRIGGER的操作顺序。

Ø触发频率：

说明触发器内定义的动作被执行的次数。

即语句级（STATEMENT）触发器和行级（ROW）触发器。

语句级（STATEMENT）触发器：

是指当某触发事件发生时，该触发器只执行一次；

行级（ROW）触发器：

是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

Ø触发器本身：

即该TRIGGER被触发之后的目的和意图，正是触发器本身要做的事情。

例如：

PL/SQL块。

创建触发器

创建触发器的一般语法是：

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种触发器：

BEFOREINSERT

BEFOREINSERTFOREACHROW

AFTERINSERT

AFTERINSERTFOREACHROW

BEFOREUPDATE

BEFOREUPDATEFOREACHROW

AFTERUPDATE

AFTERUPDATEFOREACHROW

BEFOREDELETE

BEFOREDELETEFOREACHROW

AFTERDELETE

AFTERDELETEFOREACHROW

触发器执行顺序为：

Ø执行BEFORE语句级触发器

Ø对与受语句影响的每一行：

✓执行BEFORE行级触发器

✓执行DML语句

✓执行AFTER行级触发器

Ø执行AFTER语句级触发器

创建DML触发器

触发器名与过程名和包的名字不一样，它是单独的名字空间，因而触发器名可以和表或过程有相同的名字，但在一个模式中触发器名不能相同

例：

CREATEORREPLACETRIGGERdel_emp

BEFOREDELETEONscott.empFOREACHROW

BEGIN

--将修改前数据插入到日志记录表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,:

m,:

old.hiredate）;

END;

触发器的限制

ØCREATETRIGGER语句文本的字符长度不能超过32KB

Ø触发器体内的SELECT语句只能为SELECT…INTO…结构，或者为定义游标所使用的SELECT语句

Ø触发器中不能使用数据库事务控制语句COMMIT，ROLLBACK,SVAEPOINT语句

Ø由触发器所调用的过程或函数也不能使用数据库事务控制语句

Ø触发器中不能使用LONG,LONGRAW类型

Ø触发器内可以参照LOB类型列的列值，但不能通过:

NEW修改LOB列中的数据

行级别触发器中的相关标识符

当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值，可以使用：

NEW修饰符访问操作完成后列的值

OLD修饰符访问操作完成前列的值

特性

INSERT

UPDATE

DELETE

OLD

NULL

有效

NEW

有效

NULL

创建DML触发器

CREATEORREPLACETRIGGERdel_emp

BEFOREupdateONscott.emp

REFERENCINGnewASnnoldASoo

FOREACHROW

WHEN（nn.sal>2000）

BEGIN

dbms_output.put_line（:

nn.sal||'------'||:

oo.sal）;

END;

DML触发器中的谓词

在DML触发器中，他们被不同的DML语句所触发，有三个布尔型函数来确定操作到底是什么：

谓词

描述

INSERTING

如果触发语句是INSERT，则为TRUE，否则为FALSE

UPDATING

如果触发语句是UPDATE，则为TRUE，否则为FALSE

DELETING

如果触发语句是DELETE，则为TRUE，否则为FALSE

CREATEORREPLACETRIGGERcheck_emp

BEFOREupdateORinsertORdeleteONscott.emp

REFERENCINGnewASnnoldASoo

FOREACHROW

WHEN（nn.sal>2000）

BEGIN

IFINSERTINGTHEN

dbms_output.put_line（'THEOPERATIONISINSERT'）;

ELSIFUPDATINGTHEN

dbms_output.put_line（'THEOPERATIONISUPDATE'）;

ELSIFDELETINGTHEN

dbms_output.put_line（'THEOPERATIONISDELETE'）;

ELSE

dbms_output.put_line（'OTHERSOPERATION'）;

ENDIF;

END;

创建替代触发器

创建触发器的一般语法是:

CREATE[ORREPLACE]TRIGGERtrigger_name

INSTEADOF

{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}

ON[schema.]view_name

[REFERENCING{OLD[AS]old|NEW[AS]new|PARENTasparent}]

[FOREACHROW]

[WHENcondition]

trigger_body;

其中：

INSTEADOF选项使ORACLE激活触发器，而不执行触发事件。

只能对视图和对象视图建立INSTEADOF触发器，而不能对表、模式和数据库建立INSTEADOF触发器。

FOREACHROW选项说明触发器为行触发器。

行触发器和语句触发器的区别表现在：

当省略FOREACHROW选项时，BEFORE和AFTER触发器为语句触发器，而INSTEADOF触发器则为行触发器。

REFERENCING子句说明相关名称，在行触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。

触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号（:

），但在WHEN子句中则不能加冒号。

WHEN子句说明触发约束条件。

Condition为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL函数。

WHEN子句指定的触发约束条件只能用在BEFORE和AFTER行触发器中，不能用在INSTEADOF行触发器和其它类型的触发器中。

INSTEAD_OF用于对视图的DML触发，由于视图有可能是由多个表进行联结（join）而成，因而并非是所有的联结都是可更新的。

但可以按照所需的方式执行更新

CREATEORREPLACEVIEWemp_viewAS

SELECTdeptno,count（*）total_employeer,sum（sal）total_salary

FROMempGROUPBYdeptno;

在此视图中直接删除是非法：

SQL>DELETEFROMemp_viewWHEREdeptno=10;

DELETEFROMemp_viewWHEREdeptno=10

ERROR位于第1行:

ORA-01732:

此视图的数据操纵操作非法

但是我们可以创建INSTEAD_OF触发器来为DELETE操作执行所需的处理，即删除EMP表中所有基准行：

CREATEORREPLACETRIGGERemp_view_delete

INSTEADOFDELETEONemp_viewFOREACHROW

BEGIN

DELETEFROMempWHEREdeptno=:

old.deptno;

ENDemp_view_delete;

DELETEFROMemp_viewWHEREdeptno=10;

创建系统触发器

系统触发器可以在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;

其中:

ddl_event_list为一个或多个DDL事件，事件间用OR分开；

database_event_list为一个或多个数据库事件，事件间用OR分开；

注意:

系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。

当建立在模式（SCHEMA）之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器,默认时为当前用户模式。

当建立在数据库（DATABASE）之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。

要在数据库之上建立触发器时，要求用户具有ADMINISTERDATABASETRIGGER权限。

系统触发器的种类和事件出现的时机（前或后）：

事件

允许的时机

说明

启动STARTUP

之后

实例启动时激活

关闭SHUTDOWN

之前

实例正常关闭时激活

服务器错误SERVERERROR

之后

只要有错误就激活

登录LOGON

之后

成功登录后激活

注销LOGOFF

之前

开始注销时激活

创建CREATE

之前，之后

在创建之前或之后激活

撤消DROP

之前，之后

在撤消之前或之后激活

变更ALTER

之前，之后

在变更之前或之后激活

创建系统触发器应用举例

CREATEORREPLACETRIGGERtrig4_ddl

AFTERCREATEORALTERORDROPONDATABASE

DECLARE

EventVARCHAR2（20）;TypVARCHAR2（20）;

NameVARCHAR2（30）;OwnerVARCHAR2（30）;

BEGIN

--读取DDL事件属性

Event:

=SYSEVENT;

Typ:

=DICTIONARY_OBJ_TYPE;

Name:

=DICTIONARY_OBJ_NAME;

Owner:

=DICTIONARY_OBJ_OWNER;

--将事件属性插入到事件日志表中

INSERTINTOscott.eventlog（eventname,obj_type,obj_name,obj_owner）

VALUES（event,typ,name,owner）;

END;

使用触发器谓词

ORACLE提供三个参数INSERTING,UPDATING,DELETING用于判断触发了哪些操作。

谓词

行为

INSERTING

如果触发语句是INSERT语句，则为TRUE,否则为FALSE

UPDATING

如果触发语句是UPDATE语句，则为TRUE,否则为FALSE

DELETING

如果触发语句是DELETE语句，则为TRUE,否则为FALSE

重新编译触发器

如果在触发器内调用其它函数或过程，当这些函数或过程被删除或修改后，触发器的状态将被标识为无效。

当DML语句激活一个无效触发器时，ORACLE将重新编译触发器代码，如果编译时发现错误，这将导致DML语句执行失败。

删除触发器

删除触发器使用下面的语句：

DROPTRIGGERtrigger_name;

当删除其他用户模式中的触发器名称，需要具有DROPANYTRIGGER系统权限，当删除建立在数据库上的触发器时，用户需要具有ADMINISTERDATABASETRIGGER系统权限。

此外，当删除表或视图时，建立在这些对象上的触发器也随之删除

触发器状态

Ø有效状态（ENABLE）：

当触发事件发生时，处于有效状态的数据库触发器TRIGGER将被触发。

Ø无效状态（DISABLE）：

当触发事件发生时，处于无效状态的数据库触发器TRIGGER将不会被触发，此时就跟没有这个数据库触发器（TRIGGER）一样。

数据库TRIGGER的这两种状态可以互相转换。

格式为：

ALTERTIGGERtrigger_name[DISABLE|ENABLE];

例：

ALTERTRIGGERemp_view_deleteDISABLE;

ALTERTRIGGER语句一次只能改变一个触发器的状态，而ALTERTABLE语句则一次能够改变与指定表相关的所有触发器的使用状态。

格式为：

ALTERTABLE[schema.]table_name{ENABLE|DISABLE}ALL

使表EMP上的所有TRIGGER失效：

ALTERTABLEempDISABLEALLTRIGGERS;

触发器和数据字典