触发器在被同一表激发后无法读取该表 [英] Trigger cant read the table, after being fired by the same table

问题描述

让我们说我有一张桌子，如下表-

Lets say I have a table as follows--

create table employees 
(
 eno      number(4) not null primary key, 
 ename    varchar2(30),
 zip      number(5) references zipcodes,
 hdate    date
);

我使用以下代码块创建了触发器

I've created a trigger using the following code block

create or replace TRIGGER COPY_LAST_ONO
AFTER INSERT ON ORDERS
FOR EACH ROW
DECLARE

ID_FROM_ORDER_TABLE VARCHAR2(10);

BEGIN

SELECT MAX(ORDERS.ONO)INTO ID_FROM_ORDER_TABLE from ORDERS ;
DBMS_OUTPUT.PUT_LINE(ID_FROM_ORDER_TABLE);

INSERT INTO BACKUP_ONO VALUES( VALUE1, VALUE2,VALUE3, ID_FROM_ORDER_TABLE);

END;

触发器在插入后触发，并尝试从触发它的表中读取(逻辑上是duhh！)，但是oracle给我一个错误，并要求我修改触发器，以便它不读取表.错误代码-

The trigger fires after insertion and attempts to read from the table that fired it(logically duhh!) but oracle is giving me an error and asking me to modify the trigger so that it doesnt read the table. Error code-

Error report -
SQL Error: ORA-04091: table TEST1.ORDERS is mutating, trigger/function may not see it
ORA-06512: at "TEST1.COPY_LAST_ONO", line 8
ORA-04088: error during execution of trigger 'TEST1.LOG_INSERT'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql 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.

此触发器要实现的目的是将最后一个INSERTED ONO(这是ORDER表的主键)复制到INSERTED之后立即复制到其他表中.我不明白的是，为什么甲骨文抱怨呢?触发器正在尝试读取AFTER插入！

What I'm trying to achieve with this trigger is to copy the last INSERTED ONO (which is a primary key for the ORDER table) immediately to a different table after being INSERTED. What I don't get is, why oracle complaining? The trigger is attempting to read AFTER the insertion!

想法?解决方案?

非常感谢

推荐答案

如果要记录刚插入的ONO，请使用:new.ono并完全跳过选择:

If you are trying to log the ONO you just inserted, use :new.ono and skip the select altogether:

INSERT INTO BACKUP_ONO VALUES( VALUE1, VALUE2,VALUE3, :new.ono);

我不认为您可以从插入过程中的表中进行选择，因为尚未发出提交，因此发生了变异表错误.

I don't believe you can select from the table you are in the middle of inserting into as the commit has not been issued yet, hence the mutating table error.

P.S.考虑不要缩写.对于下一个开发人员，请明确说明其名称，无论您公司的命名标准如何，都将其命名为ORDER_NUMBER或至少一个公认的缩写，例如ORDER_NBR. :-)

P.S. Consider not abbreviating. Make it clear for the next developer and call it ORDER_NUMBER or at least a generally accepted abbreviation like ORDER_NBR, whatever your company's naming standards are. :-)

仅供参考-如果您要更新，则还可以访问:OLD.column，即更新前的值(当然，如果该列不是主键列).

FYI - If you are updating, you can access :OLD.column as well, the value before the update (of course if the column is not a primary key column).

这篇关于触发器在被同一表激发后无法读取该表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！