使用BEFORE INSERT触发器搭配SELECT ... FOR UPDATE的异常问题解决

表结构与初始数据 #

规则说明：

• 用户1拥有权限2和4，用户2拥有权限4，用户3的permission为null代表拥有全部权限
• 核心规则：若要为用户插入/更新permission为null的记录，必须失败，除非该用户在表中无任何记录；反之，若用户已有permission为null的记录，不能再插入/更新为带具体数值的权限记录

触发器异常场景 #

编写的触发器在单条插入/更新操作时正常工作，但执行SELECT * FROM my_table FOR UPDATE后，删除用户1的一条记录并将另一条的permission改为null时，触发器中的count(*)仍读取旧数据，导致逻辑判断错误。

原触发器代码：

create or replace trigger my_trigger
  before insert or update
  on my_table
  for each row
declare
  w_count number;
PRAGMA AUTONOMOUS_TRANSACTION;  
begin
  if :new.permission is null then
    select count(*)
      into w_count
      from my_table
     where user_id  = :new.user_id  
       and permission is not null;  

    if w_count > 1 then
      --raise error, cannot insert user with null permission, because records with numbered permissions exist. Delete the existing records first

    end if;

  else
    select count(*)
      into w_count
      from my_table
     where user_id  = :new.user_id  
       and permission   is null;
       
    if w_count > 1 then
      --raise error, cannot insert user with numbered permissions, because records with null permission exists. Delete the existing records first.
    end if;
  end if;
end;

原触发器中使用了PRAGMA AUTONOMOUS_TRANSACTION（自治事务），自治事务是完全独立于当前主事务的子事务，无法读取主事务中尚未提交的修改。当你在主事务中执行删除和更新操作后，这些变更还没提交，自治事务查询表时只能看到事务开始前的旧数据，所以count结果完全不符合当前操作后的实际数据状态。

1. 移除自治事务声明：删掉PRAGMA AUTONOMOUS_TRANSACTION;，让触发器在当前主事务的上下文里执行，就能读取到主事务中未提交的实时变更。
2. 修正count判断逻辑：原代码中w_count > 1的条件错误，应该改为w_count > 0——只要存在符合条件的记录就触发错误，而不是要求数量大于1。
3. 补充明确的错误抛出语句：原代码仅留了注释，需要添加RAISE_APPLICATION_ERROR抛出可识别的业务错误信息。

修正后的触发器代码：

create or replace trigger my_trigger
  before insert or update
  on my_table
  for each row
declare
  w_count number;
begin
  if :new.permission is null then
    -- 检查该用户是否已有非null权限记录
    select count(*)
      into w_count
      from my_table
     where user_id = :new.user_id  
       and permission is not null;  

    if w_count > 0 then
      raise_application_error(-20001, 
        '无法设置为全权限（null）：该用户已有具体权限记录，请先删除现有记录');
    end if;

  else
    -- 检查该用户是否已有全权限（null）记录
    select count(*)
      into w_count
      from my_table
     where user_id = :new.user_id  
       and permission is null;
       
    if w_count > 0 then
      raise_application_error(-20002, 
        '无法设置具体权限：该用户已有全权限（null）记录，请先删除现有记录');
    end if;
  end if;
end;
/

• 对于FOR UPDATE后的批量修改/删除操作，触发器会逐行触发，移除自治事务后，触发器能看到当前事务中已经完成的行变更（比如先删除了用户1的一条记录，后续更新另一条时，count会正确统计剩余的非null权限记录）。
• 如果需要处理删除操作的联动逻辑（比如删除用户最后一条非null权限记录后允许插入null权限），当前触发器已覆盖该场景——删除操作不会触发before insert or update触发器，后续插入时会正确判断用户是否还有其他权限记录。

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