mysql - 错误 1054 (42s22) : unknown column 'action' in 'field list'

Question

首先，为什么更新此触发器有效？

CREATE TABLE employees_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employeeNumber INT NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    changedat DATETIME DEFAULT NULL,
    action VARCHAR(50) DEFAULT NULL
);

CREATE TRIGGER before_employee_update 
    BEFORE UPDATE ON employees
    FOR EACH ROW 
 INSERT INTO employees_audit
 SET action = 'update',
     employeeNumber = OLD.employeeNumber,
     lastname = OLD.lastname,
     changedat = NOW();

更新此触发器会导致错误。我通过简单的更新来更新表，并在两个代码上使用 where 子句设置代码。

CREATE TABLE Salespeople_audit(
  EmpID INT AUTO_INCREMENT PRIMARY KEY,
  FName Varchar(25) NOT NULL,
  LName Varchar(25) NOT NULL
);

CREATE TRIGGER before_Salepeople_update 
    BEFORE UPDATE ON Salespeople 
    FOR EACH ROW 
 INSERT INTO Salespeople_audit
 SET action = 'update',
     EmpID = OLD.EmpID,
     FName = OLD.FName;

Answer 1

此创建表中没有配置操作字段:

CREATE TABLE Salespeople_audit(
    EmpID INT AUTO_INCREMENT PRIMARY KEY,
    FName Varchar(25) NOT NULL,
    LName Varchar(25) NOT NULL
);