插入触发器正常但更新触发器触发ORA-04091变异表错误求助

需求背景 #

需要为项目阶段表（Projet_phase）创建两个独立触发器，分别处理插入和更新操作，确保项目所有阶段的预算总和不超过关联项目表（Projet）中的项目预算。

现有代码 #

表创建语句 #

CREATE TABLE Projet (
        id_projet integer PRIMARY KEY,
        nom_projet varchar(50) NOT NULL,
        date_debut date NOT NULL,
        date_fin date NOT NULL,
        budget numeric(10,2) NOT NULL
);

CREATE TABLE Projet_phase (
        id_projet integer,
        id_phase integer UNIQUE,
        nom_phase varchar(50) NOT NULL,
        date_debut date NOT NULL,
        date_fin date NOT NULL,
        budget numeric(10,2) NOT NULL,
        PRIMARY KEY (id_projet, id_phase),
        FOREIGN KEY (id_projet) REFERENCES Projet(id_projet)ON DELETE CASCADE
); 

触发器代码 #

-- 插入阶段时验证预算
CREATE OR REPLACE TRIGGER T_validerbudget_Insertion_projetphase
BEFORE INSERT ON Projet_phase
FOR EACH ROW
DECLARE
    budget_total NUMBER := 0;
    budget_projet Projet.budget%TYPE;

    
BEGIN
    SELECT NVL((sum(budget) + :NEW.budget), 0) total
        INTO budget_total
        FROM projet_phase
        WHERE id_projet = :NEW.id_projet;
    SELECT budget INTO budget_projet FROM Projet WHERE id_projet = :NEW.id_projet;
    IF budget_total > budget_projet THEN
        RAISE_APPLICATION_ERROR(
        -20001,
        'IMPOSSIBLE D''AJOUTER LA PHASE CAR LA SOMME DES BUDGETS DES PHASES DÉPASSE CELUI DU PROJET'
        );
    END IF;
END;
/

-- 更新阶段时验证预算（报错触发器）
CREATE OR REPLACE TRIGGER T_validerbudget_Insertion_projetphase
BEFORE UPDATE ON Projet_phase
FOR EACH ROW
DECLARE
    budget_total NUMBER := 0;
    budget_projet Projet.budget%TYPE;

    
BEGIN
    SELECT NVL((sum(budget) + :NEW.budget), 0) total
        INTO budget_total
        FROM projet_phase
        WHERE id_projet = :NEW.id_projet AND id_phase <> :NEW.id_phase;
    SELECT budget INTO budget_projet FROM Projet WHERE id_projet = :NEW.id_projet;
    IF budget_total > budget_projet THEN
        RAISE_APPLICATION_ERROR(
        -20001,
        'IMPOSSIBLE DE MODIFIER LA PHASE CAR LA SOMME DES BUDGETS DES PHASES DÉPASSE CELUI DU PROJET'
        );
    END IF;
END;
/

错误现象 #

插入触发器运行正常，但更新触发器执行时抛出错误：

ORA-04091 table string.string is mutating, trigger/function may not see it

疑问点 #

1. 为何仅更新触发器出现该问题，插入触发器却正常？
2. 若触发器无法访问正在修改的表数据，该如何验证预算总和？

解答 #

1. 仅更新触发器报错的原因 #

Oracle的行级触发器（FOR EACH ROW）在执行时，若触发器内部尝试读取正在被修改的表（即触发触发器的表），会触发变异表错误（ORA-04091）：

• 插入操作时，新行尚未写入表中，Projet_phase表处于稳定状态，触发器读取现有数据加新值计算的逻辑不会触发变异表检查。
• 更新操作时，当前行已被标记为修改状态，表处于"变异"状态（数据未最终提交，状态不确定），Oracle禁止触发器读取这种状态的表，因此抛出错误。

2. 正确的预算验证方案 #

