SQL: negative autoincrement?

I want to decrease automatically the value available_seats in the FLIGHT table each time a new row en开发者_如何学Pythontry 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?

---

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 COUNT(*) FROM FLIGHT_PLAN where PLAN_NUMBER = ?

  SELECT total_seats FROM FLIGHT;

  ...

  $available_seats - $total_seats - $occupied_seats;

---

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 ).

---

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((
    SELECT count(*) FROM FLIGHT_PLAN P
    WHERE P.flight_number = F.flight_number
    ), 0) as AvailableSeats
FROM FLIGHT F
INNER JOIN PLANE_MODEL M ON M.model_id=F.model_id

---

I'd prefer a SQL proc i.e. increment and decrement (and no doubt other data) within a single transaction.

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

Alter Table FLIGHT Add
  Constraint FLIGHT_AVAIL_SEATS_CHK
  Check (available_seats >= 0);