Trigger in MySQL to prevent insertion

Question

I want to create the trigger which prevents insertion in the reservation table of flights (numflights) which does not exist.

Tables
----------------------------------------------
flights(numflight, origine, destination, dep, arr)
airports(code, city, country)
reservation(people, numflight, date_travel)

The columns origine and destination contain airport codes.

Answer 1

The following example is based on a jerry-rigged error trapping method in triggers from Chapter 11, Pages 254-256 of the book MySQL Stored Procedure Programming under the subheading 'Validating Data with Triggers':

DELIMITER $$

CREATE TRIGGER reservation_bi BEFORE INSERT ON reservation FOR EACH ROW
BEGIN

DECLARE found_count,dummy INT;

SELECT COUNT(1) INTO found_count FROM flights WHERE numflight=new.numflight;
IF found_count = 0 THEN
    SELECT 'Cannot Insert This Reservation Because Flight Number is Invalid'
    INTO dummy FROM reservation WHERE numflight=new.numflight;
END IF;

END; $$

DELIMITER ;

If there is other validation to be done, follow this same error trapping method!!!

UPDATE

The reason the book suggests preempting the Trigger in this manner stems from the fact that the MySQL Stored Procedure Language did not have SIGNAL implemented into the language (of course, SIGNAL is ANSI standard).

The authors of the book created work arounds by calling on SQL statements that are syntactically correct but fail at runtime. Pages 144-145 (Chapter 6: Error Handling) of the book gives these examples on preempting a Stored Procedure directly (Example 6-18) or by SIGNAL emulation (Examples 6-19 and 6-20).

# Example 6-18. Using a nonexistent column name to force an error to the calling program

    CREATE PROCEDURE sp_update_employee_dob2
        (p_employee_id INT, p_dob DATE)
    BEGIN
        IF datediff(curdate(),p_dob)<(16*365) THEN
            UPDATE `Error: employee is too young; Employee must be 16 years or older`
                SET x=1;
        ELSE
            UPDATE employees
               SET date_of_birth=dob
            WHERE employee_id=p_dob;
       END IF;
    END;

# Example 6-19. Standard procedure to emulate SIGNAL

    CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))
    BEGIN
        SET @sql=CONCAT('UPDATE `',in_errortext,'` SET x=1');
        PREPARE my_signal_stmt FROM @sql;
        EXECUTE my_signal_stmt;
        DEALLOCATE PREPARE my_signal_stmt;
    END;

# Example 6-20. Using our SIGNAL emulation procedure to raise an error

    CREATE PROCEDURE sp_update_employee_dob2
        (p_employee_id INT, p_dob DATE)
    BEGIN
        IF datediff(curdate(),p_dob)<(16*365) THEN
            CALL my_signal('Error: employee is too young; Employee must be 16 years or older')
        ELSE
            UPDATE employees
               SET date_of_birth=dob
            WHERE employee_id=p_dob;
       END IF;
    END;

Unfortunately, the SIGNAL emulation from Examples 6-19 and 6-20 cannot be done in Triggers. The book's authors used the technique in Example 6-18 to preempt triggers on pages 254-256. This is why the book's suggestion has to be the way to go.

Answer 2

Why not just remove INSERT rights?