要避免变异表错误，推荐使用复合触发器（Oracle 11g及以上支持），同时满足"分开处理插入和更新"的要求，可以拆分为两个独立的复合触发器：

插入操作专用触发器

CREATE OR REPLACE TRIGGER T_validerbudget_Insert_projetphase
FOR INSERT ON Projet_phase
COMPOUND TRIGGER

    TYPE t_projet_ids IS TABLE OF Projet_phase.id_projet%TYPE;
    v_projet_ids t_projet_ids := t_projet_ids();

    BEFORE EACH ROW IS
    BEGIN
        IF v_projet_ids.COUNT = 0 OR NOT (:NEW.id_projet MEMBER OF v_projet_ids) THEN
            v_projet_ids.EXTEND;
            v_projet_ids(v_projet_ids.LAST) := :NEW.id_projet;
        END IF;
    END BEFORE EACH ROW;

    AFTER STATEMENT IS
        CURSOR c_projet_budget IS
            SELECT p.id_projet, p.budget projet_budget, SUM(pp.budget) phases_total
            FROM Projet p
            JOIN Projet_phase pp ON p.id_projet = pp.id_projet
            WHERE p.id_projet MEMBER OF v_projet_ids
            GROUP BY p.id_projet, p.budget;
        v_projet_rec c_projet_budget%ROWTYPE;
    BEGIN
        FOR v_projet_rec IN c_projet_budget LOOP
            IF v_projet_rec.phases_total > v_projet_rec.projet_budget THEN
                RAISE_APPLICATION_ERROR(
                    -20001,
                    'IMPOSSIBLE D''AJOUTER LA PHASE CAR LA SOMME DES BUDGETS DES PHASES DÉPASSE CELUI DU PROJET'
                );
            END IF;
        END LOOP;
    END AFTER STATEMENT;

END T_validerbudget_Insert_projetphase;
/

更新操作专用触发器

CREATE OR REPLACE TRIGGER T_validerbudget_Update_projetphase
FOR UPDATE OF budget ON Projet_phase
COMPOUND TRIGGER

    TYPE t_projet_ids IS TABLE OF Projet_phase.id_projet%TYPE;
    v_projet_ids t_projet_ids := t_projet_ids();

    BEFORE EACH ROW IS
    BEGIN
        IF v_projet_ids.COUNT = 0 OR NOT (:NEW.id_projet MEMBER OF v_projet_ids) THEN
            v_projet_ids.EXTEND;
            v_projet_ids(v_projet_ids.LAST) := :NEW.id_projet;
        END IF;
    END BEFORE EACH ROW;

    AFTER STATEMENT IS
        CURSOR c_projet_budget IS
            SELECT p.id_projet, p.budget projet_budget, SUM(pp.budget) phases_total
            FROM Projet p
            JOIN Projet_phase pp ON p.id_projet = pp.id_projet
            WHERE p.id_projet MEMBER OF v_projet_ids
            GROUP BY p.id_projet, p.budget;
        v_projet_rec c_projet_budget%ROWTYPE;
    BEGIN
        FOR v_projet_rec IN c_projet_budget LOOP
            IF v_projet_rec.phases_total > v_projet_rec.projet_budget THEN
                RAISE_APPLICATION_ERROR(
                    -20001,
                    'IMPOSSIBLE DE MODIFIER LA PHASE CAR LA SOMME DES BUDGETS DES PHASES DÉPASSE CELUI DU PROJET'
                );
            END IF;
        END LOOP;
    END AFTER STATEMENT;

END T_validerbudget_Update_projetphase;
/

方案说明

• 复合触发器分为行级和语句级两部分：行级收集所有涉及修改的项目ID，语句级在所有行修改完成后，批量查询项目的阶段预算总和并与项目预算对比。
• 这种方式避免了在修改过程中读取变异表，同时保证了数据一致性，也满足了"两个独立触发器"的要求。

内容的提问来源于stack exchange，提问作者Jack