在Oracle中用触发器实现主键功能时遇ORA-00036递归错误求助

错误原因 #

ORA-00036报错是因为触发器陷入了无限递归循环：

• 执行INSERT语句触发tg1触发器（before insert类型）
• 触发器内部又执行insert into gt1，会再次触发同一个tg1触发器
• 这个过程反复触发，直到达到Oracle默认的50层递归上限，抛出错误

另外触发器逻辑还存在两个问题：

• 非空判断用了or，应该改为and——N_prd is not null and N_prd <>''，否则空字符串会绕过非空检查
• 触发器里的insert操作完全多余，before insert触发器的作用是验证或修改:new行的数据，合法数据Oracle会自动完成插入，不需要手动执行插入语句

正确实现方式 #

方式1：用Oracle原生约束（推荐） #

Oracle自带的主键/唯一约束+非空约束，性能比触发器更高，且更可靠：

-- 先删除错误的触发器
drop trigger tg1;

-- 添加非空约束和唯一约束
ALTER TABLE gt1 MODIFY PRODUCT VARCHAR2(2) NOT NULL;
ALTER TABLE gt1 ADD CONSTRAINT uk_gt1_product UNIQUE (PRODUCT);

-- 或者直接将PRODUCT设为主键（主键自带非空+唯一属性）
-- ALTER TABLE gt1 ADD CONSTRAINT pk_gt1_product PRIMARY KEY (PRODUCT);

方式2：修正触发器逻辑（仅作学习参考，不推荐） #

如果一定要用触发器实现，逻辑应该是验证数据合法性，不合法则抛出异常，不要手动插入：

create or replace trigger tg1 before insert on gt1
for each row
declare
    cnt number;
begin
    -- 检查非空
    if :new.product is null or :new.product = '' then
        raise_application_error(-20001, ':new.product - 不能为空');
    end if;

    -- 检查唯一性
    select count(*) into cnt from gt1 where product = :new.product;
    if cnt > 0 then
        raise_application_error(-20002, :new.product || ' - 产品已存在');
    end if;

    -- 验证通过后，Oracle会自动执行插入，无需手动操作
    dbms_output.put_line('插入成功');
end;

测试验证 #

执行插入语句：

INSERT INTO gt1 VALUES ('P1', sysdate + 1, 23);

• 插入重复值时，会抛出自定义异常
• 插入空值时，会抛出异常
• 合法数据则正常插入

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