MySQL server 5.1 trigger problem

Posted on

Question :

I have implemented a trigger. But once I try to update the table it is giving me the error:

can’t update table ‘booking’ in stored function/trigger because it is already used by the statement which invoked this stored function/trigger

This is the trigger

DELIMITER //
CREATE TRIGGER CANCEL_BOOK AFTER UPDATE on BOOKING
FOR EACH ROW
BEGIN
DELETE FROM BOOKING
    WHERE NEW.F_ID=NULL AND NEW.H_ID=NULL;
END //
DELIMITER ;

please help

Answer :

Prevent record creation with F_ID and H_ID having NULL values (needs 5.5 or newer server version):

DELIMITER @@;

CREATE TRIGGER cancel_insert_book BEFORE INSERT ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        SIGNAL SQLSTATE '23000'
        SET MESSAGE_TEXT = 'Both F_ID and H_ID cannot be NULL at the same time', 
        MYSQL_ERRNO = 1048;
    END IF;
END; 
@@;

CREATE TRIGGER cancel_update_book BEFORE UPDATE ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        SIGNAL SQLSTATE '23000'
        SET MESSAGE_TEXT = 'Both F_ID and H_ID cannot be NULL at the same time', 
        MYSQL_ERRNO = 1048;
    END IF;
END; 
@@;

DELIMITER ;

Remove record which have F_ID and H_ID having NULL values:

DELIMITER @@;

CREATE TRIGGER cancel_book AFTER UPDATE ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        CREATE EVENT remove_record
        ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
        DO
          DELETE FROM booking
          WHERE ((f_id IS NULL) AND (h_id IS NULL));
    END IF;
END; 
@@;

DELIMITER ;

Leave a Reply

Your email address will not be published.