How to Create a MySQL Trigger to Update a Different Table\'s Column After an Insert?

MySQL Trigger to Update Different Table's Column after Insert

This question involves creating a MySQL trigger that updates the status column in the BookingRequest table based on the insertion of a row into the OccupiedRoom table.

The initial trigger code attempted to directly update the BookingRequest table using a comparison between BookingRequest.idRequest and NEW.idRequest. However, this approach was unsuccessful.

To resolve this issue and create a functional trigger, the code below should be used:

DELIMITER $$
CREATE TRIGGER occupy_trig
AFTER INSERT ON `OccupiedRoom` FOR EACH ROW
begin
       DECLARE id_exists Boolean;
       -- Check BookingRequest table
       SELECT 1
       INTO @id_exists
       FROM BookingRequest
       WHERE BookingRequest.idRequest= NEW.idRequest;

       IF @id_exists = 1
       THEN
           UPDATE BookingRequest
           SET status = '1'
           WHERE idRequest = NEW.idRequest;
        END IF;
END;
$$
DELIMITER ;

This trigger utilizes a variable @id_exists to check if the idRequest from the OccupiedRoom table exists in the BookingRequest table. If it does, it sets the status column in the BookingRequest table to '1'.

By using the placeholder NEW within the trigger, the comparison is now performed correctly between the inserted row's idRequest and the corresponding idRequest in the BookingRequest table.

The above is the detailed content of How to Create a MySQL Trigger to Update a Different Table\'s Column After an Insert?. For more information, please follow other related articles on the PHP Chinese website!