trigger 错误ORA-04084 ORA-04088

错误trigger code
CREATE OR REPLACE TRIGGER trig_usernumber_insert_dxdss after insert on dxdss
referencing old as old_value new as new_value for each row
declare
v_curr_size integer;
v_has number;
v_sql varchar2(128);
v_num varchar2(33);
p_nError NUMBER;
p_userID NUMBER;
p_vaddusID NUMBER;
p_dxhfsID NUMBER;
p_dxhfsSID NUMBER;
p_dxhfsWID NUMBER;
p_dxhfsBID NUMBER;
V_ERROR_MESSAGE VARCHAR2(500);
begin
select count(*) into v_has from DXBG_MAIL_BOX_COUNT where usernumber=:new_value.USERNUMBER;
if(v_has =0) then
[color=red]select count(*) into v_curr_size from DXdss where usernumber=:new_value.USERNUMBER;[/color] insert into DXBG_MAIL_BOX_COUNT (USERNUMBER,BOX_CURR_SIZE) values (:new_value.USERNUMBER,v_curr_size);
elsif(v_has =1) then
[color=red]select BOX_CURR_SIZE into v_curr_size from DXBG_MAIL_BOX_COUNT where usernumber=:new_value.USERNUMBER;[/color]
v_curr_size:=v_curr_size+1;
update DXBG_MAIL_BOX_COUNT set BOX_CURR_SIZE=v_curr_size where usernumber=:new_value.USERNUMBER;
else
[color=red]select count(*) into v_curr_size from DXdss where usernumber=:new_value.USERNUMBER;[/color]
delete from DXBG_MAIL_BOX_COUNT where usernumber=:new_value.USERNUMBER;
insert into DXBG_MAIL_BOX_COUNT (USERNUMBER,BOX_CURR_SIZE) values (:new_value.USERNUMBER,v_curr_size);
end if;
EXCEPTION
when others then
p_nError := SQLCODE;
V_ERROR_MESSAGE := sqlerrm;
insert into HISTORYOAMALERT (ALERTTYPE, ALERTTIME, ALERTFROM, COMPUTER, ALERTLEVEL, DESCRIPTION)
values('ORACLE',TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'),'ORACLE','DB','1',p_nError||' '||V_ERROR_MESSAGE);
RAISE;

END;
ORA-04091 table string.string 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.

ORA-06512 at string line string

Cause: Backtrace message as the stack is unwound by unhandled exceptions.

Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or database administrator.

ORA-04088 error during execution of trigger 'string.string'

Cause: A runtime error occurred during execution of a trigger.

解释：
变异表是一个当前正在改变的表。改变可以是因为INSERT、UPDATE或DELETE语句，或者由于DELETE CASCADE约束。

这种错误类型只会在行级触发器上发生。

当表在改变时，不能对表进行查询或修改。细想一下就会发现它是有意义的。如果触发器因为表上的改变而激发，那么直到结束之前都看不到这种改变。尽管可以访问new和old伪记录，但是不能读取表的状态。任何这么做的企图都会引发ORA-04091异常。

下面演示了变异错误的发生过程。按照如下代码所示创建一个mutant表：

CREATE TABLE mutant

( mutant_id NUMBER

, mutant_name VARCHAR2(20));

然后可以插入4个主要“忍者神龟”：

INSERT INTO mutant VALUES (mutant_s1.nextval,'Donatello');

INSERT INTO mutant VALUES (mutant_s1.nextval,'Leonardo');

INSERT INTO mutant VALUES (mutant_s1.nextval,'Michelangelo');

INSERT INTO mutant VALUES (mutant_s1.nextval,'Raphael');

插入数据以后，可以构建下面的触发器：

CREATE OR REPLACE TRIGGER mutator

AFTER DELETE ON mutant

FOR EACH ROW

DECLARE

rows NUMBER;

BEGIN

SELECT COUNT(*) INTO rows FROM mutant;

dbms_output.put_line('[rows] has '||rows||']');

END;

/

触发器主体试图得到行数，但是它得不到行数，因为记录集没有结束。存在这个限制是为了防止触发器看到不一致的数据。

可以通过运行下面的命令删除变异表中的Michelangelo来激发触发器。DELETE语句为：

DELETE FROM MUTANT WHERE mutant_name = 'Michelangelo';

运行了该语句后，DELETE语句会抛出下面的错误堆栈：

DELETE FROM mutant WHERE mutant_name = 'Michelangelo'

ERROR at line 1:

ORA-04091: table PLSQL.MUTANT is mutating, trigger/function may not see it

ORA-06512: at "PLSQL.MUTATOR", line 4

ORA-04088: error during execution of trigger 'PLSQL.MUTATOR'

当触发器遇到变异表时，它会回滚触发器主体指令和触发语句。知道了为什么会生生变异表错误，就应当小心地避免这种错误。


错误的触发器code：
CREATE OR REPLACE TRIGGER trig_dxbg_count_insert [color=red]after [/color]insert on DXBG_MAIL_BOX_COUNT
referencing old as old_value new as new_value FOR EACH ROW
declare
v_curr_size integer;
v_has number;
v_boxid VARCHAR2(32);
v_box VARCHAR2(32);
v_sql VARCHAR2(128);
p_nError NUMBER;
V_ERROR_MESSAGE VARCHAR2(500);
begin
v_boxid:=SUBSTR((:new_value.USERNUMBER),10);
v_box:='DXBG_MAIL_BOX_'||v_boxid;
v_sql:='select count(*) from '||v_box||' where usernumber='||:new_value.USERNUMBER;
execute immediate v_sql into v_curr_size;
:new_value.BOX_CURR_SIZE:=v_curr_size;
EXCEPTION
when others then
p_nError := SQLCODE;
V_ERROR_MESSAGE := sqlerrm;
insert into HISTORYOAMALERT (ALERTTYPE, ALERTTIME, ALERTFROM, COMPUTER, ALERTLEVEL, DESCRIPTION)
values('ORACLE',TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'),'ORACLE','DB','1',p_nError||' '||V_ERROR_MESSAGE);
RAISE;

END;
/

执行报错，错误信息：ORA-04084 无法更改此触发器类型的NEW值

New trigger variables can only be changed in before row insert or update triggers.

把触发器的after改成before 触发