Orcale触发器详解.docx

Orcale触发器详解.docx

《Orcale触发器详解.docx》由会员分享，可在线阅读，更多相关《Orcale触发器详解.docx（32页珍藏版）》请在冰点文库上搜索。

Orcale触发器详解

ORACLEPL/SQL编程之八：

把触发器说透

本篇主要内容如下：

8.1触发器类型

8.1.1DML触发器

8.1.2替代触发器

8.1.3系统触发器

8.2创建触发器

8.2.1触发器触发次序

8.2.2创建DML触发器

8.2.3创建替代（INSTEADOF）触发器

8.2.3创建系统事件触发器

8.2.4系统触发器事件属性

8.2.5使用触发器谓词

8.2.6重新编译触发器

8.3删除和使能触发器

8.4触发器和数据字典

8.5数据库触发器的应用举例

触发器是许多关系数据库系统都提供的一项技术。

在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系统的启动与关闭等。

触发器组成:

l触发事件：

引起触发器被触发的事件。

例如：

DML语句（INSERT,UPDATE,DELETE语句对表或视图执行数据处理操作）、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

l触发时间：

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

l触发操作：

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

例如：

PL/SQL块。

l触发对象：

包括表、视图、模式、数据库。

只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

l触发条件：

由WHEN子句指定一个逻辑表达式。

只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

l触发频率：

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

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

语句级（STATEMENT）触发器：

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

行级（ROW）触发器：

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

编写触发器时，需要注意以下几点：

l触发器不接受参数。

l一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。

并各触发器之间不能有矛盾。

l在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

l触发器最大为32KB。

若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

l在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

l触发器中不能包含事务控制语句（COMMIT，ROLLBACK，SAVEPOINT）。

因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

l在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

l在触发器主体中不能申明任何Long和blob变量。

新值new和旧值old也不能是表中的任何long和blob列。

l不同类型的触发器（如DML触发器、INSTEADOF触发器、系统触发器）的语法格式和作用有较大区别。

8.2创建触发器

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

CREATE[ORREPLACE]TRIGGERtrigger_name

{BEFORE|AFTER}

{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}[OR{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}...]ON[schema.]table_name|[schema.]view_name[REFERENCING{OLD[AS]old|NEW[AS]new|PARENTasparent}][FOREACHROW][WHENcondition]

PL/SQL_BLOCK|CALLprocedure_name;

其中：

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

8.2.1触发器触发次序

1.执行BEFORE语句级触发器;

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

l执行BEFORE行级触发器

l执行DML语句

l执行AFTER行级触发器

3.执行AFTER语句级触发器

8.2.2创建DML触发器

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

DML触发器的限制

lCREATETRIGGER语句文本的字符长度不能超过32KB；

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

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

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

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

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

NEW修改LOB列中的数据；

DML触发器基本要点

l触发时机：

指定触发器的触发时间。

如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。

l触发事件：

引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。

既可以是单个触发事件，也可以是多个触发事件的组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。

l条件谓词：

当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的如下条件谓词。

1）。

INSERTING：

当触发事件是INSERT时，取值为TRUE，否则为FALSE。

2）。

UPDATING[（column_1,column_2,…,column_x）]：

当触发事件是UPDATE时，如果修改了column_x列，则取值为TRUE，否则为FALSE。

其中column_x是可选的。

3）。

DELETING：

当触发事件是DELETE时，则取值为TRUE，否则为FALSE。

解发对象：

指定触发器是创建在哪个表、视图上。

l触发类型：

是语句级还是行级触发器。

l触发条件：

由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定UPDATING后面的列的列表。

问题：

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

实现:

:

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

:

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

特性

INSERT

UPDATE

DELETE

OLD

NULL

实际值

实际值

NEW

实际值

实际值

NULL

例1:

建立一个触发器,当职工表emp表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATETABLEemp_hisASSELECT*FROMEMPWHERE1=2;CREATEORREPLACETRIGGERtr_del_emp

BEFOREDELETE--指定触发时机为删除操作前触发ONscott.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;DELETEempWHEREempno=7788;DROPTABLEemp_his;DROPTRIGGERdel_emp;

例2：

限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表。

CREATEORREPLACETRIGGERtr_dept_time

BEFOREINSERTORDELETEORUPDATEONdepartmentsBEGINIF（TO_CHAR（sysdate,'DAY'）IN（'星期六','星期日'））OR（TO_CHAR（sysdate,'HH24:

MI'）NOTBETWEEN'08:

30'AND'18:

00'）THEN

RAISE_APPLICATION_ERROR（-20001,'不是上班时间，不能修改departments表'）;ENDIF;END;

例3：

限定只对部门号为80的记录进行行触发器操作。

CREATEORREPLACETRIGGERtr_emp_sal_comm

BEFOREUPDATEOFsalary,commission_pctORDELETEONHR.employeesFOREACHROWWHEN（old.department_id=80）BEGINCASEWHENUPDATING（'salary'）THENIF:

NEW.salary<:

old.salaryTHEN

