如何在触发器INSERT操作中获取同表另一行的invnr值？

我有一张payments表，发票与支付记录通过idparent=id关联。现有如下触发器：

CREATE OR REPLACE TRIGGER UDX_TR_LOG_DELETEDPAYMENTS
BEFORE DELETE ON payments
FOR EACH ROW
BEGIN
IF :old.invnr IS NULL THEN
INSERT INTO UDX_TABLE_LOG_DELETEDPAYMENTS (idaopkopf, table_name, invnr, idparent, extnr, date, transactionid, info, partner, createdby, deleted_by, date_of_delete)
values (:old.id, 'payments', null, :old.idparent, :old.extnr, :old.date, :old.transactionid, :old.info, :old.partner, :old.createdby, sys_context('userenv','OS_USER'), SYSDATE);
END;
END;

需要将INSERT语句中的null替换为从同表查询id=idparent对应行的invnr值，但尝试以下方案均触发ORA-04091等错误：

• 用SELECT替代VALUES
• 在日志表创建单独的AFTER INSERT触发器
• 同一触发器中先执行INSERT再执行UPDATE

测试用表结构及数据 #

日志表UDX_TABLE_LOG_DELETEDPAYMENTS创建语句 #

CREATE TABLE UDX_TABLE_LOG_DELETEDPAYMENTS 
(
id number generated by default as identity,
idaopkopf number(10),
TABLE_NAME VARCHAR2(20),
invnr VARCHAR2(20),
IDPARENT VARCHAR2(20),
extnr VARCHAR2(20),
date DATE,
TRANSACTIONID NUMBER(15),
INFO VARCHAR2(200),
partner number(15),
CREATEDBY VARCHAR2(20),
DELETED_BY VARCHAR2(20),
DATE_OF_DELETE DATE
);

日志表测试数据（修正语法错误后） #

INSERT INTO UDX_TABLE_LOG_DELETEDPAYMENTS (idaopkopf, table_name, invnr, idparent, extnr, date, transactionid, info, partner, createdby, deleted_by, date_of_delete)
VALUES (34042887, 'aopkopf', null, 29335828, null, TO_DATE('22-06-01','RR-MM-DD'), 34042886, null, 3433534, 9083446, 'pesho', SYSDATE);
INSERT INTO UDX_TABLE_LOG_DELETEDPAYMENTS (idaopkopf, table_name, invnr, idparent, extnr, date, transactionid, info, partner, createdby, deleted_by, date_of_delete)
VALUES (34042000, 'aopkopf', null, 29335828, null, TO_DATE('22-01-01','RR-MM-DD'), 34042886, null, 3433534, 9083446, 'sasho', SYSDATE);

payments表结构及测试数据（修正语法错误后） #

CREATE TABLE payments (
id number(15),
idparent number(15),
invnr number(20),
date date);

INSERT INTO payments (id, invnr, date)
VALUES(29335828, 1111112234, TO_DATE('22-01-20','RR-MM-DD'));
INSERT INTO payments (id, invnr, date)
VALUES(29335555, 1555112234, TO_DATE('22-12-14','RR-MM-DD'));

ORA-04091错误是因为触发器执行时，payments表处于变异状态——当前DELETE操作尚未完成，Oracle会阻止行级触发器直接查询正在修改的表，避免读取到不一致的数据。

使用复合触发器分阶段处理，规避变异表限制：

CREATE OR REPLACE TRIGGER UDX_TR_LOG_DELETEDPAYMENTS
FOR DELETE ON payments
COMPOUND TRIGGER
    -- 定义集合存储待处理的删除记录
    TYPE rec_deleted IS RECORD (
        id payments.id%TYPE,
        idparent payments.idparent%TYPE,
        extnr payments.extnr%TYPE,
        date_col payments.date%TYPE,
        transactionid payments.transactionid%TYPE,
        info payments.info%TYPE,
        partner payments.partner%TYPE,
        createdby payments.createdby%TYPE
    );
    TYPE tab_deleted IS TABLE OF rec_deleted INDEX BY PLS_INTEGER;
    g_deleted tab_deleted;
    g_index PLS_INTEGER := 0;

    -- 行级触发：收集需要处理的删除记录
    BEFORE EACH ROW IS
    BEGIN
        IF :old.invnr IS NULL THEN
            g_index := g_index + 1;
            g_deleted(g_index).id := :old.id;
            g_deleted(g_index).idparent := :old.idparent;
            g_deleted(g_index).extnr := :old.extnr;
            g_deleted(g_index).date_col := :old.date;
            g_deleted(g_index).transactionid := :old.transactionid;
            g_deleted(g_index).info := :old.info;
            g_deleted(g_index).partner := :old.partner;
            g_deleted(g_index).createdby := :old.createdby;
        END IF;
    END BEFORE EACH ROW;

    -- 语句级触发：DELETE完成后查询并插入日志
    AFTER STATEMENT IS
        v_invnr payments.invnr%TYPE;
    BEGIN
        FOR i IN 1..g_index LOOP
            -- 此时DELETE已执行完毕，可安全查询payments表
            SELECT invnr INTO v_invnr
            FROM payments
            WHERE id = g_deleted(i).idparent;

            INSERT INTO UDX_TABLE_LOG_DELETEDPAYMENTS (
                idaopkopf, table_name, invnr, idparent, extnr, date, 
                transactionid, info, partner, createdby, deleted_by, date_of_delete
            ) VALUES (
                g_deleted(i).id, 'payments', v_invnr, g_deleted(i).idparent, 
                g_deleted(i).extnr, g_deleted(i).date_col, g_deleted(i).transactionid, 
                g_deleted(i).info, g_deleted(i).partner, g_deleted(i).createdby, 
                sys_context('userenv','OS_USER'), SYSDATE
            );
        END LOOP;
    END AFTER STATEMENT;
END UDX_TR_LOG_DELETEDPAYMENTS;
/

方案说明 #

1. 分阶段处理：
   • BEFORE EACH ROW阶段：仅收集invnr为NULL的删除记录字段，存入内存集合，不直接查询原表。
   • AFTER STATEMENT阶段：整个DELETE语句执行完成后，payments表脱离变异状态，此时可安全查询关联行的invnr并插入日志。
2. 兼容批量操作：集合存储支持多条删除记录，单条或批量删除均可正常处理。
3. 异常处理（可选）：若idparent对应行可能不存在，需添加NO_DATA_FOUND异常处理避免触发器中断：

BEGIN
    SELECT invnr INTO v_invnr
    FROM payments
    WHERE id = g_deleted(i).idparent;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        v_invnr := NULL; -- 或设置自定义默认值
END;

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