ORA-04091和Compound Trigger(Oracle 11g)

Trigger

常見有兩種:行(Row Trigger)和語句(Statement Trigger)

還有：Instead of Trigger和Event trigger。

例子1-Row Trigger：

CREATE OR REPLACE TRIGGER client AFTER INSERT ON tt1 FOR EACH row

BEGIN

  dbms_application_info.set_client_info(userenv('client_info')+1 );

END;

例子2-Statement Trigger

CREATE OR REPLACE TRIGGER client_1 AFTER INSERT ON tt1

BEGIN

  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 we have a table that includes country currency combinations with a primary currency. The following is sample data:

 

Country Currency   Primary_Currency

US      USD        Y

US      USN        N

US      USS        N

 

We need to enforce 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 (using autonomous transaction to avoid the mutating error) to check whether the country has any primary currency.

That was all I needed to read. I knew they had a serious bug on their hands when I read—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 first obvious clue that something was seriously wrong was their need to use an autonomous transaction. They did that because without it, an update would produce 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/function may

not see it

ORA-06512: at "OPS$TKYTE.CURRENCIES_TRIGGER”, line 4

ORA-04088: error during execution of

trigger 'OPS$TKYTE.CURRENCIES_TRIGGER'

 

That is not really an error but more of a warning. Basically it is saying, “You are doing something so fundamentally wrong in your trigger that Oracle Database is just not going to permit you to do that.” If the database allowed the 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 being updated, 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 which the table never should be seen.

By way of example, suppose the CURRENCIES table, above, was in place with the sample data provided in the question and the trigger was permitted to read 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 from USD to USN. After the statement finishes, there will be only one primary currency row. But what if the rows get updated in order of first USN and then USD. Well, when the trigger fires the first time, it will see USN with PRIMARY_CURRENCY=‘Y’ and USD with PRIMARY_CURRENCY=‘Y’. The trigger will fail the 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 that case, the trigger will fire and find zero PRIMARY_CURRENCY=‘Y’ rows and then fire 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 with the same data will fail on different sets of rows and succeed on others. It will all depend on how the data happens to be organized on disk and in what order it is processed. And that is unacceptable (not to mention very confusing).

 

That, in a nutshell, is why the mutating table constraint exists: to protect us from ourselves. But unfortunately, the developers asking this question found a way around the mutating table constraint: the autonomous transaction. That “feature” permits developers to query the table the trigger is firing on yet query it as if it were in another session/transaction altogether. The trigger will not see its own modifications to the table, and that is the huge flaw with this thinking: the trigger is attempting to validate the very modifications it cannot see. The trigger’s sole purpose is to validate the modifications to the data, but the trigger is reading the data before the modifications take place. It therefore cannot work!

 

Note that in Oracle Database 11g, there is a new feature, 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 employees

CREATE OR REPLACE TRIGGER log_deletions

  AFTER DELETE ON emp FOR EACH ROW

DECLARE

  n INTEGER;

BEGIN

  INSERT INTO log (emp_id, l_name) VALUES (

    :OLD.empno,

    :OLD.ename

  );

 

  SELECT COUNT(*) INTO n FROM emp;

  DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');

END;

 

DELETE FROM emp WHERE empno = 7935;

 

SQL Error: ORA-04091: table SCOTT.EMP is mutating, trigger/function may 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 a user defined plsql function that is referenced in

           this statement) attempted to look at (or modify) a table that was

           in the middle of being 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 IS

BEGIN

  INSERT INTO log (emp_id, l_name) VALUES (

    :OLD.empno,

    :OLD.ename

  );

END AFTER EACH ROW;

AFTER STATEMENT IS

BEGIN

  SELECT COUNT(*) INTO n FROM emp;

  DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');

END AFTER STATEMENT;

END;

DELETE FROM emp WHERE empno = 7935;
1 rows deleted.

請注意：讀取emp的select語句被放入After Statement段中，表示這條語句將在整個SQL Statement完成後才被執行。這就規避了ORA-04091錯誤。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-1062459/，如需轉載，請註明出處，否則將追究法律責任。