RAISE_APPLICATION_ERROR（-20001,'部门80的人员的工资不能降'）;ENDIF;WHENUPDATING（'commission_pct'）THENIF:

NEW.commission_pct<:

mission_pctTHEN

RAISE_APPLICATION_ERROR（-20002,'部门80的人员的奖金不能降'）;ENDIF;WHENDELETINGTHEN

RAISE_APPLICATION_ERROR（-20003,'不能删除部门80的人员记录'）;ENDCASE;END;/*实例：

UPDATEemployeesSETsalary=8000WHEREemployee_id=177;

DELETEFROMemployeesWHEREemployee_idin（177,170）;

*/

例4：

利用行触发器实现级联更新。

在修改了主表regions中的region_id之后（AFTER），级联的、自动的更新子表countries表中原来在该地区的国家的region_id。

CREATEORREPLACETRIGGERtr_reg_cou

AFTERupdateOFregion_idONregionsFOREACHROWBEGIN

DBMS_OUTPUT.PUT_LINE（'旧的region_id值是'||:

old.region_id||'、新的region_id值是'||:

new.region_id）;UPDATEcountriesSETregion_id=:

new.region_idWHEREregion_id=:

old.region_id;END;

例5：

在触发器中调用过程。

CREATEORREPLACEPROCEDUREadd_job_history

（p_emp_idjob_history.employee_id%type

p_start_datejob_history.start_date%type

p_end_datejob_history.end_date%type

p_job_idjob_history.job_id%type

p_department_idjob_history.department_id%type

）ISBEGININSERTINTOjob_history（employee_id,start_date,end_date,

job_id,department_id）VALUES（p_emp_id,p_start_date,p_end_date,p_job_id,p_department_id）;ENDadd_job_history;--创建触发器调用存储过程...CREATEORREPLACETRIGGERupdate_job_history

AFTERUPDATEOFjob_id,department_idONemployeesFOREACHROWBEGIN

add_job_history（:

old.employee_id,:

old.hire_date,sysdate,

:

old.job_id,:

old.department_id）;END;

8.2.3创建替代（INSTEADOF）触发器

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

CREATE[ORREPLACE]TRIGGERtrigger_name

INSTEADOF

{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}[OR{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}...]ON[schema.]view_name--只能定义在视图上[REFERENCING{OLD[AS]old|NEW[AS]new|PARENTasparent}][FOREACHROW]--因为INSTEADOF触发器只能在行级上触发,所以没有必要指定[WHENcondition]

PL/SQL_block|CALLprocedure_name;

其中：

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行触发器和其它类型的触发器中。

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

但可以按照所需的方式执行更新，例如下面情况：

例1：

CREATEORREPLACEVIEWemp_viewASSELECTdeptno,count（*）total_employeer,sum（sal）total_salaryFROMempGROUPBYdeptno;

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

SQL>DELETEFROMemp_viewWHEREdeptno=10;DELETEFROMemp_viewWHEREdeptno=10

ERROR位于第1行:

ORA-01732:

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

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

CREATEORREPLACETRIGGERemp_view_delete

INSTEADOFDELETEONemp_viewFOREACHROWBEGINDELETEFROMempWHEREdeptno=:

old.deptno;ENDemp_view_delete;DELETEFROMemp_viewWHEREdeptno=10;DROPTRIGGERemp_view_delete;DROPVIEWemp_view;

例2：

创建复杂视图，针对INSERT操作创建INSTEADOF触发器，向复杂视图插入数据。

l创建视图：

CREATEORREPLACEFORCEVIEW"HR"."V_REG_COU"（"R_ID","R_NAME","C_ID","C_NAME"）ASSELECTr.region_id,

r.region_name,

c.country_id,

c.country_nameFROMregionsr,

countriescWHEREr.region_id=c.region_id;

l创建触发器：

CREATEORREPLACETRIGGER"HR"."TR_I_O_REG_COU"INSTEADOFINSERTONv_reg_couFOREACHROWDECLAREv_countNUMBER;BEGINSELECTCOUNT（*）INTOv_countFROMregionsWHEREregion_id=:

new.r_id;IFv_count=0THENINSERTINTOregions

（region_id,region_name

）VALUES

（:

new.r_id,:

new.r_name

）;ENDIF;SELECTCOUNT（*）INTOv_countFROMcountriesWHEREcountry_id=:

new.c_id;IFv_count=0THENINSERTINTOcountries

（

country_id,

country_name,

region_id

）VALUES

（

:

new.c_id,

:

new.c_name,

:

new.r_id

）;ENDIF;END;

创建INSTEADOF触发器需要注意以下几点：

l只能被创建在视图上，并且该视图没有指定WITHCHECKOPTION选项。

l不能指定BEFORE或AFTER选项。

lFOREACHROW子可是可选的，即INSTEADOF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。

l没有必要在针对一个表的视图上创建INSTEADOF触发器，只要创建DML触发器就可以了。

8.2.3创建系统事件触发器

ORACLE10G提供的系统事件触发器可以在DDL或数据库系统上被触发。

DDL指的是数据定义语言