如何在触发器的INSERT语句VALUES中嵌入子查询获取关联行数据？

原行级触发器无法判断是否同时删除了id=3和id=4的行，因为它仅处理单条删除记录，无法感知本次删除操作的整体范围。需要改用复合触发器，结合行级收集与语句级判断来实现需求：

修改后的触发器代码 #

CREATE OR REPLACE TRIGGER LOG_DELETEDPAYMENTS
FOR DELETE ON payments
COMPOUND TRIGGER
    -- 定义集合存储本次删除的所有行ID
    TYPE t_deleted_ids IS TABLE OF NUMBER;
    v_deleted_ids t_deleted_ids := t_deleted_ids();

    -- 行级逻辑：收集每一条被删除行的ID
    BEFORE EACH ROW IS
    BEGIN
        v_deleted_ids.EXTEND;
        v_deleted_ids(v_deleted_ids.LAST) := :OLD.id;
    END BEFORE EACH ROW;

    -- 语句级逻辑：删除操作完成后，判断并执行日志插入
    AFTER STATEMENT IS
        v_invnr payments.invnr%TYPE;
        v_extinvnr payments.extinvnr%TYPE;
    BEGIN
        -- 检查本次删除是否同时包含ID=3和ID=4的行
        IF 3 MEMBER OF v_deleted_ids AND 4 MEMBER OF v_deleted_ids THEN
            -- 根据idparent=id的关联规则，查询对应的父行（行1）的invnr和extinvnr
            -- 假设行3、4的idparent均指向行1的ID，这里取行3的idparent关联查询
            SELECT invnr, extinvnr
            INTO v_invnr, v_extinvnr
            FROM payments
            WHERE id = (SELECT idparent FROM payments WHERE id = 3);

            -- 插入日志表，使用父行的invnr和extinvnr值
            INSERT INTO TABLE_LOG_DELETEDPAYMENTS (
                table_name, invnr, extinvnr, invdate, transactionid, 
                info, createdby, deleted_by, date_of_delete
            ) VALUES (
                'payments', v_invnr, v_extinvnr, NULL, NULL,
                '批量删除ID=3和ID=4的支付记录', NULL,
                sys_context('userenv','OS_USER'), SYSDATE
            );
        END IF;
    END AFTER STATEMENT;
END LOG_DELETEDPAYMENTS;
/

关键说明 #

• 复合触发器分为两部分：行级收集负责记录本次删除的所有行ID，语句级判断在整个删除操作完成后检查是否满足“同时删3、4行”的条件；
• 关联查询部分假设行3、4的idparent字段值对应行1的id，如果行1是固定ID=1的记录，可直接把查询条件改为WHERE id = 1；
• 日志表中invdate、transactionid等字段如果不需要填充被删行的值，可设为NULL，也可根据实际需求补充其他逻辑。

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