Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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!

share|improve this question
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)? – dezso Nov 12 '12 at 10:40
yes, InnoDB is the engine. I insert by fixed SQL queries and manual. no procedures yet. – Don Angelo Annoni Nov 12 '12 at 18:34

2 Answers

up vote 1 down vote accepted 
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

share|improve this answer
thank you men, this is the most complete answer that I ever had received, thank you a lot!!! – Don Angelo Annoni Nov 12 '12 at 21:43
up vote 1 down vote

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.

share|improve this answer
I have added the constraints witch I have needed at single table level. Now I'm gonna try your suggestion, thanks – Don Angelo Annoni Nov 12 '12 at 18:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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