错误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
select count(*) into v_curr_size from DXdss where usernumber=:new_value.USERNUMBER; insert into DXBG_MAIL_BOX_COUNT (USERNUMBER,BOX_CURR_SIZE) values (:new_value.USERNUMBER,v_curr_size);
elsif(v_has =1) then
select BOX_CURR_SIZE into v_curr_size from DXBG_MAIL_BOX_COUNT where usernumber=:new_value.USERNUMBER;
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
select count(*) into v_curr_size from DXdss where usernumber=:new_value.USERNUMBER;
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
after 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 触发
分享到:
相关推荐
使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查。使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查 使用工具IMPDP导入数据时ORA-39002、ORA-39070错误排查
ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法ora-00604 错误 解决 方法
在运行查询SELECT * FROM V$SESSION 会出现ORA-29275:部分多字节字符的错误,这是什么原因开始我不得其解,网上也没有介绍什么好办法。本文给出答案。
Oracle 11gr2连Oracle 19c 报ORA-28040 ORA-01017解决方法
oracle数据库ora-01152和ora-01110的解决办法
oracle网络配置(listener_ora-sqlnet_ora-tnsnames_ora).mht
Drop goldengate用户时,报ORA-00604 ORA-20782 ORA-06512错误
ORA-12560 TNS 协议适配器错误
ORA-00604: 递归SQL层1出现错误 ORA-03106: 致命的双工通信协议错误 ORA-02063: 紧接着line(源于dblink) 以及 ORA-04052: 在查找远程对象时出错 ORA-00604: 递归SQL层1出现错误 ORA-03120: 双工转换例行程序:整数...
ORACLE ORA-00132 ORA-00214
NULL 博文链接:https://rongren.iteye.com/blog/1886071
离线误删空间文件导致的ORA-01033及ORA-01145问题的解决办法,在解决ORA-01033的过程中,又出现ORA-01145 * 第 1 行出现错误: ORA-01145: 除非启用了介质恢复, 否则不允许立即脱机 接着的解决步骤
oracle启动失败,ORA-00702报错,windows,linux系统下解决办法
创建物化视图ORA-12014错误解决方法 创建物化视图ORA-12014错误解决方法
客户端进行连接的时候,系统不定期出现ora-12520,ora-12516的连接问题, 问题解决方案建议: 1、增加process和session的连接数。 2、检查连接的应用,是不是有没有释放的连接。 3、将修改参数local_listener中的vip为...
用oracle数据库新建连接时遇到ora-12505,此问题解决后又出现ora-12519错误,郁闷的半天,经过一番折腾问题解决,下面小编把我的两种解决方案分享给大家,仅供参考。 解决方案一: 今天工作时在新建连接的时候遇到...
ORA-12541 TNSno listener 的解决方案 ORA-12541 TNSno listener 的解决方案
【DATAGUARD】物理dg配置客户端无缝切换 (八.4)--ora-16652 和 ora-16603错误【DATAGUARD】物理dg配置客户端无缝切换 (八.4)--ora-16652 和 ora-16603错误
在oracle里面运行一下,解决Exception java.sql.SQLException ORA-00600 内部错误代码