title: How to update a row value in one table using the rencently inserted value in another table in MySQL
body: <p>Ok, first of all I'm pretty new to MySQL. I'm currently designing a demo database in which i have multiple tables already. What I want to do is that I have these two tables, one named loans, and the other revenue. in the loan table columns are ( transaction, customer-id, video-id, loan-date, return-date, total-loan, period(this is a date value with just month and year))</p>
  <p>On the revenue table I have ( period(same as loan), no_of_transactions, total_revenue) The period column in this table is UNIQUE constrained. So I was thinking of adding a trigger, every time there's a new INSERT into the transaction table, the total_revenue column in the revenue table should update. I designed this code:</p>
  <pre><code>CREATE TRIGGER UpdateRevenue
  AFTER INSERT ON loan
  FOR EACH ROW
  UPDATE revenue
  SET total_revenue = total_revenue + loan.total-loan
  WHERE period = NEW.period; 
  </code></pre>
  <p>But I think is not working because I'm not referring to the new insert. Any guidance on this would be good. Thanks.</p>