ORA-04091和Compound Trigger(Oracle 11g)
来源:互联网 发布:java mvc框架理解 编辑:程序博客网 时间:2024/06/05 13:21
Trigger
常见有两种:行(Row Trigger)和语句(Statement Trigger)
还有:Instead of Trigger和Event trigger。
例子1-Row Trigger:
CREATE OR REPLACE TRIGGER client AFTERINSERT ON tt1 FOR EACH rowBEGIN dbms_application_info.set_client_info(userenv('client_info')+1 );END;例子2-Statement TriggerCREATE OR REPLACE TRIGGER client_1 AFTERINSERT ON tt1BEGIN dbms_application_info.set_client_info(userenv('client_info')-1 );END;
ORA-04091错误
Tom Kyte有一篇文章很好的解释了ORA-04091。
http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html
部分摘抄如下:
Suppose wehave a table that includes country currency combinations with a primarycurrency. The following is sample data:
CountryCurrency Primary_Currency
US USD Y
US USN N
US USS N
We need toenforce the rule that at most one currency can be primary for a given country.We have a BEFORE UPDATE trigger on the above table for each row (usingautonomous transaction to avoid the mutating error) to check whether thecountry has any primary currency.
That was allI needed to read. I knew they had a serious bug on their hands when Iread—paraphrasing:
- At most one currency can be primary (we have a constraint that crosses rows in the table).
- We have a . . . trigger.
- We are using an autonomous transaction to avoid the mutating table error.
The trigger would have looked something like this:
SQL< create or replace 2 trigger currencies_trigger 3 before update on currencies 4 for each row 5 declare 6 PRAGMA AUTONOMOUS_TRANSACTION; 7 l_cnt number; 8 begin 9 select count(*) 10 into l_cnt 11 from currencies 12 where primary_currency='Y' 13 and country = :new.country; 14 if ( l_cnt < 1 ) 15 then 16 raise_application_error 17 (-20000, 'only one allowed'); 18 end if; 19 end; 20 /
Trigger created.
Now, there are many things wrong with this trigger. But the firstobvious clue that something was seriously wrong was their need to use anautonomous transaction. They did that because without it, an update wouldproduce the following result:
SQL< update currencies 2 set primary_currency = 'Y';
update currencies *ERROR at line 1:ORA-04091: table OPS$TKYTE.CURRENCIES Is mutating, trigger/functionmaynot see itORA-06512: at "OPS$TKYTE.CURRENCIES_TRIGGER”, line 4ORA-04088: error during execution oftrigger 'OPS$TKYTE.CURRENCIES_TRIGGER'That is not really an error but more of a warning. Basically it issaying, “You are doing something so fundamentally wrong in your trigger thatOracle Database is just not going to permit you to do that.” If the database allowedthe trigger to read the table it was defined on, as the update was proceeding,the trigger would see the table partially updated. If five rows were beingupdated, the row trigger would see the table with one of the rows modified,then two, then three, and so on. It would see the table in a manner in whichthe table never should be seen.
By way of example, suppose the CURRENCIES table, above, was in placewith the sample data provided in the question and the trigger was permitted toread the table while it was changing. Now issue the following command:
update currencies
set primary_currency =
decode(currency, 'USD','N', 'USN', 'Y')
where country = 'US'
and currency in ( 'USD', 'USN');
That should be OK, because it moves the primary currency flag fromUSD to USN. After the statement finishes, there will be only one primarycurrency row. But what if the rows get updated in order of first USN and thenUSD. Well, when the trigger fires the first time, it will see USN withPRIMARY_CURRENCY=‘Y’ and USD with PRIMARY_CURRENCY=‘Y’. The trigger will failthe statement, but the statement is supposed to succeed. On the other hand,what if the data is processed in the order of first USD and then USN? In thatcase, the trigger will fire and find zero PRIMARY_CURRENCY=‘Y’ rows and thenfire again, see only one, and be done.
So, for this trigger, the update will work for some data, sometimes.For other bits of data, the update will not work, sometimes. Two databases withthe same data will fail on different sets of rows and succeed on others. Itwill all depend on how the data happens to be organized on disk and in whatorder it is processed. And that is unacceptable (not to mention veryconfusing).
That, in a nutshell, is why the mutating table constraint exists: toprotect us from ourselves. But unfortunately, the developers asking thisquestion found a way around the mutating table constraint: the autonomoustransaction. That “feature” permits developers to query the table the triggeris firing on yet query it as if it were in another session/transactionaltogether. The trigger will not see its own modifications to the table, andthat is the huge flaw with this thinking: the trigger is attempting to validatethe very modifications it cannot see. The trigger’s sole purpose is to validatethe modifications to the data, but the trigger is reading the data before themodifications take place. It therefore cannot work!
Note that in Oracle Database 11g, there is a newfeature, the compound trigger that can be used to solve this issue.
Compound Trigger
官方文档见:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS694
下面给出一个例子
CREATE TABLE log ( emp_id NUMBER(6), l_name VARCHAR2(25));-- Create trigger that updates log and then reads employeesCREATE OR REPLACE TRIGGER log_deletions AFTER DELETE ON emp FOR EACHROWDECLARE n INTEGER;BEGIN INSERT INTO log (emp_id,l_name) VALUES ( :OLD.empno, :OLD.ename ); SELECT COUNT(*) INTO n FROMemp; DBMS_OUTPUT.PUT_LINE('Thereare now ' || n || ' employees.');END;DELETE FROM emp WHERE empno = 7935;
SQL Error: ORA-04091: table SCOTT.EMP is mutating, trigger/functionmay not see it
ORA-06512: at "SCOTT.LOG_DELETIONS", line 9
ORA-04088: error during execution of trigger 'SCOTT.LOG_DELETIONS'
04091. 00000 - "table%s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or auser defined plsql function that is referenced in
this statement)attempted to look at (or modify) a table that was
in the middle ofbeing modified by the statement which fired it.
*Action: Rewrite the trigger(or function) so it does not read that table.
很明显,该Trigger试图读取正在被更改的表,因而触发了ORA-04091。
我们可以用Oacle 11g的新特性compound Trigger重写这个Trigger,从而解决问题。
CREATE OR REPLACE TRIGGER log_deletions FOR DELETE ON emp COMPOUND TRIGGER n int; AFTER EACH ROW ISBEGIN INSERT INTO log (emp_id,l_name) VALUES ( :OLD.empno, :OLD.ename );END AFTER EACH ROW;AFTER STATEMENT ISBEGIN SELECT COUNT(*) INTO n FROMemp; DBMS_OUTPUT.PUT_LINE('Thereare now ' || n || ' employees.');END AFTER STATEMENT;END;
DELETE FROM emp WHERE empno = 7935;
1 rows deleted.
请注意:读取emp的select语句被放入AfterStatement段中,表示这条语句将在整个SQL Statement完成后才被执行。这就规避了ORA-04091错误。
- ORA-04091和Compound Trigger(Oracle 11g)
- Oracle compound trigger的大坑(Oracle-12c)
- oracle 11g,ora-00257,ora-09945
- oracle 11g ora-00972
- sql: Oracle 11g create table, function,trigger, sequence
- sql: Oracle 11g create table, function,trigger, sequence
- 解决连接oracle 11g报ORA-01034和ORA-27101的错误
- 解决连接Oracle 11g报ORA-01034和ORA-27101的错误
- oracle 11g listener.ora和tnsname.ora配置模板(IP地址方式)
- Oracle 10g ORA-07445和ORA-00108
- Oracle 11G R2.0 ORA-00600
- oracle 11g的ORA-28001 处理
- oracle 11g ORA-00845错误解决
- oracle 11g ora-03113错误
- Oracle 11g ORA-12560错误
- Oracle 11g R2 listener.ora&tnsnames.ora Sample
- oracle 11G ORA-03113 ORA-19815错误
- 华润基金oracle rac 10g迁移11g ORA-31693 ORA-31640:ORA-19505:
- android http带参数get与post
- ubuntu文件名乱码
- paip. 内存占用少的php ide选择评测总结
- linux md5工具
- C++默认构造函数——深入理解
- ORA-04091和Compound Trigger(Oracle 11g)
- Jquery 基本使用
- LeetCode Minimum Path Sum
- Linux 常用函数——memcpy函数
- 16--2--2指针变量交换
- SqlServer2005 合并两个字符串的方法
- DXSDK_Jun10安装错误解决!/directX安装错误
- 我心中的计算机学科(二)
- 十六进制数组转换为浮点计算方法两种算法