Take the 2-minute tour ×
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.

Please help me for few question in SQL..

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.

share|improve this question
    
you can refer this link for your query.. roseindia.net/sql/trigger/mysql-trigger-after-update.shtmlenter link description here Thanks. –  Chandresh Apr 28 '11 at 9:19
    
In order to prevent insertion into the reservation table, what is needed is a "BEFORE INSERT" trigger and not "AFTER UPDATE" because the trigger needs to be preempted in the event the numflight value you are attempting to INSERT INTO the reservation table DOES NOT EXIST in the flights table. This is why I used the example provided by the 'MySQL Stored Procedures Programming' book. –  RolandoMySQLDBA Apr 29 '11 at 17:25

2 Answers 2

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.

share|improve this answer

Why not just remove INSERT rights?

share|improve this answer

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.