mysql after insert trigger which updates another table's column

Question

i'm trying to write a trigger, I have following tables: BookingRequest:

  +-----------+---------+------+-----+---------+----------------+     | Field     | Type    | Null | Key | Default | Extra          |     +-----------+---------+------+-----+---------+----------------+     | idRequest | int(11) | NO   | PRI | NULL    | auto_increment |     | roomClass | int(11) | NO   |     | NULL    |                |     | inDate    | date    | NO   |     | NULL    |                |     | outDate   | date    | NO   |     | NULL    |                |     | numOfBeds | int(11) | NO   |     | NULL    |                |     | status    | int(11) | NO   | MUL | NULL    |                |     | idUser    | int(11) | NO   | MUL | NULL    |                |     +-----------+---------+------+-----+---------+----------------+ 

status table:

+------------+--------------------------------------------------+------+-----+---------+-------+ | Field      | Type                                             | Null | Key | Default | Extra | +------------+--------------------------------------------------+------+-----+---------+-------+ | idStatus   | int(11)                                          | NO   | PRI | NULL    |       | | nameStatus | enum('underConsideration','approved','rejected') | YES  |     | NULL    |       | +------------+--------------------------------------------------+------+-----+---------+-------+ 

OccupiedRoom:

+--------------+---------+------+-----+---------+----------------+ | Field        | Type    | Null | Key | Default | Extra          | +--------------+---------+------+-----+---------+----------------+ | idOccupation | int(11) | NO   | PRI | NULL    | auto_increment | | idRoom       | int(11) | NO   |     | NULL    |                | | idRequest    | int(11) | NO   |     | NULL    |                | +--------------+---------+------+-----+---------+----------------+ 

i need a trigger which will change status in BookingReques to 1 if request with the same id is inserted into OccupiedRoom table, so i tried something like this

create trigger occupy_trig after insert on OccupiedRoom  for each row begin   if BookingRequest.idRequest= NEW.idRequest    then        update BookingRequest        set status = '1';        where idRequest = NEW.idRequest;      end if; END; 

and it doesn't work, so any suggestions would be very appriciated

Answer 1

Try this:

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 ; 

Answer 2

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;