Oracle Trigger :Before,after,each row and table level triggers ORA-04082 ORA-04084
来源:互联网 发布:秀才权限管理系统源码 编辑:程序博客网 时间:2024/05/18 03:37
Before, after, each row and table level triggers [Oracle]
The goal of this page is to demonstrate the most important differences between before and after triggers as well as the differences between for each row and table level triggers.
Before / for each row trigger
A before trigger is called before because it fires before
create table t_update_before_each_row ( txt varchar2(10));create table log ( txt varchar2(20));create trigger update_before_each_row before update on t_update_before_each_row for each rowbegin :new.txt := upper(:new.txt); insert into log values ('old: ' || :old.txt); insert into log values ('new: ' || :new.txt);end update_before_each_row;/
insert into t_update_before_each_row values('one');insert into t_update_before_each_row values('two');insert into t_update_before_each_row values('three');insert into t_update_before_each_row values('four');
Updating (that is: concatenating the value with itself) the rows containing two and three:
update t_update_before_each_row set txt = txt || txt where substr(txt,1,1) = 't';
select * from t_update_before_each_row;
As can be seen by the output of the select statement, the trigger changed the values of the new values; they're in uppercase now:
oneTWOTWOTHREETHREEfour
The log displays the old and new values:
select * from log;
old: twonew: TWOTWOold: threenew: THREETHREE
Cleaning up:
drop table t_update_before_each_row;drop table log;
After / for each row trigger
In contrast to a before trigger, an after trigger does not allow to change
:new.field_name
because the value is, when the trigger fires, already written to the table. If one tries to assign a value to
:new.field_name
, Oracle throws an ORA-04084: cannot change NEW values for this trigger type. create table t_update_after_each_row ( txt varchar2(10));create table log ( txt varchar2(20));create trigger update_after_each_row after update on t_update_after_each_row for each rowbegin -- :new.txt := upper(:old.txt); -- ORA-04084: cannot change NEW values for this trigger type insert into log values ('old: ' || :old.txt); insert into log values ('new: ' || :new.txt);end update_after_each_row;/
insert into t_update_after_each_row values('one');insert into t_update_after_each_row values('two');insert into t_update_after_each_row values('three');insert into t_update_after_each_row values('four');
update t_update_after_each_row set txt = txt || txt where substr(txt,1,1) = 't';
select * from t_update_after_each_row;
onetwotwothreethreefour
select * from log;
As the log table shows, it is possible to use
:new
and :old
although it's not possible to assign something to :new
. old: twonew: twotwoold: threenew: threethree
Cleaning up:
drop table t_update_after_each_row;drop table log;
Table level trigger
A table level trigger is a trigger that doesn't fire for each row to be changed. Accordingly, it lacks the
for each row
. Consequently, both, the :new
and :old
are not permitted in the trigger's PL/SQL block, otherwise, an ORA-04082: NEW or OLD references not allowed in table level triggers is thrown. create table t_update_before ( txt varchar2(10));create table log ( txt varchar2(20));create trigger update_before before update on t_update_beforebegin -- :new.txt := upper(:old.txt); -- ORA-04082 insert into log values ('update trigger');end update_before;/
insert into t_update_before values('one');insert into t_update_before values('two');insert into t_update_before values('three');insert into t_update_before values('four');
update t_update_before set txt = txt || txt where substr(txt,1,1) = 't';
select * from t_update_before;
onetwotwothreethreefour
select * from log;
Although two rows were updated, only one record is found in the log table:
select * from log;
update trigger
An update statement that doesn't update any row:
update t_update_before set txt = txt || txt where txt = 'no update';
Still, the trigger fires...
select * from log;
... which results in another row found in the log table:
update triggerupdate trigger
Cleaning up:
drop table t_update_before;drop table log;
Order of execution
Oracle allows to create multiple triggers on the same table. The order of the execution of these triggers is undeterministic (or random, if you want this word) except that all before triggers fire before the after triggers.
- Oracle Trigger :Before,after,each row and table level triggers ORA-04082 ORA-04084
- Before, after, each row and table level triggers [Oracle]
- Oracle —— before row trigger OR after row trigger
- oracle trigger中的before和after
- [Bug]ArcSDE分区表报:ORA-14301: table-level attributes must be specified before partition-level attribute
- ORA-04082: 及 ORA-06512 异常,table trigger 影响自身修改示例
- PLSQL Trigger FOR EACH ROW
- ORA-600 [16606] Error From Database Level Triggers Using :OLD or :NEW Syntax [ID 1089801.1]
- trigger抛出异常ORA-04091:table ...... is mutating, trigger/function may not see it
- MySQL触发器 trigger之for each row
- ORA-04091和Compound Trigger(Oracle 11g)
- Oracle trigger recursive level
- Oracle/PLSQL: AFTER DELETE Trigger
- oracle自增auto_increment与oracle 创建触发器是非法的 ORA-04098: trigger 'USER_TRIGGER'is invalid and failed re-v
- ORA-04091: table XXXX is mutating, trigger/function may not see it
- 错误"ORA-04091: table is mutating, trigger/function may not see it"的原因以及解决办法
- ORA-04091: table name is mutating, trigger/function may not see it.
- ORA-04091: table is mutating, trigger/function may not see it
- 项目软件过程的迭代设计作业(案例设计)
- 从VB6 到VB.net 2005(一)
- 学习.Net的经典网站
- Facebook App 的5种盈利建议
- ubuntu source.list
- Oracle Trigger :Before,after,each row and table level triggers ORA-04082 ORA-04084
- SOCKET_JAVA
- 可定制的数据库备份和恢复程序
- 水晶报表参数编程示例代码
- 页面装载用户控件时的事件顺序
- Antechinus JavaScript Editor--JS编辑工具
- .NET中的密码学--对称加密
- 从追MM谈Java的23种设计模式
- .NET对软件安装的冲击