trigger抛出异常ORA-04091:table ...... is mutating, trigger/function may not see it
来源:互联网 发布:java类加载机制面试题 编辑:程序博客网 时间:2024/05/17 11:37
1.问题描述
今天做一个触发器例子,假设为A表写一个触发器A_trigger,在触发器中对本表(A表)进行DML,然后抛出异常:ORA-04091:table ...... is mutating, trigger/function may not see it
2.问题产生的原因:
这是有在被触发TRIGGER工作的时候,默认把当前表 (表)锁死,不允许对其进行操作,不允许对其进行操作,.,然而在trigger中包含了对当前表的DML操作,所以就报错了;
3.解决问题的方式:
第一种:自治事务:AUTONOMOUS_TRANSACTION,就是自己管理自己意思,自己可以commit,但是不建议使用这个,因为一个DML可能触发很多次触发器,导致死锁什么的,它的作用一般是用作审计日志;
第二种:用存储过程代替触发器:这个是建议使用的;
第三种:改变sql绕过去(:new , :old):这个一般
4.代码:
第一种:自治事务
create or replace trigger emps_jon_triggerafter insert or update or delete on emps_jonfor each rowdeclare PRAGMA AUTONOMOUS_TRANSACTION; .... begin...end;第二种:用存储过程代替触发器
create or replace procedure emps_insert_proisbegin insert into depts_jon values(depts_seq.nextval,'销售部1','北京');end;第三种:改变sql绕过去
create or replace trigger insert_jon_trigbefore insert on emps_jon for each row begin :new.ename := 'A'; end insert_jon_trig;
谢谢阅读!!
0 0
- trigger抛出异常ORA-04091:table ...... is mutating, trigger/function may not see it
- ORA-04091: table XXXX is mutating, trigger/function may not see it
- 错误"ORA-04091: table is mutating, trigger/function may not see it"的原因以及解决办法
- ORA-04091: table name is mutating, trigger/function may not see it.
- ORA-04091: table is mutating, trigger/function may not see it
- 关于错误:"ORA-04091: table is mutating, trigger/function may not see it"的分析(触发器操作自身表)
- 关于错误:"ORA-04091: table is mutating, trigger/function may not see it"的分析(触发器操作自身表)
- 关于错误:"table is mutating, trigger/function may not see it
- Oracle触发器报错table xxxx is mutating,trigger/function may not see it
- 關於觸發器(Trigger)對於變異表(Mutating table)的操作
- MySQL触发器更新本表数据异常:Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this
- MySQL触发器更新本表数据异常:Can’t update table ‘tbl’ in stored function/trigger because it is already used by s
- mysql 触发器的问题 Can't update table 'tbl' in stored function/trigger because it is already used by state
- Can't update table 'psxt_user' in stored function/trigger because it is already used by statement wh
- ora-04091 mutating table
- "udevadm trigger is not permitted"
- mysql 触发器 trigger 中断操作 抛出异常
- Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/t
- 【zzulioj 1902 985的因子对难题】
- linux 安装git
- iOS 弹出的键盘被window遮挡住了
- ZZULI 1904: 小火山的股票交易
- SSM框架运行原理
- trigger抛出异常ORA-04091:table ...... is mutating, trigger/function may not see it
- 程序猿需要了解的CPU Cache
- Myeclipse下部署项目以及JSP的连接
- Html控件和Web控件的比较分析
- iOS 手势笔记
- 物联网通信第一天
- placeholder效果适配IE8(2)
- iOS界面布局之四——使用第三方库Masonry进行autolayout布局
- 接口和抽象类有什么区别