Oracle SQL触发器无法自动填充Product_Id问题求助

问题场景 #

现有以下数据库表结构及触发器逻辑：

客户维护表 #

CUSTOMER_PRODUCT_TABLE_A和CUSTOMER_PRODUCT_TABLE_B结构一致：

create table CUSTOMER_PRODUCT_TABLE_A
(
Product_Name varchar2(300),
Product_id varchar2(300)
)

create table CUSTOMER_PRODUCT_TABLE_B
(
Product_Name varchar2(300),
Product_id varchar2(300)
)

目标表MY_TABLE #

create table MY_TABLE
(
My_product_name varchar2(300),
My_product_id varchar2(300),
My_product_type varchar2(300)
)

测试数据 #

INSERT ALL
    INTO CUSTOMER_PRODUCT_TABLE_A (Product_Name, Product_id)  
VALUES('Product1-A', 123)
    INTO CUSTOMER_PRODUCT_TABLE_A (Product_Name, Product_id)  
VALUES('Product2-A', 123)
    INTO CUSTOMER_PRODUCT_TABLE_A (Product_Name, Product_id)  
VALUES('Product3-A', 123)

    INTO CUSTOMER_PRODUCT_TABLE_B (Product_Name, Product_id)  
VALUES('Product1-B', 'ABC')   
    INTO CUSTOMER_PRODUCT_TABLE_B (Product_Name, Product_id)  
VALUES('Product2-B', 'DEF')
    INTO CUSTOMER_PRODUCT_TABLE_B (Product_Name, Product_id)  
VALUES('Product3-B', 'GHI')
SELECT 1 FROM DUAL

原触发器代码 #

创建前置插入触发器，意图在插入MY_TABLE时，根据My_product_type从对应客户表匹配My_product_name，自动填充My_product_id：

create or replace TRIGGER  "MY_INSERT_ID_TRIGGER" 
before insert on "MY_TABLE"                
for each row   

DECLARE

begin    

if :NEW.My_Product_Name is null and lower(:NEW.My_product_type) = 'A'
then
    SELECT DISTINCT Product_Id
    INTO :NEW.My_product_id
    FROM CUSTOMER_PRODUCT_TABLE_A
    --To remove special characters and spaces:
    WHERE lower( regexp_replace( replace(Product_name, ' ', '') , '[^a-zA-Z ]') )
    =
    lower( regexp_replace( replace(:NEW.My_Product_name, ' ', '') , '[^a-zA-Z ]') );

elsif  :NEW.PRODUCT is null and lower(:NEW.PRODUCT_TYPE) = 'B'
then

    SELECT DISTINCT Product_Id
    INTO :NEW.My_product_id
    FROM CUSTOMER_PRODUCT_TABLE_B
    --To remove special characters and spaces:
    WHERE lower( regexp_replace( replace(Product_name, ' ', '') , '[^a-zA-Z ]') )
    =
    lower( regexp_replace( replace(:NEW.My_Product_name, ' ', '') , '[^a-zA-Z ]') );

else null;
end if;

end;

问题现象：单独执行触发器内的SELECT语句能正常获取Product_Id，但触发器无法为MY_TABLE.My_product_id赋值，例如插入Product1-A时，My_product_id字段未被填充。

问题原因 #

触发器逻辑存在两处关键错误，导致条件永远不满足，无法执行赋值逻辑：

• 第一个条件判断逻辑颠倒：
  原条件if :NEW.My_Product_Name is null and lower(:NEW.My_product_type) = 'A'完全不符合业务逻辑——插入时My_Product_Name是有值的，需要填充的是My_product_id，应该判断My_product_id是否为空，而非My_Product_Name。
• 第二个条件字段名错误：
  原条件elsif :NEW.PRODUCT is null and lower(:NEW.PRODUCT_TYPE) = 'B'中，PRODUCT和PRODUCT_TYPE并非MY_TABLE的字段名，正确字段应为My_product_id和My_product_type。

修复后的触发器代码 #

修正上述错误后，触发器逻辑即可正常执行：

create or replace TRIGGER  "MY_INSERT_ID_TRIGGER" 
before insert on "MY_TABLE"                
for each row   

begin    
    -- 当My_product_id为空，且类型为A时，从CUSTOMER_PRODUCT_TABLE_A匹配
    if :NEW.My_product_id is null and lower(:NEW.My_product_type) = 'a' then
        SELECT DISTINCT Product_Id
        INTO :NEW.My_product_id
        FROM CUSTOMER_PRODUCT_TABLE_A
        WHERE lower( regexp_replace( replace(Product_name, ' ', '') , '[^a-zA-Z]') )
        = lower( regexp_replace( replace(:NEW.My_product_name, ' ', '') , '[^a-zA-Z]') );
    
    -- 当My_product_id为空，且类型为B时，从CUSTOMER_PRODUCT_TABLE_B匹配
    elsif :NEW.My_product_id is null and lower(:NEW.My_product_type) = 'b' then
        SELECT DISTINCT Product_Id
        INTO :NEW.My_product_id
        FROM CUSTOMER_PRODUCT_TABLE_B
        WHERE lower( regexp_replace( replace(Product_name, ' ', '') , '[^a-zA-Z]') )
        = lower( regexp_replace( replace(:NEW.My_product_name, ' ', '') , '[^a-zA-Z]') );
    end if;
end;

额外优化说明 #

• 原正则表达式[^a-zA-Z ]中保留了空格，但前面已经用replace(Product_name, ' ', '')去掉了空格，因此简化为[^a-zA-Z]，避免冗余处理。
• 去掉了不必要的else null分支，逻辑更简洁。

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