插入触发器后发生突变错误 [英] Mutating error on after insert trigger

问题描述

以下代码给出了变异错误. 任何人都可以帮助解决这个问题吗?

The below code is giving a mutating error. Can any1 pls help in solving this.

    CREATE OR REPLACE TRIGGER aso_quote_cuhk_trigger
    BEFORE INSERT
    ON aso.aso_quote_headers_all
    FOR EACH ROW
    BEGIN
     UPDATE aso.aso_quote_headers_all
     SET quote_expiration_date=sysdate+90
     where quote_header_id=:new.quote_header_id;
    END;
    /

推荐答案

在oracle中，触发器分为两个级别:行级别和表级别.

In oracle there are two levels of triggers: row level and table level.

行级触发器在for each row处执行.表级触发器针对每个语句执行，即使一条语句更改多于一行.
在行级触发器中，您无法选择/更新具有触发器的表本身:您将得到一个变异错误.

Row level triggers are executed for each row. Table level triggers executed per statement, even if a statement changed more then one row.
In a row level trigger, you cannot select/update the table itself that has the trigger: you will get a mutating error.

在这种情况下，不需要UPDATE语句.只需尝试一下:

In this case, there is no need for an UPDATE statement. Just try this:

CREATE OR REPLACE TRIGGER aso_quote_cuhk_trigger
BEFORE INSERT
ON aso.aso_quote_headers_all
FOR EACH ROW
BEGIN
 :new.quote_expiration_date=sysdate+90;     
END;
/

EDIT (Rajesh)提到，有可能在插入新行之前，OP希望更新aso_quote_headers_all表中的所有其他记录.

EDIT Rajesh mentioned it is possible, that before inserting a new row, OP wants to update all other records in the aso_quote_headers_all table.

嗯，这是可行的，但是有点棘手.要正确执行此操作，您将需要

Well, this is feasible, but it's a little tricky. To do this properly, you will need

1. pl/sql软件包和软件包标头中的变量，这些变量由触发器修改.此变量可以是一个列表，其中包含新插入的记录的ID.插入触发器后的行级别会将新ID添加到列表中.对于每个不同的会话，此包变量的内容将有所不同，因此我们将此变量称为session_variable.
2. 插入触发器后的行级，它将为session_variable添加新的ID.
插入触发器之后的
3. 表级别，该触发器将从session_variable获取ID，处理该ID，然后将其从session_variable中删除.该触发器可以在aso_quote_headers_all上执行必要的选择/更新.处理新插入的ID后，此触发器应确保将其从session_variable中删除.

1. A pl/sql package and a variable in the package header that is modified by the triggers. This variable could be a list holding the IDs of newly inserted records. Row level after insert trigger would add a new ID to the list. The content of this package variable will be different for each different session, so let's call this variable session_variable.
2. Row level after insert trigger, that would add new ID to the session_variable.
3. Table level after insert trigger that would get IDs from the session_variable, process the ID and then remove it from the session_variable. This trigger could execute necessary selects/updates on the aso_quote_headers_all. After a newly inserted ID is processed, this trigger should make sure it gets removed from the session_variable.

这篇关于插入触发器后发生突变错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！