Skip to content
Advertisement

SQL IF statements in transactions

I’m trying to make a condition where if the number of available seats within a given plane is less than zero then ROLLBACK. However, I am receiving a message stating that there is a syntax error at IF (i.e. “syntax error at or near “IF””). Why is this, can I not use if statements in transactions? And if so, how can I perform this conditional statement?

BEGIN;

UPDATE FlightBooking SET NumSeats = NumSeats + 100, TotalCost = TotalCost + 100
WHERE CustomerID = 20005;

IF (SELECT Available_Seats FROM checkAvailability(30001) < 0) THEN
  ROLLBACK;
END IF;

COMMIT;

Many thanks, callum

Advertisement

Answer

Use a check constraint!

alter table flightbooking add constraint chk_flightbooking_availability
    check (availability >= 0);

That way, the update simply fails and the data never fails to meet your condition.

Alas, if you want to use your function, you need to convert it to a scalar value:

alter table flightbooking add constraint chk_flightbooking_availability
    check (checkAvailability_scalar(30001) >= 0);

That said, it is probably better to store that value somewhere and use a check constraint on that table.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement