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.