MySQL server 5.1 trigger problem

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

• Akina: The error message explains what is wrong. This restriction cannot be bypassed. So the way you select to solve your task is wrong. Publish/explain this task, maybe it can be solved without using a trigger.
• mustaccio: An excellent example of an X-Y problem.
• pksv: thank you @Akina but its is in my project to use a trigger and a saved procedure hence i need to use the trigger or the saved procedure my project is about flights database reservation and cancellation i've created the database and the tables are login, registration, flights and booking i have already inserted the values to the database all i'm left to do is add a trigger and a short procedure so if you can help please guide me i need help before 4th december
• pksv: thank you @mustaccio..... but i dont know much about what you are telling if you can explain it will be very helpful i am very keen on learning about such stuffs
• mustaccio: Explain the logic that you need to implement, using human language. You seem to be using inappropriate tools for the job, but we don't know what your job is.

Answer 1

The error occurs because your trigger tries to DELETE from the BOOKING table, which is already locked by the UPDATE statement. MySQL/MariaDB doesn't allow modifying the same table in a trigger.

A potential solution would be to use a BEFORE UPDATE trigger to prevent NULL values or raise an error:

DELIMITER //
CREATE TRIGGER CANCEL_BOOK BEFORE UPDATE ON BOOKING
FOR EACH ROW
BEGIN
 IF NEW.F_ID IS NULL AND NEW.H_ID IS NULL THEN
 SIGNAL SQLSTATE '45000'
 SET MESSAGE_TEXT = 'Cannot update row with both F_ID and H_ID as NULL';
 END IF;
END //
DELIMITER ;

Alternatively, handle the cleanup outside the trigger with a separate DELETE statement:

DELETE FROM BOOKING WHERE F_ID IS NULL AND H_ID IS NULL;

Could you clarify your goal (e.g., delete the updated row or others)? This will help refine the solution.

Answer 2

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 ;

• pksv: thank you for you time and help but I want to remove that row if it is null I don't want it to show any error message. I mean that once those 2 values is null I no longer need that record in my database I want it to be deleted
• Akina: I want to remove that row WHAT ROW? This trigger do not allow to create the record which have both field with NULL values - so there is no rows to remove.
• pksv: this trigger won't allow me to set h_id and f_id as null but my question is that let's say if a person has booked both flight seat and hotel room so all the values in that row will be filled now suppose the person doesn't want to go so he starts cancelling the flight ticket and the room so once he does that the values for h_id and f_id will become null so I want a trigger that will delete such rows as soon as they become null
• Michael - sqlbot: @Akina note also that MySQL Server 5.1 does not support SIGNAL. This was not introduced until 5.5.
• Akina: @pksv I want a trigger that will delete such rows as soon as they become null Updated.