DELETE触发器关联多发票transactionid时无法记录发票号的解决方法

当前触发器直接从被删除的payments行提取发票字段，但一个transactionid对应多笔发票时，payments表的单条记录可能只关联部分发票（甚至不存储发票信息），导致日志中发票字段为NULL。要解决这个问题，需要通过transactionid关联到发票表，获取该交易下的所有发票后再逐条写入日志。

修改后的触发器代码 #

CREATE OR REPLACE TRIGGER LOG_DELETEDPAYMENTS
BEFORE DELETE ON payments
FOR EACH ROW
DECLARE
    -- 声明游标，根据当前删除的transactionid查询所有关联发票
    CURSOR cur_invoices IS
        SELECT invnr, extinvnr, invdate
        FROM invoices -- 替换为你的实际发票表名
        WHERE transactionid = :old.transactionid;
    v_invnr invoices.invnr%TYPE;
    v_extinvnr invoices.extinvnr%TYPE;
    v_invdate invoices.invdate%TYPE;
BEGIN
    IF :old.type IN(2, 3) THEN 
        -- 遍历当前交易下的所有发票
        OPEN cur_invoices;
        LOOP
            FETCH cur_invoices INTO v_invnr, v_extinvnr, v_invdate;
            EXIT WHEN cur_invoices%NOTFOUND;
            
            -- 为每个发票单独插入一条删除日志
            INSERT INTO TABLE_LOG_DELETEDPAYMENTS (
                table_name, invnr, extinvnr, invdate, transactionid, 
                info, createdby, deleted_by, date_of_change
            ) VALUES (
                'payments', v_invnr, v_extinvnr, v_invdate, :old.transactionid, 
                :old.info, :old.createdby, sys_context('userenv','OS_USER'), SYSDATE
            );
        END LOOP;
        CLOSE cur_invoices;
        
        -- 如果当前交易没有关联发票，仍按原逻辑记录付款删除信息
        IF cur_invoices%ROWCOUNT = 0 THEN
            INSERT INTO TABLE_LOG_DELETEDPAYMENTS (
                table_name, invnr, extinvnr, invdate, transactionid, 
                info, createdby, deleted_by, date_of_change
            ) VALUES (
                'payments', :old.invnr, :old.extinvnr, :old.invdate, :old.transactionid, 
                :old.info, :old.createdby, sys_context('userenv','OS_USER'), SYSDATE
            );
        END IF;
    END IF;
END;
/

关键说明 #

• 替换代码中的invoices为你数据库中实际的发票表名，确保字段名和实际表结构匹配
• 修正了原代码中 :old:transactionid的语法错误
• 触发器会遍历当前transactionid下的所有发票，每一条发票对应一条日志记录，确保所有关联发票都被完整记录
• 如果交易没有关联发票，仍会保留原逻辑插入付款的删除日志（发票字段沿用payments表原有值）

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