title: mysql after insert trigger which updates another table's column
body: <p>i'm trying to write a trigger, I have following tables:
    BookingRequest:</p>
  
  <pre><code>  +-----------+---------+------+-----+---------+----------------+
      | 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    |                |
      +-----------+---------+------+-----+---------+----------------+
  </code></pre>
  
  <p>status table:</p>
  
  <pre><code>+------------+--------------------------------------------------+------+-----+---------+-------+
  | Field      | Type                                             | Null | Key | Default | Extra |
  +------------+--------------------------------------------------+------+-----+---------+-------+
  | idStatus   | int(11)                                          | NO   | PRI | NULL    |       |
  | nameStatus | enum('underConsideration','approved','rejected') | YES  |     | NULL    |       |
  +------------+--------------------------------------------------+------+-----+---------+-------+
  </code></pre>
  
  <p>OccupiedRoom:</p>
  
  <pre><code>+--------------+---------+------+-----+---------+----------------+
  | Field        | Type    | Null | Key | Default | Extra          |
  +--------------+---------+------+-----+---------+----------------+
  | idOccupation | int(11) | NO   | PRI | NULL    | auto_increment |
  | idRoom       | int(11) | NO   |     | NULL    |                |
  | idRequest    | int(11) | NO   |     | NULL    |                |
  +--------------+---------+------+-----+---------+----------------+
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>and it doesn't work, so any suggestions would be very appriciated</p>