ORA-04091错误排查：航班预订系统触发器更新flightseat表失败

问题原因 #

你遇到的ORA-04091错误是因为行级触发器中查询了正在被修改的booking表。Oracle会阻止这种操作，因为此时booking表处于"变异"状态——当前事务的修改还未完成，直接查询会导致数据不一致，同时可能引发并发问题。

添加pragma autonomous_transaction无效的原因是：自治事务是独立于当前事务的，它看不到当前事务中对booking表的未提交修改，统计出的预订数还是旧值，无法正确更新座位数。

复合触发器解决方案 #

复合触发器可以在语句级和行级之间切换，我们可以先收集所有被修改的航班ID，在整个DML语句执行完成后，统一计算并更新flightseat表，避免直接查询变异的booking表。

完整代码 #

create or replace trigger tg_update_flightseat
for insert or update or delete on booking
compound trigger
    -- 定义集合存储受影响的flightid
    type t_flight_ids is table of flight.flightid%type;
    v_flight_ids t_flight_ids := t_flight_ids();

    -- AFTER EACH ROW：收集每个被修改的航班ID
    after each row is
    begin
        -- 处理插入、更新（航班ID变化）、删除操作
        if inserting then
            v_flight_ids.extend;
            v_flight_ids(v_flight_ids.count) := :new.flightid;
        elsif updating then
            if :old.flightid != :new.flightid then
                -- 旧航班ID也要统计（因为预订从旧航班转移到新航班）
                v_flight_ids.extend;
                v_flight_ids(v_flight_ids.count) := :old.flightid;
                v_flight_ids.extend;
                v_flight_ids(v_flight_ids.count) := :new.flightid;
            else
                v_flight_ids.extend;
                v_flight_ids(v_flight_ids.count) := :new.flightid;
            end if;
        elsif deleting then
            v_flight_ids.extend;
            v_flight_ids(v_flight_ids.count) := :old.flightid;
        end if;
    end after each row;

    -- AFTER STATEMENT：语句执行完成后统一更新flightseat
    after statement is
        v_totalseats flight.totalnoofseats%type;
        v_noofbookings number;
    begin
        -- 遍历所有受影响的航班ID，去重后处理
        for rec in (select distinct flightid from table(v_flight_ids)) loop
            -- 获取航班总座位数
            select totalnoofseats into v_totalseats
            from flight
            where flightid = rec.flightid;

            -- 统计该航班的有效预订数（这里可以根据业务调整，比如排除取消状态的预订）
            select count(*) into v_noofbookings
            from booking
            where flightid = rec.flightid;

            -- 更新flightseat表
            update flightseat
            set bookedseats = v_noofbookings,
                availableseats = v_totalseats - v_noofbookings
            where flightid = rec.flightid;

            -- 如果flightseat中没有该航班记录，插入新记录（可选，根据业务需求）
            if sql%rowcount = 0 then
                insert into flightseat(flightid, bookedseats, availableseats)
                values(rec.flightid, v_noofbookings, v_totalseats - v_noofbookings);
            end if;
        end loop;
    end after statement;
end tg_update_flightseat;
/

代码逻辑说明 #

• 集合存储受影响航班：定义t_flight_ids集合，在AFTER EACH ROW阶段收集所有被插入、更新、删除操作涉及的航班ID，包括更新时的旧航班ID（因为预订转移会影响两个航班的座位数）。
• 语句结束后统一处理：在AFTER STATEMENT阶段，遍历去重后的航班ID，此时booking表的修改已经全部完成，查询不会触发表变异错误。
• 兼容新增航班记录：如果flightseat中没有对应航班的记录，自动插入新行（可选，根据你的业务是否要求提前初始化flightseat表）。
• 支持删除操作：原触发器只处理了插入和更新，复合触发器补充了删除场景，删除预订时会自动增加可用座位数。

额外优化建议 #

• 如果booking表有"取消状态"的字段（比如status字段标记为'CANCELLED'），统计预订数时需要排除这些记录：

  select count(*) into v_noofbookings
  from booking
  where flightid = rec.flightid
    and status != 'CANCELLED'; -- 根据实际业务字段调整
  

• 可以在flight表上也创建触发器，当总座位数修改时，同步更新flightseat的可用座位数，保证数据一致性。

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