MYSQL 触发器新旧

Question

使用触发器时如何从表中获取新/旧值？我尝试将以前的人口放入另一个表中，但出现语法错误。我已经编辑了表格并放置了城市/国家架构，以便其他人可以查看并尝试帮助我。

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

国家架构；

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 

城市架构:

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 

创建触发器后，我运行了以下命令:

UPDATE city
SET Population = 1
WHERE CountryCode = 'AFG';

SELECT Population, CountryCode FROM city;

Population  CountryCode
1   AFG
1   AFG
1   AFG
1   AFG

国家人口没有变化:

SELECT Population FROM country WHERE CODE = 'AFG';

Population
20387904

Answer 1

当城市人口有更新时，这是更新国家/地区的触发器。假设首尔市人口从1000万更新为1100万；因此该国 KOR 人口也应增加 100 万。触发器上的问题是“FOR Country”的语法，它应该是“FOR EACH ROW”。也就是说，对于城市表中更新的每一行，对国家/地区表进行更新。老城区人口与新城区人口的差额将计入乡村人口。如果城市减少人口，那么乡村人口也会减少。

CREATE TRIGGER `previous_pop` AFTER UPDATE ON city
    FOR EACH ROW
    BEGIN

      UPDATE country
      SET    Population = Population + (NEW.Population - OLD.Population)
      WHERE  Code = OLD.CountryCode;

    END;