title: mysql update column based on event
body: <p>I have a database table that contain data of different farming pratices during several years on several fields. Farmers entered there information based on the dates when a certain activity was done. A simplified example would look like this, where ID is an auto-increment value.</p>
  
  <pre><code>ID|Field|Date      |Activity  |
   1|A    |2012/08/01|Tillage   |
   2|A    |2012/08/24|Seeding   |
   3|A    |2013/03/05|Spraying  |
   4|A    |2013/03/05|Fertilizer|
   5|A    |2013/07/25|Harvest   |
   6|B    |2012/09/01|Tillage   |
   7|B    |2012/09/05|Seeding   |
   8|B    |2013/04/05|Spraying  |
   9|B    |2013/07/28|Harvest   |
  10|B    |2010/08/24|Tillage   |
  11|B    |2010/09/29|Seeding   |
  12|B    |2011/05/01|Fertilizer|
  13|B    |2011/07/12|Harvest   |
  14|A    |2011/09/01|Seeding   |
  15|A    |2011/10/10|Spraying  |
  16|A    |2012/04/10|Fertilizer|
  17|A    |2012/08/02|Harvest   |
  </code></pre>
  
  <p>Now I would like to add a column called "Season" that contains only the year when a field was harvested but is automatically updated for all other activites between the last harvest and this harvest for individual fields. Note that harvest dates are different for different fields. In this example the output would ideally look like:</p>
  
  <pre><code>ID|Field|Date      |Activity  |Season
   1|A    |2012/08/01|Tillage   |2013
   2|A    |2012/08/24|Seeding   |2013
   3|A    |2013/03/05|Spraying  |2013
   4|A    |2013/03/05|Fertilizer|2013
   5|A    |2013/07/25|Harvest   |2013
   6|B    |2012/09/01|Tillage   |2013
   7|B    |2012/09/05|Seeding   |2013
   8|B    |2013/04/05|Spraying  |2013
   9|B    |2013/07/28|Harvest   |2013
  10|B    |2010/08/24|Tillage   |2011
  11|B    |2010/09/29|Seeding   |2011
  12|B    |2011/05/01|Fertilizer|2011
  13|B    |2011/07/12|Harvest   |2011
  14|A    |2011/09/01|Seeding   |2012
  15|A    |2011/10/10|Spraying  |2012
  16|A    |2012/04/10|Fertilizer|2012
  17|A    |2012/08/02|Harvest   |2012
  </code></pre>
  
  <p>How would I do that for the present data? Are triggers a good application for future data entry?</p>