如何编写包含返回多值子查询的Trigger，实现主键重复自定义报错

你之前的代码报错是因为PL/SQL不支持IF 变量 EXISTS(子查询)这种语法，EXISTS关键字只能直接跟子查询，且需要把匹配条件写在子查询内部。

调整后的触发器代码（兼容所有Oracle版本） #

CREATE OR REPLACE TRIGGER ad_vegetarian
INSTEAD OF INSERT ON Retete_vegetariane
FOR EACH ROW
DECLARE
    v_exists NUMBER;
BEGIN
    -- 检查目标reteta_id是否已存在
    SELECT COUNT(*) 
    INTO v_exists 
    FROM reteta 
    WHERE reteta_id = :NEW.reteta_id
    AND ROWNUM = 1; -- 匹配到第一条就停止查询，优化性能

    IF v_exists > 0 THEN
        RAISE_APPLICATION_ERROR(-20512, 'Reteta_id  already exists');
    END IF;

    -- 原有插入逻辑保持不变
    INSERT INTO categorie(categ_id, tip)
    VALUES(:NEW.categ_id, :NEW.tip);

    INSERT INTO Ingredient(ingred_id, ingredient)
    VALUES(:NEW.ingred_id, :NEW.ingredient);

    INSERT INTO Reteta(reteta_id, nume, descriere, categ_id, vegetariana, timp_preparare, portii)
    VALUES(:NEW.reteta_id, :NEW.reteta, :NEW.descriere, :NEW.categ_id,'D', :NEW.timp_preparare, :NEW.portii);
    
    INSERT INTO Set_Ingrediente(reteta_id, ingred_id, cantitate, um, comentarii)
    VALUES (:NEW.reteta_id, :NEW.ingred_id, :NEW.cantitate, :NEW.um, :NEW.comentarii);
END;
/

Oracle 12c及以上版本简化写法 #

12c及更高版本支持直接在IF条件中使用EXISTS，不需要额外定义变量：

CREATE OR REPLACE TRIGGER ad_vegetarian
INSTEAD OF INSERT ON Retete_vegetariane
FOR EACH ROW
BEGIN
    -- 直接使用EXISTS判断
    IF EXISTS(SELECT 1 FROM reteta WHERE reteta_id = :NEW.reteta_id) THEN
        RAISE_APPLICATION_ERROR(-20512, 'Reteta_id  already exists');
    END IF;

    -- 原有插入逻辑保持不变
    INSERT INTO categorie(categ_id, tip)
    VALUES(:NEW.categ_id, :NEW.tip);

    INSERT INTO Ingredient(ingred_id, ingredient)
    VALUES(:NEW.ingred_id, :NEW.ingredient);

    INSERT INTO Reteta(reteta_id, nume, descriere, categ_id, vegetariana, timp_preparare, portii)
    VALUES(:NEW.reteta_id, :NEW.reteta, :NEW.descriere, :NEW.categ_id,'D', :NEW.timp_preparare, :NEW.portii);
    
    INSERT INTO Set_Ingrediente(reteta_id, ingred_id, cantitate, um, comentarii)
    VALUES (:NEW.reteta_id, :NEW.ingred_id, :NEW.cantitate, :NEW.um, :NEW.comentarii);
END;
/

内容的提问来源于stack exchange，提问作者Székely Róka