我正在尝试编写一个触发器,在插入的情况下将执行以下操作:
1)计算飞机上的座位数
2)计算已经乘坐此航班的乘客人数
3)比较这些值并在表中插入新行或引发错误,指出飞机上不再有空座位。
这是我的代码:
DELIMITER //
USE AIRPORT;
CREATE TRIGGER CHECK_FOR_PLACES
BEFORE INSERT ON TICKET
FOR EACH ROW
BEGIN
DECLARE NUM_OF_PLACES INT;
DECLARE NUM_OF_PASSENGERS INT;
SET NUM_OF_PLACES := (SELECT CAPACITY
FROM AIRPLANE
INNER JOIN FLIGHT ON FLIGHT.ID_AIRPLANE = AIRPLANE.ID_AIRPLANE
WHERE FLIGHT.ID_FLIGHT = NEW.ID_FLIGHT);
SET NUM_OF_PASSENGERS := (SELECT COUNT(*)
FROM TICKET
WHERE TICKET.ID_FLIGHT = NEW.ID_FLIGHT);
IF NUM_OF_PASSENGERS >= NUM_OF_PLACES THEN
SIGNAL SQLSTATE '-20000' SET MESSAGE_TEXT = 'NO MORE PLACES ON THIS FLIGHT';
END IF;
END//
DELIMITER ;
尝试编译脚本后,出现以下错误:
Error Code: 2014. Commands out of sync; you can't run this command now
我不知道此错误的原因可能是什么。我将不胜感激任何帮助。提前致谢
use airport
是一个单独的语句,应该用//结尾,因为您更改了分隔符:
DELIMITER //
USE AIRPORT//
...