变异，触发器/函数可能看不到它 - 触发器执行期间出错
CREATE OR REPLACE TRIGGER UPDATE_TEST_280510
AFTER insert on TEST_TRNCOMPVISIT
declare
V_TRNCOMPNO NUMBER(10);
CURSOR C1 IS SELECT B.COMPNO FROM TEST_TRNCOMPVISIT A, TEST_TRNCOMPMST B,
TEST_MEMMAST C
WHERE A.COMPNO=B.COMPNO
AND B.TRNMEMID=C.MEMID
AND C.MEMOS>=1000;
begin
open c1;
fetch c1 into V_TRNCOMPNO;
UPDATE TEST_TRNCOMPMST SET COMPSTATUS='P',
remark='comp is pending due to O/S>1000'
WHERE COMPNO=V_TRNCOMPNO AND COMPSTATUS='C';
CLOSE C1;
end;
我已经制作了此触发器，并且在表中插入行时 - TEST_TRNCOMPVISIT 它给出了以下错误 -
发生了以下错误：
ORA-04091: 表 TEST.TEST_TRNCOMPVISIT 正在发生变化，触发器/函数可能看不到它
ORA-06512: 在“TEST.UPDATE_TEST_280510”，第 4 行
ORA-06512: 在“TEST.UPDATE_TEST_280510”，第 10 行
ORA-04088：执行触发器“TEST.UPDATE_TEST_280510”期间出错
CREATE OR REPLACE TRIGGER UPDATE_TEST_280510
AFTER insert on TEST_TRNCOMPVISIT
declare
V_TRNCOMPNO NUMBER(10);
CURSOR C1 IS SELECT B.COMPNO FROM TEST_TRNCOMPVISIT A, TEST_TRNCOMPMST B,
TEST_MEMMAST C
WHERE A.COMPNO=B.COMPNO
AND B.TRNMEMID=C.MEMID
AND C.MEMOS>=1000;
begin
open c1;
fetch c1 into V_TRNCOMPNO;
UPDATE TEST_TRNCOMPMST SET COMPSTATUS='P',
remark='comp is pending due to O/S>1000'
WHERE COMPNO=V_TRNCOMPNO AND COMPSTATUS='C';
CLOSE C1;
end;
I have made this trigger and while insert the row in table- TEST_TRNCOMPVISIT it gives following error-
The following error has occurred:
ORA-04091: table TEST.TEST_TRNCOMPVISIT is mutating, trigger/function may not see it
ORA-06512: at "TEST.UPDATE_TEST_280510", line 4
ORA-06512: at "TEST.UPDATE_TEST_280510", line 10
ORA-04088: error during execution of trigger 'TEST.UPDATE_TEST_280510'
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当定义为 FOR EACH ROW 的触发器尝试访问为其触发的表时，会引发“表正在发生变化”异常。 Tom Kyte 在此处编写了有关此异常的原因和解决方案的精彩指南。
在您发布的示例中，您没有 FOR EACH ROW，因此我不希望引发异常。通常，只有在需要访问每一行的 :OLD 或 :NEW 值（但您不需要）的情况下才需要使用 FOR EACH ROW 触发器。
The "table is mutating" exception is raised when a trigger that is defined as FOR EACH ROW tries to access the table that is was fired for. Tom Kyte has written a great guide to the causes and resolution of this exception here.
In your posted example you do not have FOR EACH ROW and so I would not expect the exception to be raised. Usually one only needs to use FOR EACH ROW triggers in cases where it is necessary to access the :OLD or :NEW values of each row, which you are not.
这是你唯一的触发点吗？
您的触发器更新表 TEST_TRNCOMPMST。如果此表上存在访问 TEST_TRNCOMPVISIT 的触发器，您将收到错误消息。
Is this the only trigger you have?
Your trigger updates table TEST_TRNCOMPMST. If there is a trigger on this table that accesses TEST_TRNCOMPVISIT you get the error message.