ORACLE TRIGGER全面讲解文档格式.docx
- 文档编号:17215605
- 上传时间:2022-11-29
- 格式:DOCX
- 页数:29
- 大小:33.21KB
ORACLE TRIGGER全面讲解文档格式.docx
《ORACLE TRIGGER全面讲解文档格式.docx》由会员分享,可在线阅读,更多相关《ORACLE TRIGGER全面讲解文档格式.docx(29页珍藏版)》请在冰豆网上搜索。
●触发事件:
引起触发器被触发的事件。
例如:
DML语句(INSERT,UPDATE,DELETE语句对表或视图执行数据处理操作)、DDL语句(如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象)、数据库系统事件(如系统启动或退出、异常错误)、用户事件(如登录或退出数据库)。
●触发时间:
即该TRIGGER是在触发事件发生之前(BEFORE)还是之后(AFTER)触发,也就是触发事件和该TRIGGER的操作顺序。
●触发操作:
即该TRIGGER被触发之后的目的和意图,正是触发器本身要做的事情。
PL/SQL块。
●触发对象:
包括表、视图、模式、数据库。
只有在这些对象上发生了符合触发条件的触发事件,才会执行触发操作。
●触发条件:
由WHEN子句指定一个逻辑表达式。
只有当该表达式的值为TRUE时,遇到触发事件才会自动执行触发器,使其执行触发操作。
●触发频率:
说明触发器内定义的动作被执行的次数。
即语句级(STATEMENT)触发器和行级(ROW)触发器。
语句级(STATEMENT)触发器:
是指当某触发事件发生时,该触发器只执行一次;
行级(ROW)触发器:
是指当某触发事件发生时,对受到该操作影响的每一行数据,触发器都单独执行一次。
编写触发器时,需要注意以下几点:
●触发器不接受参数。
●一个表上最多可有12个触发器,但同一时间、同一事件、同一类型的触发器只能有一个。
并各触发器之间不能有矛盾。
●在一个表上的触发器越多,对在该表上的DML操作的性能影响就越大。
●触发器最大为32KB。
若确实需要,可以先建立过程,然后在触发器中用CALL语句进行调用。
●在触发器的执行部分只能用DML语句(SELECT、INSERT、UPDATE、DELETE),不能使用DDL语句(CREATE、ALTER、DROP)。
●触发器中不能包含事务控制语句(COMMIT,ROLLBACK,SAVEPOINT)。
因为触发器是触发语句的一部分,触发语句被提交、回退时,触发器也被提交、回退了。
●在触发器主体中调用的任何过程、函数,都不能使用事务控制语句。
●在触发器主体中不能申明任何Long和blob变量。
新值new和旧值old也不能向表中的任何long和blob列。
●不同类型的触发器(如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.对与受语句影响的每一行:
●执行BEFORE行级触发器
●执行DML语句
●执行AFTER行级触发器
3.执行AFTER语句级触发器
触发器名与过程名和包的名字不一样,它是单独的名字空间,因而触发器名可以和表或过程有相同的名字,但在一个模式中触发器名不能相同。
DML触发器的限制
●CREATETRIGGER语句文本的字符长度不能超过32KB;
●触发器体内的SELECT语句只能为SELECT…INTO…结构,或者为定义游标所使用的SELECT语句。
●触发器中不能使用数据库事务控制语句COMMIT;
ROLLBACK,SVAEPOINT语句;
●由触发器所调用的过程或函数也不能使用数据库事务控制语句;
●触发器中不能使用LONG,LONGRAW类型;
●触发器内可以参照LOB类型列的列值,但不能通过:
NEW修改LOB列中的数据;
DML触发器基本要点
●触发时机:
指定触发器的触发时间。
如果指定为BEFORE,则表示在执行DML操作之前触发,以便防止某些错误操作发生或实现某些业务规则;
如果指定为AFTER,则表示在执行DML操作之后触发,以便记录该操作或做某些事后处理。
引起触发器被触发的事件,即DML操作(INSERT、UPDATE、DELETE)。
既可以是单个触发事件,也可以是多个触发事件的组合(只能使用OR逻辑组合,不能使用AND逻辑组合)。
●条件谓词:
当在触发器中包含多个触发事件(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。
解发对象:
指定触发器是创建在哪个表、视图上。
●触发类型:
是语句级还是行级触发器。
由WHEN子句指定一个逻辑表达式,只允许在行级触发器上指定触发条件,指定UPDATING后面的列的列表。
问题:
当触发器被触发时,要使用被插入、更新或删除的记录中的列值,有时要使用操作前、后列的值.
实现:
:
NEW修饰符访问操作完成后列的值
:
OLD修饰符访问操作完成前列的值
特性
INSERT
UPDATE
DELETE
OLD
NULL
实际值
NEW
例1:
建立一个触发器,当职工表emp表被删除一条记录时,把被删除记录写到职工表删除日志表中去。
CREATETABLEemp_hisASSELECT*FROMEMPWHERE1=2;
CREATEORREPLACETRIGGERtr_del_emp
BEFOREDELETE--指定触发时机为删除操作前触发
ONscott.emp
FOREACHROW--说明创建的是行级触发器
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
BEFOREINSERTORDELETEORUPDATE
ONdepartments
IF(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_pct
ORDELETE
ONHR.employees
FOREACHROW
WHEN(old.department_id=80)
CASE
WHENUPDATING('
salary'
IF:
NEW.salary<
old.salaryTHEN
部门80的人员的工资不能降'
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_id
ONregions
DBMS_OUTPUT.PUT_LINE('
旧的region_id值是'
||:
old.region_id
||'
、新的region_id值是'
new.region_id);
UPDATEcountriesSETregion_id=:
new.region_id
WHEREregion_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
)
IS
INSERTINTOjob_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_idONemployees
add_job_history(:
old.employee_id,:
old.hire_date,sysdate,
old.job_id,:
old.department_id);
INSTEADOF
ON[schema.]view_name--只能定义在视图上
[FOREACHROW]--因为INSTEADOF触发器只能在行级上触发,所以没有必要指定
PL/SQL_block|CALLprocedure_name;
INSTEADOF选项使ORACLE激活触发器,而不执行触发事件。
只能对视图和对象视图建立INSTEADOF触发器,而不能对表、模式和数据库建立INSTEADOF触发器。
当省略FOREACHROW选项时,BEFORE和AFTER触发器为语句触发器,而INSTEADOF触发器则为行触发器。
INSTEAD_OF用于对视图的DML触发,由于视图有可能是由多个表进行联结(join)而成,因而并非是所有的联结都是可更新的。
但可以按照所需的方式执行更新,例如下面情况:
例1:
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
DELETEFROMempWHEREdeptno=:
old.deptno;
ENDemp_view_delete;
DROPTRIGGERemp_view_delete;
DROPVIEWemp_view;
创建复杂视图,针对INSERT操作创建INSTEADOF触发器,向复杂视图插入数据。
●创建视图:
CREATEORREPLACEFORCEVIEW"
HR"
."
V_REG_COU"
("
R_ID"
"
R_NAME"
C_ID"
C_NAME"
AS
SELECTr.region_id,
r.region_name,
c.country_id,
c.country_name
FROMregionsr,
countriesc
WHEREr.region_id=c.region_id;
●创建触发器:
CREATEORREPLACETRIGGER"
TR_I_O_REG_COU"
INSTEADOF
INSERTONv_reg_couFOREACHROWDECLAREv_countNUMBER;
SELECTCOUNT(*)INTOv_countFROMregionsWHEREregion_id=:
new.r_id;
IFv_count=0THEN
INSERTINTOregions
(region_id,region_name
)VALUES
(:
new.r_id,:
new.r_name
SELECTCOUNT(*)INTOv_countFROMcountriesWHEREcountry_id=:
new.c_id;
INTOcountries
(
country_id,
country_name,
region_id
VALUES
new.c_id,
new.c_name,
new.r_id
创建INSTEADOF触发器需要注意以下几点:
●只能被创建在视图上,并且该视图没有指定WITHCHECKOPTION选项。
●不能指定BEFORE或AFTER选项。
●FOREACHROW子可是可选的,即INSTEADOF触发器只能在行级上触发、或只能是行级触发器,没有必要指定。
●没有必要在针对一个表的视图上创建INSTEADOF触发器,只要创建DML触发器就可以了。
ORACLE10G提供的系统事件触发器可以在DD
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE TRIGGER全面讲解 TRIGGER 全面 讲解
![提示](https://static.bdocx.com/images/bang_tan.gif)