Oracle PL/SQL 11g：插入前触发器实现存在则更新不存在则插入最优方案咨询

嘿Gianluca，针对你在Oracle 11g里要实现“插入前检查记录，不存在则插入、存在则更新”的需求，我来给你梳理下最靠谱的实现方式～

其实Oracle本身就提供了专门处理这类“匹配更新、不匹配插入”场景的语法——MERGE，它比触发器更高效、原子性更强，还能避免触发器带来的各种潜在问题，是首选方案。

举个例子，假设你的表是your_table，主键为id，还有col1、col2两个字段，MERGE的写法如下：

MERGE INTO your_table t
USING (
    SELECT 
        :p_id AS id, 
        :p_col1 AS col1, 
        :p_col2 AS col2 
    FROM DUAL
) s
ON (t.id = s.id) -- 匹配条件：主键相等
WHEN MATCHED THEN
    -- 记录存在，更新字段
    UPDATE SET 
        t.col1 = s.col1, 
        t.col2 = s.col2
WHEN NOT MATCHED THEN
    -- 记录不存在，插入新行
    INSERT (id, col1, col2) 
    VALUES (s.id, s.col1, s.col2);

你可以把这段逻辑封装成存储过程，或者直接在应用层调用，它是原子操作，由Oracle优化器自动处理锁和并发问题，比触发器靠谱得多。

如果业务限制必须通过前置插入触发器来实现，那推荐捕获主键冲突异常的方案，这种方式能避免“检查-操作”的竞态条件（比如多个会话同时插入同一记录的情况），而且不需要用到自治事务，事务一致性更好。

实现步骤： #

1. 确保表有主键或唯一约束（这是判断记录是否存在的基础）：

CREATE TABLE your_table (
    id NUMBER PRIMARY KEY,
    col1 VARCHAR2(50),
    col2 NUMBER
);

2. 创建BEFORE INSERT行级触发器，捕获DUP_VAL_ON_INDEX异常（主键/唯一键冲突时抛出）：

CREATE OR REPLACE TRIGGER trg_insert_or_update_your_table
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    -- 触发器不做额外操作，让原INSERT先执行
    NULL;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        -- 捕获主键冲突，说明记录已存在，执行更新
        UPDATE your_table
        SET col1 = :NEW.col1,
            col2 = :NEW.col2
        WHERE id = :NEW.id;
        -- 异常被处理，原INSERT的失败不会导致整个事务回滚（只要UPDATE成功）
END;
/

为什么不推荐提前检查再更新？ #

如果在触发器里先查询记录是否存在，再决定插入或更新，会有竞态条件：比如两个会话同时查询到记录不存在，然后都执行插入，这时候还是会抛出主键冲突异常，而且为了避免这个问题引入自治事务的话，又会导致事务独立性问题（比如原事务回滚，但触发器里的更新已经提交，造成数据不一致）。所以捕获异常的方案更可靠。

• 必须确保表有主键或唯一约束，否则DUP_VAL_ON_INDEX异常不会触发，MERGE的匹配条件也无法生效；
• MERGE语句的USING子句可以根据实际需求调整，比如传入多个参数或者从其他表取数据；
• 触发器方案中，如果更新操作也可能触发其他触发器，要注意触发器的嵌套调用问题。

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