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

User · Answer

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

User · Answer

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

User · Answer

DELIMITER     CREATE TRIGGER contacts after insert AFTER INSERT    ON contacts FOR EACH ROW  BEGIN     DECLARE vUser varchar 50       -- Find username of person performing the INSERT into table    SELECT USER   INTO vUser      -- Insert record into audit table    INSERT INTO contacts audit      contact id       deleted date       deleted by     VALUES      NEW contact id       SYSDATE         vUser     END      DELIMITER

User · Answer

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

[mysql] mysql after insert trigger which updates another table's column

Examples related to mysql

Examples related to sql-update

Examples related to triggers