mysql after insert trigger which updates another table's column
With your requirements you don't need BEGIN END and IF with unnecessary SELECT in your trigger. So you can simplify it to this
CREATE TRIGGER occupy_trig AFTER INSERT ON occupiedroom FOR EACH ROW UPDATE BookingRequest SET status = 1 WHERE idRequest = NEW.idRequest;
Try this:
DELIMITER $$CREATE TRIGGER occupy_trigAFTER INSERT ON `OccupiedRoom` FOR EACH ROWbegin 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 ;
Maybe remove the semi-colon after set because now the where statement doesn't belong to the update statement. Also the idRequest could be a problem, better write BookingRequest.idRequest