在將SYBASE的Trigger移植到ORACLE的時候發現一個問題，
就是在一張表的Trigger中不能含有操作該基表的SQL，
例如STOCKINFO的Trigger里不有操作STOCKINFO的SQL。
那加權平均價如何取呢？
現在只能在原平均價的基礎再與新價進行加權平均，結果是一樣的。
注意：
AFTER UPDATE的Trigger不能操作基表，只有在BEFORE INSERT的Trigger里才能操作基表。
BEFORE INSERT的Trigger只有用INSERT INTO T1(COL1) VALUES('1');才有效，
INSERT INTO T1(COL1) SELECT '1' FROM DUAL;也是無效的。
例如：
SQL> insert into t1 values (1,'a'); --先插入一條數據，避免ORA-01403: no data found 錯誤。
1 row inserted
SQL> commit;
SQL> create or replace trigger tri_t1
2 before insert on t1 for each row
3 declare
4 cvar varchar2(10);
5 begin
6 select 'Y' into cvar from t1 WHERE ROWNUM=1;
7 end;
8 /
Trigger created
SQL> insert into t1 values (2,'b'); -- insert into ... values 沒有問題
1 row inserted
SQL> insert into t1 select '3','c' from dual; -- insert into ... select .. from 報錯
ORA-04091: table TEST.T1 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TEST.TRI_T1'
并且:old和:new只有在行級（for each row）的Trigger有效。
網上原文：
ORA-04091與 table mutating
近日解決了一個trigger中報ORA-04091錯誤的問題，補了關于Oracle table mutating的一課：
mutating table 是指一個當前正在被update,delete,insert語句修改的表，如果在一個行級別的trigger中讀取或修改一個mutating table，則往往會遇到ORA-04091錯誤.例如，如果在trigger中使用了select或dml 語句訪問trigger所在的表，則就會收到這個錯誤。
然而，Oracle8i和9i文檔中都沒有解釋清楚before和after 類型的 row trigger 在對待兩種不同的insert語句(insert into ... values ... 與 insert into ... select ...)時的差別：
1、對于after 類型的 for each row 級別的triggers，不論哪種insert語句觸發了trigger，都不允許在 trigger 中訪問本trigger所依賴的table的，測試如下：
SQL> create table t1 ( c1 number,c2 varchar2(10));
Table created
SQL> create or replace trigger tri_t1
2 after insert on t1 for each row
3 declare
4 cvar varchar2(10);
5 begin
6 select 'Y' into cvar from t1 WHERE ROWNUM=1; --這里訪問了trigger 本表
7 end;
8 /
Trigger created
SQL> insert into t1 values (1,'a');
ORA-04091: table TEST.T1 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TES.TRI_T1'
SQL> insert into t1 select '1','a' from dual;
ORA-04091: table TEST.T1 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TEST.TRI_T1'
2、對于before 類型的 for each row 級別的triggers，如果使用 insert into ... values 語句觸發此trigger ，則在trigger 中訪問本table沒有問題；
但如果使用 insert into select .. from 語句觸發此trigger ，則在trigger 中訪問本table就報ora-04091錯誤；
只有在Oracle 7標準的開發文檔中有這樣的說明：
From the Application Developers Guide
"There is an exception to this restriction;
For single row INSERTs, constraining tables are mutating for
AFTER row triggers, but not for BEFORE row triggers.
INSERT statements that involve more than 1 row are not considered
single row inserts."
"INSERT INTO <table_name> SELECT ..." are not considered single row
inserts, even if they only result in 1 row being inserted.
測試如下：
SQL> drop trigger tri_t1;
Trigger dropped
SQL> insert into t1 values (1,'a'); --先插入一條數據，避免ORA-01403: no data found 錯誤。
1 row inserted
SQL> commit;
SQL> create or replace trigger tri_t1
2 before insert on t1 for each row
3 declare
4 cvar varchar2(10);
5 begin
6 select 'Y' into cvar from t1 WHERE ROWNUM=1;
7 end;
8 /
Trigger created
SQL> insert into t1 values (2,'b'); -- insert into ... values 沒有問題
1 row inserted
SQL> insert into t1 select '3','c' from dual; -- insert into ... select .. from 報錯
ORA-04091: table TEST.T1 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TEST.TRI_T1'
我們的開發人員因為不知道這個特別情況，近日在修改一個存儲過程時候，將原來的insert values 寫法改成了insert select 寫法，而trigger 又是before類型的，導致出現了ORA-04091錯誤，搞得分析了好久也沒有頭緒。
其實，在metalink中有一篇note說到了：
文檔 ID: 注釋:132569.1
主題: ORA-4091 on BEFORE ROW TRIGGER with INSERT statement
類型: PROBLEM
狀態: PUBLISHED
內容類型: TEXT/X-HTML
創建日期: 16-JAN-2001
上次修訂日期: 09-AUG-2004
Problem Description
-------------------
You want to do an insert into a table that has a BEFORE row Trigger.
When you hard code the values into the INSERT statement, the trigger works fine.
For example:
INSERT
INTO content (cont_name,cont_seg,cat_seq)
VALUES('blah',100,200);
1 row created.
However, your trigger errors with ERROR ORA-4091 with
INSERT INTO...select statement:
INSERT
INTO content (cont_name,cont_seq,cat_seq) (select....from category);
ERROR at line 1:
ORA-4091: table <schema>.CONTENT is mutating, trigger/function may not see it
ORA-6512: at "<schema>.INS_CONTENT", line 4
ORA-4088: error during execution of trigger '<schema>.INS_CONTENT'
TRIGGER:
CREATE OR REPLACE trigger INS_CONTENT
BEFORE INSERT on CONTENT
FOR EACH ROW
DECLARE
max_sort number;
BEGIN
SELECT max(cont_sort) INTO max_sort FROM CONTENT;
IF max_sort IS NOT NULL AND max_sort!= 99999 THEN
IF :new.cont_sort IS NULL THEN
:new.cont_sort := max_sort +1;
END IF;
END IF;
SELECT SEQ_CONT_SEQ.nextval INTO :new.CONT_SEQ from dual;
END;
Explanation
-----------
Error: ORA 4091
Text: table %s.%s is mutating, trigger/function may not see it
-------------------------------------------------------------------------------
Cause: A trigger (or a user defined PL/SQL 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.
Explanation:
You cannot look at or modify the table that is mutating.
Note:
From the Application Developers Guide
"There is an exception to this restriction;
For single row INSERTs, constraining tables are mutating for
AFTER row triggers, but not for BEFORE row triggers.
INSERT statements that involve more than 1 row are not considered
single row inserts."
"INSERT INTO <table_name> SELECT ..." are not considered single row
inserts, even if they only result in 1 row being inserted.
RELATED DOCUMENTS
-----------------
Oracle Application Developer's Guide (A68003-01)
Chapter 'Using Database Triggers', page 13-22)