Oracle SQL插入触发器开发需求：处理重复联系人并更新OWNER字段

针对你的CONTACTS表需求，以下是满足三种场景的插入触发器实现：

CREATE OR REPLACE TRIGGER TRG_CONTACTS_INSERT
BEFORE INSERT ON CONTACTS
FOR EACH ROW
DECLARE
    v_existing_name CONTACTS.NAME%TYPE;
    v_existing_owner CONTACTS.OWNER%TYPE;
BEGIN
    -- 查询是否存在相同手机号的记录
    SELECT NAME, OWNER
    INTO v_existing_name, v_existing_owner
    FROM CONTACTS
    WHERE PHONE = :NEW.PHONE;

    -- 存在相同手机号的情况，进入分支处理
    IF v_existing_name != :NEW.NAME THEN
        -- 场景3：手机号存在但姓名不同，抛出错误
        RAISE_APPLICATION_ERROR(-20001, '手机号已关联其他联系人姓名，无法插入');
    ELSE
        -- 姓名+手机号组合已存在，检查OWNER是否不同
        IF v_existing_owner != :NEW.OWNER THEN
            -- 场景2：拼接新旧OWNER并更新原记录
            UPDATE CONTACTS
            SET OWNER = v_existing_owner || ',' || :NEW.OWNER
            WHERE PHONE = :NEW.PHONE AND NAME = :NEW.NAME;
            -- 终止当前插入操作，避免触发唯一约束
            RAISE_APPLICATION_ERROR(-20002, '已更新现有记录的OWNER字段，本次插入已取消');
        ELSE
            -- 完全重复的记录，无需操作
            RAISE_APPLICATION_ERROR(-20003, '该联系人记录已存在，无需重复插入');
        END IF;
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 场景1：无相同手机号，正常执行插入
        NULL;
END;
/

场景逻辑说明 #

• 场景1：全新手机号插入
  触发器查询不到相同PHONE的记录，触发NO_DATA_FOUND异常，直接执行原插入语句，完成正常插入。

• 场景2：Name+Phone组合相同但OWNER不同
  找到匹配的记录后，判断OWNER不一致，执行UPDATE语句拼接新旧OWNER，随后抛出异常终止插入操作（避免触发(Name,Phone)的唯一约束）。

• 场景3：PHONE存在但NAME不同
  找到相同PHONE但NAME不同的记录，直接抛出自定义错误，阻止插入，符合PHONE字段的唯一约束要求。

可选优化点 #

如果需要避免OWNER重复拼接（比如多次插入同一个OWNER的情况），可以修改UPDATE部分的逻辑，先判断OWNER是否已存在：

IF INSTR(v_existing_owner, :NEW.OWNER) = 0 THEN
    UPDATE CONTACTS
    SET OWNER = v_existing_owner || ',' || :NEW.OWNER
    WHERE PHONE = :NEW.PHONE AND NAME = :NEW.NAME;
    RAISE_APPLICATION_ERROR(-20002, '已更新现有记录的OWNER字段，本次插入已取消');
ELSE
    RAISE_APPLICATION_ERROR(-20004, '该OWNER已关联此联系人，无需更新');
END IF;

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