Take the 2-minute tour ×

I want to decrease automatically the value available_seats in the FLIGHT table each time a new row entry is added to the FLIGHT_PLAN table

FLIGHT_PLAN is defined as:

FLIGHT_PLAN (plan_number NUMBER, flight_number NUMBER)

and FLIGHT is defined as

FLIGHT (flight_number NUMBER, available_seats NUMBER) 

Should I do this with a stored procedure, a trigger?

share|improve this question
add comment

4 Answers

If you do, you're storing the same data multiple times in the DB, which is an optimisation. Are you sure you need the optimisation?

  • If you do, look up triggers. You probably want to trigger on create, update and delete of the FLIGHT_PLAN table to adjust the FLIGHT table.

  • If you don't need the optimisation or you're not yet sure it's required, don't optimise. You can put together a quick query for the number of allocated seats and calculate the remaining_seats value you want from that:


    SELECT total_seats FROM FLIGHT;


    $available_seats - $total_seats - $occupied_seats;

share|improve this answer
"Are you sure you need the optimisation?" -- very good point but I'd prefer a SQL proc i.e. increment and decrement (and no doubt other data) within a single transaction. –  onedaywhen Jan 18 '11 at 11:47
A strong argument in favor of normalization is concurrency: it would be very easy for the denormalized seat count to become incorrect if the same flight is modified by two parallel processes. The only way to prevent this is to serialize the transactions, which would impact scalability. The normalized approach, as suggested by @ijw and @cyberkiwi, removes the need for serialization and ensures that the derived value is always correct. –  Allan Jan 18 '11 at 22:41
add comment

I'd handle the whole process in a stored procedure. Much easier to read and manage the necessary locking (ie ensure that you don't get negative available seats when person A adds a flight-plan at exactly the same time as person B ).

share|improve this answer
add comment

In conjunction with your other question here I would recommend you keep the data normalised.

FLIGHT_PLAN (plan_number NUMBER, flight_number NUMBER)
FLIGHT (flight_number NUMBER, model_id NUMBER) 
PLANE_MODEL(NUMBER model_id, NUMBER capacity)

Add the model_id into the FLIGHT which would give you the capacity for the flight through a join. To find out how many seats are available, you can use a query:

SELECT F.flight_number, M.capacity, M.capacity - COALESCE((
    WHERE P.flight_number = F.flight_number
    ), 0) as AvailableSeats
INNER JOIN PLANE_MODEL M ON M.model_id=F.model_id
share|improve this answer
+1 here despite this being a competing answer because you've incorporated PLANE_MODEL from the other question. –  ijw Jan 19 '11 at 12:09
add comment


With a trigger on FLIGHT_PLAN you could solve it. And the following check makes it round:

Alter Table FLIGHT Add
  Check (available_seats >= 0);
share|improve this answer
add comment

Your Answer


