"ORA-04091 - Table "b" is mutating, trigger/function may not see it" when updating table "a" based on inserts, updates, and deletes from table "b"

Question

The problem:

I'm trying to create a trigger that updates the values in a table (a) based on the output of another table (b). It's a flight booking system and there are three tables to be used - flight (flight details - flight no, origin and departure airport, total number of seats), booking (booking details - passenger, traveldate, departure and arrival airport, etc.), and flightseat (details - available seats, booked seats, etc.). The flightseat table is linked to the flight table with the flightid fk. Every booking reduces the available seats by 1 and vice versa. I tried creating a trigger to handle this but got the error "ORA-04091".

`

create or replace trigger tg_update_flightseat 
after insert or update on booking 
for each row 
declare 
--pragma autonomous_transaction;
    v_totalseats flight.totalnoofseats%type;
    v_noofbookings number;
    v_availableseats number; 
    v_bookedseats number;
begin 
    select totalnoofseats into v_totalseats 
        from flight
        where flightid = :new.flightid; 
    dbms_output.put_line('the total seats for ' || :new.flightid || ' ' || v_totalseats);
    select count(*) into v_noofbookings
        from booking
        where flightid = :new.flightid; 
    dbms_output.put_line('the total bookings for ' || :new.flightid || ' ' || v_noofbookings);
    v_bookedseats := v_noofbookings;
    v_availableseats := v_totalseats - v_noofbookings;
    update flightseat 
        set availableseats = v_availableseats, 
            bookedseats = v_bookedseats
        where flightid = :new.flightid; 
end; 
/

`

Error report:
ORA-04091: table x.BOOKING is mutating, trigger/function may not see it ORA-06512: at "x.TG_UPDATE_FLIGHTSEAT", line 11 ORA-04088: error during execution of trigger 'x.TG_UPDATE_FLIGHTSEAT'

I have tried adding the pragma autonomous_transaction; but that did not work. A compound trigger seems like the solution but I need some more guidance on how that'll look in this instance.

Answer 1

Write it as a COMPOUND trigger, in the "after each row is" collecting the info you all need in the "after statement is": e.g. the flight_id and the totalnoofseats. Then in "after statement is" section you will be able to "select from booking" and "update flightseat" without getting ORA-04091. You will have to declare some "table of ... index by binary_integer" to collect the flight_id and the totalnoofseats.