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!

  • 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)? Nov 12, 2012 at 10:40
  • yes, InnoDB is the engine. I insert by fixed SQL queries and manual. no procedures yet. Nov 12, 2012 at 18:34

2 Answers 2

CREATE TRIGGER Bookings_BeforeInsert BEFORE INSERT ON Bookings
    DECLARE found_flight,dummy,baddata INT;
    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; $$  

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

  • thank you men, this is the most complete answer that I ever had received, thank you a lot!!! Nov 12, 2012 at 21:43

SQL Server Answer

CREATE TRIGGER FightNumberExists ON dbo.Bookings
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   
    RAISERROR ('Flight Number does not exist', 16, 1);



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.

  • I have added the constraints witch I have needed at single table level. Now I'm gonna try your suggestion, thanks Nov 12, 2012 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.