How to make a trigger that will compare input with a value of other table?

Question

I had made two tables with the following fields:

TABLE 1: "Bookings"
 FIELD: "fk_flight_number"
 FIELD: "date_of_reservation" 
TABLE 2: "Flights"
 FIELD: "flight_number"
 FIELD: "date_of_departure"

I want to add a condition, just for the consistency of my data. I want to check ON INSERT:

1) in Bookings - fk_flight_number must exists in Flights

2) date_of_reservation must be smaller (<) from date_of_departure.

How to do that? I have read about something similar and the keyword was TRIGGER. I think in my case it must be the same. Can you show me how to refer the second table? The logical conditions are not important - I can handle it. I need some snippet, because I don't know how to start writing the TRIGGER I need for. Or If you know some article describing similar problem - I will appreciate it too. Thank a lot. Of cource I'm not 100% sure that the best approach is TRIGGER. Any other advice will be appreciated too. One more time - thank you!

• András Váczi: Which storage engine do you use? With InnoDB, if you define fk_flight_number as a foreign key on Flights.flight_number then the first condition will be satisfied: it will be assured by InnoDB. Regarding the second one: how do you insert data into Bookings (stored proc, fixed SQL from an application or sometimes manually as well)?
• Don Angelo Annoni: yes, InnoDB is the engine. I insert by fixed SQL queries and manual. no procedures yet.

Answer 1

DELIMITER $$ 
CREATE TRIGGER Bookings_BeforeInsert BEFORE INSERT ON Bookings
FOR EACH ROW 
BEGIN 
 DECLARE found_flight,dummy,baddata INT;
 DECLARE dt DATETIME;
 SET baddata = 1;
 SELECT COUNT(1) INTO found_flight FROM Flights
 WHERE flight_number = NEW.fk_flight_number;
 IF found_flight > 0 THEN
 SELECT date_of_departure INTO dt FROM Flights
 WHERE flight_number = NEW.fk_flight_number;
 IF date_of_reservation < date_of_departure THEN
 SET baddata = 0;
 END IF;
 END IF;
 IF baddata = 1 THEN 
 SELECT CONCAT('Cannot Insert This Because Age ',NEW.age,' is Invalid') 
 INTO dummy FROM information_schema.tables;
 END IF; 
END; $$ 
DELIMITER ;

The IF statement IF baddata = 1 THEN was made a bogus message on purpose. That's because MySQL error handling for Stored Procedures is not designed properly. This crazy workaround I got from Pages 254-256 of the book MySQL Stored Procedure Programming

enter image description here

under the subheading 'Validating Data with Triggers':

I have written about this style of Trigger Programming in my past posts

• Apr 25, 2011 : Trigger in MySQL to prevent insertion
• Dec 23, 2011 : check constraint does not work?

• Don Angelo Annoni: thank you men, this is the most complete answer that I ever had received, thank you a lot!!!

Answer 2

SQL Server Answer

CREATE TRIGGER FightNumberExists ON dbo.Bookings
AFTER INSERT
AS
IF EXISTS (SELECT fk_flight_number
 FROM inserted i
 INNER JOIN Flights f 
 ON f.flight_number = i.fk_flight_number
 WHERE i.date_of_reservation < f.date_of_departure 
 )
BEGIN
 RAISERROR ('Flight Number does not exist', 16, 1);
 ROLLBACK TRANSACTION;
 RETURN 
END;
GO

Notes In SQL Server the values are first loaded into the 'inserted' or 'deleted' table before being written to the physical table.

You can accomplish this with adding constraints to the tables as well.

• Don Angelo Annoni: I have added the constraints witch I have needed at single table level. Now I'm gonna try your suggestion, thanks