Oracle触发器插入更新正常触发，删除操作不触发问题求助

首次创建触发器，参考同类问题仍无法解决当前故障，特求助。现有两张表：

Bill表建表语句：

Create Table Bill
(
    Bill_Number     Number(6,0)  primary key,                                                  
    Paid_YN         Char(1),             
    Posted_YN       Char(1)             
 );

Bill_Item表建表语句：

Create Table Bill_Item
(
    Bill_Number         Number(6,0)   References Bill (Bill_Number),                                          
    Menu_Item_Number    Number(5,0),
    Quantity_Sold       Number(3,0),
    Selling_Price       Number(6,2)
);

需求：创建一个触发器，当Bill表的Paid_YN或/和Posted_YN为'Y'时，阻止对Bill_Item表的插入、更新或删除操作，并提示对应的操作类型及原因（已付款、已过账或两者皆是）。

当前问题：编写的触发器在插入和更新操作时正常工作，但删除操作时不触发，代码如下：

CREATE OR REPLACE TRIGGER TR_NO_POST
BEFORE INSERT OR UPDATE OR DELETE ON Bill_Item
FOR EACH ROW
BEGIN
    SELECT Paid_YN, Posted_YN
    INTO V_Paid_YN, V_Posted_YN
    FROM Bill 
    WHERE Bill_Number = :NEW.Bill_Number;        
    IF inserting THEN            
        IF V_Paid_YN = 'Y' AND V_Posted_YN = 'N' THEN
                RAISE_APPLICATION_ERROR(-20001, 'Bill has been paid. Cannot add more items!');
        ELSIF V_Posted_YN = 'Y' AND V_Paid_YN = 'N'THEN
                RAISE_APPLICATION_ERROR(-20002, 'Bill has been posted. Cannot add more items!');
        ELSIF V_Paid_YN = 'Y' AND V_Posted_YN = 'Y' THEN
                RAISE_APPLICATION_ERROR(-20003, 'Bill has been paid and posted. Cannot add more items!');
        END IF;                    
    ELSIF updating THEN
        IF V_Paid_YN = 'Y' AND V_Posted_YN = 'N' THEN
                RAISE_APPLICATION_ERROR(-20011, 'Bill has been paid. Cannot change!');
        ELSIF V_Posted_YN = 'Y' AND V_Paid_YN = 'N'THEN
                RAISE_APPLICATION_ERROR(-20022, 'Bill has been posted. Cannot change!');
        ELSIF V_Paid_YN = 'Y' AND V_Posted_YN = 'Y' THEN
                RAISE_APPLICATION_ERROR(-20033, 'Bill has been paid and posted. Cannot change!');
        END IF;  
    ELSIF deleting THEN
        IF V_Paid_YN = 'Y' AND V_Posted_YN = 'N' THEN
                RAISE_APPLICATION_ERROR(-20111, 'Bill has been paid. Cannot delete!');
        ELSIF V_Posted_YN = 'Y' AND V_Paid_YN = 'N'THEN
                RAISE_APPLICATION_ERROR(-20222, 'Bill has been posted. Cannot delete!');
        ELSIF V_Paid_YN = 'Y' AND V_Posted_YN = 'Y' THEN
                RAISE_APPLICATION_ERROR(-20333, 'Bill has been paid and posted. Cannot delete!');
        END IF;  
    END IF;    
END TR_NO_POST;

问题原因 #

删除操作时，:NEW.Bill_Number为空值——删除操作针对已存在的行，触发器中只能通过:OLD获取该行的原始数据。原代码查询条件用:NEW.Bill_Number，删除时该值不存在，导致查询不到数据，后续判断逻辑无法执行，触发器自然不会触发拦截。

另外原代码未声明V_Paid_YN和V_Posted_YN变量，这在Oracle中会直接报错，属于隐藏问题。

修复后的触发器代码 #

优化逻辑，区分操作类型获取对应Bill_Number，同时统一错误判断逻辑：

CREATE OR REPLACE TRIGGER TR_NO_POST
BEFORE INSERT OR UPDATE OR DELETE ON Bill_Item
FOR EACH ROW
DECLARE
    V_Paid_YN    Bill.Paid_YN%TYPE;
    V_Posted_YN  Bill.Posted_YN%TYPE;
    v_operation  VARCHAR2(10);
BEGIN
    -- 根据操作类型获取对应Bill_Number并查询状态
    CASE
        WHEN inserting THEN
            v_operation := 'add';
            SELECT Paid_YN, Posted_YN INTO V_Paid_YN, V_Posted_YN
            FROM Bill WHERE Bill_Number = :NEW.Bill_Number;
        WHEN updating THEN
            v_operation := 'change';
            SELECT Paid_YN, Posted_YN INTO V_Paid_YN, V_Posted_YN
            FROM Bill WHERE Bill_Number = :NEW.Bill_Number;
        WHEN deleting THEN
            v_operation := 'delete';
            SELECT Paid_YN, Posted_YN INTO V_Paid_YN, V_Posted_YN
            FROM Bill WHERE Bill_Number = :OLD.Bill_Number;
    END CASE;

    -- 判断状态并抛出对应错误
    IF V_Paid_YN = 'Y' AND V_Posted_YN = 'N' THEN
        RAISE_APPLICATION_ERROR(-20001, 'Bill has been paid. Cannot ' || v_operation || '!');
    ELSIF V_Posted_YN = 'Y' AND V_Paid_YN = 'N' THEN
        RAISE_APPLICATION_ERROR(-20002, 'Bill has been posted. Cannot ' || v_operation || '!');
    ELSIF V_Paid_YN = 'Y' AND V_Posted_YN = 'Y' THEN
        RAISE_APPLICATION_ERROR(-20003, 'Bill has been paid and posted. Cannot ' || v_operation || '!');
    END IF;
END TR_NO_POST;

也可以用COALESCE函数简化Bill_Number的获取逻辑：

CREATE OR REPLACE TRIGGER TR_NO_POST
BEFORE INSERT OR UPDATE OR DELETE ON Bill_Item
FOR EACH ROW
DECLARE
    V_Paid_YN    Bill.Paid_YN%TYPE;
    V_Posted_YN  Bill.Posted_YN%TYPE;
    v_operation  VARCHAR2(10);
BEGIN
    -- 自动选择非空的Bill_Number：插入/更新用NEW，删除用OLD
    SELECT Paid_YN, Posted_YN INTO V_Paid_YN, V_Posted_YN
    FROM Bill WHERE Bill_Number = COALESCE(:NEW.Bill_Number, :OLD.Bill_Number);

    v_operation := CASE WHEN inserting THEN 'add' WHEN updating THEN 'change' WHEN deleting THEN 'delete' END;

    -- 判断状态并抛出错误
    IF V_Paid_YN = 'Y' AND V_Posted_YN = 'N' THEN
        RAISE_APPLICATION_ERROR(-20001, 'Bill has been paid. Cannot ' || v_operation || ' more items!');
    ELSIF V_Posted_YN = 'Y' AND V_Paid_YN = 'N' THEN
        RAISE_APPLICATION_ERROR(-20002, 'Bill has been posted. Cannot ' || v_operation || ' more items!');
    ELSIF V_Paid_YN = 'Y' AND V_Posted_YN = 'Y' THEN
        RAISE_APPLICATION_ERROR(-20003, 'Bill has been paid and posted. Cannot ' || v_operation || ' more items!');
    END IF;
END TR_NO_POST;

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