mySQL 触发器更新计数

Question

我正在尝试在 mySQL 中设置触发器，以便在添加、删除或更新新团队时触发 countTeams 中的更新，并将基本计数和 countNA 等更改为团队的相应国籍。

这是我的表格:

CREATE TABLE countTeams(
    base integer PRIMARY KEY,
    countNA integer,
    countEU integer,
    countCN integer,
    countSEA integer,
    countINT integer,
    countCIS integer
    );
CREATE TABLE Team(
    TeamName varchar (30) PRIMARY KEY,
    Captain varchar (30),
    Nationality varchar (5) NOT NULL,
    CreationDate date,
    Description varchar (50),
    foreign key(Captain) references Emails(Username)
    );

我失败的触发尝试。

CREATE TRIGGER teamsDown
AFTER INSERT ON Team FOR EACH ROW
UPDATE countTeams
BEGIN
SET base = (base - 1)
IF Team.Nationality= "NA" THEN
    UPDATE countTeams
    SET countNA = (countNA - 1)
ELSEIF Team.Nationality = "EU" THEN
    UPDATE countTeams
    SET countEU = (countEU - 1)
IF Team.Nationality = "CN" THEN
    UPDATE countTeams
    SET countCN = (countCN - 1)
IF Team.Nationality = "SEA"
    UPDATE countTeams
    SET countSEA = (countSEA - 1)
IF Team.Nationality = "INT"
    UPDATE countTeams
    SET countINT = (countINT - 1)
IF Team.Nationality = "CIS"
    UPDATE countTeams
    SET countCIS = (countCIS - 1)
END;

Answer 1

首先，更改您的 countTeams 表。

CREATE TABLE countTeams(
    base int auto_increment PRIMARY KEY,
    nationality varchar(5),
    how_many int DEFAULT 0
);

然后用需要的数据填充它。

INSERT INTO countTeams (nationality) VALUES
('NA'),
('EU'),
('CN'),
('SEA'),
('INT'),
('CIS');

现在您必须为插入创建一个触发器，为删除创建一个触发器。更新不会改变任何东西，不确定为什么要为此创建触发器。或者团队的国籍可以在之后改变吗？

DELIMITER $$
CREATE TRIGGER inc_countTeams AFTER INSERT ON Team
FOR EACH ROW
BEGIN
  UPDATE countTeams SET how_many = how_many + 1 WHERE nationality = NEW.nationality;
END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER dec_countTeams AFTER DELETE ON Team
FOR EACH ROW
BEGIN
  UPDATE countTeams SET how_many = how_many - 1 WHERE nationality = OLD.nationality;
END $$
DELIMITER ;

编辑:

DELIMITER $$
CREATE TRIGGER dec_countTeams AFTER UPDATE ON Team
FOR EACH ROW
BEGIN
  UPDATE countTeams SET how_many = how_many - 1 WHERE nationality = OLD.nationality;
  UPDATE countTeams SET how_many = how_many + 1 WHERE nationality = NEW.nationality;
END $$
DELIMITER ;