I have the following table structure:

Product_Name    Product_Id    Product_Type
Car             123           A
House           ABC           B
Ball            UZY           B

and so on... Product Id comes from two different Customer fed Tables (table A and table B, both with Product_Name and Product_id). Once they create a new row (insert) on any of their tables a sequence add a new (random) Product_Id. I have a trigger that basically tries to look for that Product Id when a new row gets inserted on my table (for audit, reporting, etc purpose tables):

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;

My table structure as follows:

My_product_name    My_product_id    My_product_type

Both select work fine where run independently (out of the trigger), however, the trigger itself doesn't bring any value. Anybody knows why?
Thanks
This would be the SQL structure:

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)
)

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

So Inserting 'Product1-A' into My_Table would return its Id as per the trigger (into my table column my_product_id).
Thanks