title: MySQL Trigger to update a table based on changes on another table
body: <p>I've been working for this seemingly simple task for hours with no luck.</p>
  
  <p>I have a database, <strong>test</strong>, which contains two tables: <strong>room</strong> and <strong>reservation</strong>.</p>
  
  <p>In table <strong>room</strong> there is a column named <em>reserved_status</em> with possible values of 0 and 1. <em>room_id</em> is its primary key.</p>
  
  <p>In table <strong>reservation</strong>, there is the <em>room_id</em> column, which refers to the room_id from table <strong>room</strong></p>
  
  <p>Basically, I want to create a trigger, that updates <em>reserved_status</em> to 1, whenever there is a change in table <strong>reservation</strong>(Like insert, update or delete).</p>
  
  <p>The following query is actually accepted in phpMyAdmin:</p>
  
  <pre><code>DELIMITER $$
  CREATE TRIGGER reserved_status_insert AFTER
  INSERT ON
   reservation FOR EACH ROW
  BEGIN
  UPDATE
   room
  SET
   room_reserved = 1
  WHERE
   room_id = NEW.reservation.room_id ;
  END $$
  DELIMITER ;
  </code></pre>
  
  <p>However, when I try to do a insert operation, it gives me this error:</p>
  
  <pre><code>Unknown column 'NEW.reservation.room_id' in 'where clause'
  </code></pre>
  
  <p>I hope someone can provide me a suitable solution, thank you...</p>