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 ;