How to Correctly Update a Table Column in MySQL Using an After Insert Trigger?

MySQL After Insert Trigger to Update Another Table's Column

This query attempts to create a trigger in MySQL that updates a column in the BookingRequest table when a row is inserted into the OccupiedRoom table. However, the provided trigger does not work as intended.

To fix this issue, here is a corrected version of the trigger:

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 ; **Trigger Details:** * The trigger is defined using the `AFTER INSERT` clause, which means it executes after a row is inserted into the `OccupiedRoom` table. * The trigger uses a `FOR EACH ROW` clause to process each inserted row individually. * Inside the trigger body: * It declares a Boolean variable `@id_exists` to check if the `idRequest` from the inserted `OccupiedRoom` row exists in the `BookingRequest` table. * It checks the `BookingRequest` table to determine whether the `idRequest` of the newly inserted `OccupiedRoom` row exists. If it does, it sets `@id_exists` to `1`. * If `@id_exists` is `1`, it updates the `status` column in the `BookingRequest` table to '1' where the `idRequest` matches the new `idRequest` from the `OccupiedRoom` table.

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