关于错误:"ORA-04091: table is mutating, trigger/function may not see it"的分析(触发器操作自身表)
来源:互联网 发布:nginx websocket 配置 编辑:程序博客网 时间:2024/05/22 06:52
在写trigger的时候,经常会遇到这种情况
当在程序块中需要对trigger本表进行修改或查询的时候,系统会提示错误: ORA-04091: table is mutating, trigger/function may not see it
关于这个错误,其实是由于对本表的操作造成的.ORACLE DB里默认在写TRIGGER的时候把本表锁死,不允许对其进行操作,也就是说这个错误是不能通过系统的手段解决的,只能改用一些其它的SQL来绕开它.
对此可通过自治事物解决:
SQL> CREATE TABLE T(ID NUMBER(18),MC VARCHAR2(20),DT DATE);
表已创建。
SQL> CREATE OR REPLACE TRIGGER TR_T
2 AFTER DELETE ON T
3 FOR EACH ROW
4 DECLARE V_COUNT NUMBER;
5 --PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 INSERT INTO T VALUES(:OLD.ID,:OLD.MC,SYSDATE);
8 COMMIT;
9 END TR_DEL_CABLE;
10 /
触发器已创建
SQL> INSERT INTO T VALUES(1,'111111',SYSDATE);
已创建 1 行。
SQL> INSERT INTO T VALUES(2,'222222',SYSDATE);
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT ID,MC,TO_CHAR(DT,'YYYYMMDD HH24:MI:SS') FROM T;
ID MC TO_CHAR(DT,'YYYYM
---------- -------------------- -----------------
1 111111 20080802 11:07:36
2 222222 20080802 11:07:43
SQL> DELETE FROM T WHERE ID=1;
DELETE FROM T WHERE ID=1
*
第 1 行出现错误:
ORA-04091: 表 TEST.T 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "TEST.TR_T", line 4
ORA-04088: 触发器 'TEST.TR_T' 执行过程中出错
SQL> SELECT ID,MC,TO_CHAR(DT,'YYYYMMDD HH24:MI:SS') FROM T;
ID MC TO_CHAR(DT,'YYYYM
---------- -------------------- -----------------
1 111111 20080802 11:07:36
2 222222 20080802 11:07:43
SQL> CREATE OR REPLACE TRIGGER TR_T
2 AFTER DELETE ON T
3 FOR EACH ROW
4 DECLARE V_COUNT NUMBER;
5 PRAGMA AUTONOMOUS_TRANSACTION;--开启自治事物
6 BEGIN
7 INSERT INTO T VALUES(:OLD.ID,:OLD.MC,SYSDATE);
8 COMMIT;--提交自治事物
9 END TR_DEL_CABLE;
10 /
触发器已创建
SQL> DELETE FROM T WHERE ID=1;
已删除 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT ID,MC,TO_CHAR(DT,'YYYYMMDD HH24:MI:SS') FROM T;
ID MC TO_CHAR(DT,'YYYYM
---------- -------------------- -----------------
2 222222 20080802 11:07:43
1 111111 20080802 11:08:32
表已创建。
SQL> CREATE OR REPLACE TRIGGER TR_T
2 AFTER DELETE ON T
3 FOR EACH ROW
4 DECLARE V_COUNT NUMBER;
5 --PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 INSERT INTO T VALUES(:OLD.ID,:OLD.MC,SYSDATE);
8 COMMIT;
9 END TR_DEL_CABLE;
10 /
触发器已创建
SQL> INSERT INTO T VALUES(1,'111111',SYSDATE);
已创建 1 行。
SQL> INSERT INTO T VALUES(2,'222222',SYSDATE);
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT ID,MC,TO_CHAR(DT,'YYYYMMDD HH24:MI:SS') FROM T;
ID MC TO_CHAR(DT,'YYYYM
---------- -------------------- -----------------
1 111111 20080802 11:07:36
2 222222 20080802 11:07:43
SQL> DELETE FROM T WHERE ID=1;
DELETE FROM T WHERE ID=1
*
第 1 行出现错误:
ORA-04091: 表 TEST.T 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "TEST.TR_T", line 4
ORA-04088: 触发器 'TEST.TR_T' 执行过程中出错
SQL> SELECT ID,MC,TO_CHAR(DT,'YYYYMMDD HH24:MI:SS') FROM T;
ID MC TO_CHAR(DT,'YYYYM
---------- -------------------- -----------------
1 111111 20080802 11:07:36
2 222222 20080802 11:07:43
SQL> CREATE OR REPLACE TRIGGER TR_T
2 AFTER DELETE ON T
3 FOR EACH ROW
4 DECLARE V_COUNT NUMBER;
5 PRAGMA AUTONOMOUS_TRANSACTION;--开启自治事物
6 BEGIN
7 INSERT INTO T VALUES(:OLD.ID,:OLD.MC,SYSDATE);
8 COMMIT;--提交自治事物
9 END TR_DEL_CABLE;
10 /
触发器已创建
SQL> DELETE FROM T WHERE ID=1;
已删除 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT ID,MC,TO_CHAR(DT,'YYYYMMDD HH24:MI:SS') FROM T;
ID MC TO_CHAR(DT,'YYYYM
---------- -------------------- -----------------
2 222222 20080802 11:07:43
1 111111 20080802 11:08:32
0 0
- 关于错误:"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"的原因以及解决办法
- 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 name 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 state
- ora-04091 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
- 包含变异表(mutating table)的触发器
- 遇到ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations错误
- mysql触发器报错 1422 Explicit or implicit commit is not allowed in stored function or trigger.
- 创建触发器后不能进行修改操作,Can't update table 'psxt_user' in stored function/trigger because
- [Java笔记]ThinkingInJava一书中Bruce Eckel关于OOP的描述
- HTML图像、路径、链接和列表
- 细说CSS中的display属性
- spring在web容器启动时执行初始化方法
- Duilib学习笔记《04》— 窗体显示
- 关于错误:"ORA-04091: table is mutating, trigger/function may not see it"的分析(触发器操作自身表)
- 排序2:插入排序(折半插入排序)
- poj 3276 Face the right way(反转)
- hdu4549
- C++局部变量指针保持在List,Vector中使用的问题
- C# 委托实例(跨窗体操作控件)
- 链表,逆波兰表达式
- Duilib学习笔记《03》— 控件使用
- Add a Sensor to a Robot