Oracle在插入触发器之前引用另一个表中的字段

Question

我只是在Oracle中玩触发器，需要一些指导。

我试图创建一个触发器，以防止对坚果过敏的客户在家庭食品配送数据库中插入或添加某些膳食偏好，但我需要引用一个单独的表来做到这一点，我弄混了。

编译时会显示以下错误信息(对于像我这样的新手来说没有任何意义):p

LS-00103:遇到符号"Nuts"当期望下列情况之一时:not null of nan infinite悬挂空

我的表是:

Customer_allergy (CustomerID, allergy)
Customer_meal_preference (CustomerID, Preference)

我的尝试:

CREATE OR REPLACE TRIGGER AllergyCheck
BEFORE INSERT ON Customer_meal_preference 
FOR EACH ROW
DECLARE
v_CustomerAllergy VARCHAR(20);
BEGIN
SELECT CA.allergy
INTO v_CustomerAllergy
FROM Customer_allergy CA, Customer_meal_preference CP
WHERE CA.CustomerID = CP.CustomerID;

IF :new.preference = 4 AND (v_CustomerAllergy is 'Nuts') THEN RAISE_APPLICATION_ERROR(-20002, 'Customer is allergic to nuts');
END IF;
END AllergyCheck;
/

Answer 1

调整后代码:

CREATE OR REPLACE TRIGGER AllergyCheck
BEFORE INSERT ON Customer_meal_preference 
FOR EACH ROW
DECLARE
v_CustomerAllergy VARCHAR(20);
BEGIN
SELECT CA.allergy
INTO v_CustomerAllergy
FROM Customer_allergy CA
INNER JOIN Customer_meal_preference CP ON CA.CustomerID = CP.CustomerID
WHERE CP.CustomerID = :new.CustomerID; -- for the particular customer

IF :new.preference = 4 AND v_CustomerAllergy = 'Nuts' -- = instead of is
THEN 
RAISE_APPLICATION_ERROR(-20002, 'Customer is allergic to nuts');
END IF;
END AllergyCheck;
/