title: MYSQL Triggers New and OLD
body: <p>How can I get the New/Old values from a table when using a trigger? I tried putting the previous population into another table and got an syntax error. I have edited the table and put the city/country schema so others can view and try to assist me.</p>
  
  <pre><code>DELIMITER $$
  
  USE `world`$$
  
  DROP TRIGGER /*!50032 IF EXISTS */ `previous_pop`$$
  
  CREATE
      /*!50017 DEFINER = 'root'@'localhost' */
      TRIGGER `previous_pop` AFTER UPDATE ON city
      FOR country
      BEGIN
      previous.city_previous_pop = country.Old.Population;
  
  
      END;
  $$
  
  DELIMITER ;   
  
  Error Code: 1064
  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'country
      Begin
  </code></pre>
  
  <p>country schema;</p>
  
  <pre><code>TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME ORDINAL_POSITION    COLUMN_DEFAULT  IS_NULLABLE DATA_TYPE   CHARACTER_MAXIMUM_LENGTH    CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION   NUMERIC_SCALE   DATETIME_PRECISION  CHARACTER_SET_NAME  COLLATION_NAME  COLUMN_TYPE COLUMN_KEY  EXTRA   PRIVILEGES  COLUMN_COMMENT
  def sakila  country country_id  1   \N  NO  smallint    \N  \N  5   0   \N  \N  \N  smallint(5) unsigned    PRI auto_increment  select,insert,update,references 
  def sakila  country country 2   \N  NO  varchar 50  150 \N  \N  \N  utf8    utf8_general_ci varchar(50)         select,insert,update,references 
  def sakila  country last_update 3   CURRENT_TIMESTAMP   NO  timestamp   \N  \N  \N  \N  0   \N  \N  timestamp       on update CURRENT_TIMESTAMP select,insert,update,references 
  def world   country Code    1       NO  char    3   3   \N  \N  \N  latin1  latin1_swedish_ci   char(3) PRI     select,insert,update,references 
  def world   country Name    2       NO  char    52  52  \N  \N  \N  latin1  latin1_swedish_ci   char(52)            select,insert,update,references 
  def world   country Continent   3   Asia    NO  enum    13  13  \N  \N  \N  latin1  latin1_swedish_ci   enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America')           select,insert,update,references 
  def world   country Region  4       NO  char    26  26  \N  \N  \N  latin1  latin1_swedish_ci   char(26)            select,insert,update,references 
  def world   country SurfaceArea 5   0.00    NO  float   \N  \N  10  2   \N  \N  \N  float(10,2)         select,insert,update,references 
  def world   country IndepYear   6   \N  YES smallint    \N  \N  5   0   \N  \N  \N  smallint(6)         select,insert,update,references 
  def world   country Population  7   0   NO  int \N  \N  10  0   \N  \N  \N  int(11)         select,insert,update,references 
  def world   country LifeExpectancy  8   \N  YES float   \N  \N  3   1   \N  \N  \N  float(3,1)          select,insert,update,references 
  def world   country GNP 9   \N  YES float   \N  \N  10  2   \N  \N  \N  float(10,2)         select,insert,update,references 
  def world   country GNPOld  10  \N  YES float   \N  \N  10  2   \N  \N  \N  float(10,2)         select,insert,update,references 
  def world   country LocalName   11      NO  char    45  45  \N  \N  \N  latin1  latin1_swedish_ci   char(45)            select,insert,update,references 
  def world   country GovernmentForm  12      NO  char    45  45  \N  \N  \N  latin1  latin1_swedish_ci   char(45)            select,insert,update,references 
  def world   country HeadOfState 13  \N  YES char    60  60  \N  \N  \N  latin1  latin1_swedish_ci   char(60)            select,insert,update,references 
  def world   country Capital 14  \N  YES int \N  \N  10  0   \N  \N  \N  int(11)         select,insert,update,references 
  def world   country Code2   15      NO  char    2   2   \N  \N  \N  latin1  latin1_swedish_ci   char(2)         select,insert,update,references 
  </code></pre>
  
  <p>city schema:</p>
  
  <pre><code>TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME ORDINAL_POSITION    COLUMN_DEFAULT  IS_NULLABLE DATA_TYPE   CHARACTER_MAXIMUM_LENGTH    CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION   NUMERIC_SCALE   DATETIME_PRECISION  CHARACTER_SET_NAME  COLLATION_NAME  COLUMN_TYPE COLUMN_KEY  EXTRA   PRIVILEGES  COLUMN_COMMENT
  def sakila  city    city_id 1   \N  NO  smallint    \N  \N  5   0   \N  \N  \N  smallint(5) unsigned    PRI auto_increment  select,insert,update,references 
  def sakila  city    city    2   \N  NO  varchar 50  150 \N  \N  \N  utf8    utf8_general_ci varchar(50)         select,insert,update,references 
  def sakila  city    country_id  3   \N  NO  smallint    \N  \N  5   0   \N  \N  \N  smallint(5) unsigned    MUL     select,insert,update,references 
  def sakila  city    last_update 4   CURRENT_TIMESTAMP   NO  timestamp   \N  \N  \N  \N  0   \N  \N  timestamp       on update CURRENT_TIMESTAMP select,insert,update,references 
  def world   city    ID  1   \N  NO  int \N  \N  10  0   \N  \N  \N  int(11) PRI auto_increment  select,insert,update,references 
  def world   city    Name    2       NO  char    35  35  \N  \N  \N  latin1  latin1_swedish_ci   char(35)            select,insert,update,references 
  def world   city    CountryCode 3       NO  char    3   3   \N  \N  \N  latin1  latin1_swedish_ci   char(3)         select,insert,update,references 
  def world   city    District    4       NO  char    20  20  \N  \N  \N  latin1  latin1_swedish_ci   char(20)            select,insert,update,references 
  def world   city    Population  5   0   NO  int \N  \N  10  0   \N  \N  \N  int(11)         select,insert,update,references 
  </code></pre>
  
  <p>After creating the trigger I ran this: </p>
  
  <pre><code>UPDATE city
  SET Population = 1
  WHERE CountryCode = 'AFG';
  
  SELECT Population, CountryCode FROM city;
  
  Population  CountryCode
  1   AFG
  1   AFG
  1   AFG
  1   AFG
  </code></pre>
  
  <p>And the country population did not change:</p>
  
  <pre><code>SELECT Population FROM country WHERE CODE = 'AFG';
  
  Population
  20387904
  </code></pre>