Using an IF-Clause inside an event

user_13188216 0
Viewed 33

I'm modelling a basic airport management system SQL database using MariaDB. I want to create an event "LastCall" that adds a message to a table every time a flight is departing in exactly 30 minutes.

So far I have written the code:

CREATE TABLE LastCall(
TS  TIMESTAMP,
Message Varchar(100));

DELIMITER $
CREATE EVENT LastCallInsert
ON SCHEDULE EVERY 1 second
    DO
    BEGIN
        INSERT INTO LastCall(TS, Message)
        SELECT
            CURRENT_TIMESTAMP,
            CONCAT("Last call for passengers on flight ",FLIGHT.Flight_ID,". Go to gate ",FLIGHT.GateID)
        FROM FLIGHT
        WHERE TIMEDIFF(FLIGHT.Expected_Time,CURRENT_TIMESTAMP) = "00:30:00" AND FLIGHT.Depart_Arrives = "Departure";
    END; $
DELIMITER ;

And this is the table Flight:

CREATE TABLE Flight(
Flight_ID       varchar(4) not null,
Depart_Arrives  enum('Departure','Arrival'),
AirportID       varchar(3),
AircraftREG     varchar(6) not null,
Expected_Time   datetime, 
GateID          varchar(3),
PRIMARY KEY(Flight_ID,Depart_Arrives),
FOREIGN KEY(AirportID) REFERENCES Airport(AirportID),
FOREIGN KEY(AircraftREG) REFERENCES Aircraft(AircraftREG),
FOREIGN KEY(GateID) REFERENCES Gate(GateID)
);

It gives me the error code:

Error Code: 1054. Unknown column 'Expected_Time' in 'field list'

Any ideas on why it can't understand my reference to Expected_Time?

mariadb sql-insert if-statement sql database
1 Answers

You need to actually SELECT from the FLIGHT table to see if a flight satisfy the conditions - which your IF statement does not do.

I think that you need an INSERT ... SELECT statement instead:

BEGIN
    INSERT INTO LastCall (ts, message)
    SELECT
        CURRENT_TIMESTAMP, 
        CONCAT("Last call for passengers on flight ", FlightID, ". Go to gate ", GateID) 
    FROM Flight
    WHERE 
        Expected_Time >= NOW() - INTERVAL 30 MINUTE
        AND Expected_Time < NOW() - INTERVAL 29 MINUTE;
END;

The SELECT query searches for flights whose expected_time is exactly 30 minutes from now ; you probably want a range check rather than an exact match (now() has seconds). If rows are found, the corresponding information is inserted in the target table.

user_106767160
Related