Orcale触发器详解解读Word文档下载推荐.docx

Orcale触发器详解解读Word文档下载推荐.docx

《Orcale触发器详解解读Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《Orcale触发器详解解读Word文档下载推荐.docx（25页珍藏版）》请在冰豆网上搜索。

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

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触发器、系统触发器）的语法格式和作用有较大区别。

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

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

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

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

l执行BEFORE行级触发器

l执行DML语句

l执行AFTER行级触发器

3.执行AFTER语句级触发器

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

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操作之后触发，以便记录该操作或做某些事后处理。

引起触发器被触发的事件，即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触发类型：

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

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

问题：

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

实现:

:

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

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

特性

INSERT

UPDATE

DELETE

OLD

NULL

实际值

NEW

例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;

例3：

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

CREATEORREPLACETRIGGERtr_emp_sal_comm

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

salary'

）THENIF:

NEW.salary<

old.salaryTHEN

部门80的人员的工资不能降'

ENDIF;

WHENUPDATING（'

commission_pct'

NEW.commission_pct<

mission_pctTHEN

RAISE_APPLICATION_ERROR（-20002,'

部门80的人员的奖金不能降'

WHENDELETINGTHEN

RAISE_APPLICATION_ERROR（-20003,'

不能删除部门80的人员记录'

ENDCASE;

/*实例：

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;

例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）;

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触发器。

行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；

当省略FOREACHROW选项时，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;

创建复杂视图，针对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"

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

）;

SELECTCOUNT（*）INTOv_countFROMcountriesWHEREcountry_id=:

new.c_id;

IFv_count=0THENINSERTINTOcountries

（

country_id,

country_name,

region_id

new.c_id,

new.c_name,

new.r_id

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

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

l不能指定BEFORE或AFTER选项。

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

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

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

DDL指的是数据定义