mysql 触发器错误

Question

我正在尝试创建一个触发器。首先，我创建了表Employees和表employees_audit：

CREATE TABLE employees (
  employeeNumber int(11) NOT NULL,
  lastName varchar(50) NOT NULL,
  firstName varchar(50) NOT NULL,
  extension varchar(10) NOT NULL,
  email varchar(100) NOT NULL,
  officeCode varchar(10) NOT NULL,
  reportsTo int(11) default NULL,
  jobTitle varchar(50) NOT NULL,
  PRIMARY KEY  (employeeNumber)
)

 CREATE TABLE employees_audit ( 
 id int(11) NOT NULL AUTO_INCREMENT, 
 employeeNumber int(11) NOT NULL, 
 lastname varchar(50) NOT NULL, 
 changedon datetime DEFAULT NULL, 
 action varchar(50) DEFAULT NULL, 
 PRIMARY KEY (id) 
 ) 

然后我创建了触发器：

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

第一个表和第二个表都被创建，但是当我执行触发器时 我收到错误

#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 'DELIMITER $$
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employe' at line 1 

我的代码中是否有任何错误或者我需要另一个 MySQL 版本吗？

Answer 1

您是从 phpMyAdmin 运行的吗？如果是这样，分隔符应设置在 SQL 文本区域下方的字段中，而不是文本区域本身。

没有。与已删除的答案相反，在 DELIMITER $$ 之后不需要 ; 因为这会将分隔符设置为 $$;

Answer 2

好吧，我知道你做错了什么。参见下面的执行：

mysql>  CREATE TABLE employees_audit (
->  id int(11) NOT NULL AUTO_INCREMENT,
->  employeeNumber int(11) NOT NULL,
->  lastname varchar(50) NOT NULL,
->  changedon datetime DEFAULT NULL,
->  action varchar(50) DEFAULT NULL,
->  PRIMARY KEY (id)
->  )
->
-> DELIMITER $
->  CREATE TRIGGER before_employee_update
->  BEFORE UPDATE ON employees
->  FOR EACH ROW BEGIN
->  INSERT INTO employees_audit
->  SET action = 'update',
->  employeeNumber = OLD.employeeNumber,
->  lastname = OLD.lastname,
->  changedon = NOW(); END$

错误 1064 (42000)：您的 SQL 语法有错误；检查手册
对应于您的 MySQL 服务器版本，以便在 'DELIM 附近使用正确的语法
国际热核聚变实验堆$$

敲响了警钟？看起来很相似？

兄弟，您（很可能）忘记在 create table 指令的末尾添加 ; 。下面的代码有效：

    mysql>
    mysql>  CREATE TABLE employees_audit (
        ->  id int(11) NOT NULL AUTO_INCREMENT,
        ->  employeeNumber int(11) NOT NULL,
        ->  lastname varchar(50) NOT NULL,
        ->  changedon datetime DEFAULT NULL,
        ->  action varchar(50) DEFAULT NULL,
        ->  PRIMARY KEY (id)
        ->  );
    Query OK, 0 rows affected (0.09 sec)

    mysql>
    mysql> DELIMITER $
    mysql>  CREATE TRIGGER before_employee_update
        ->  BEFORE UPDATE ON employees
        ->  FOR EACH ROW BEGIN
        ->  INSERT INTO employees_audit
        ->  SET action = 'update',
        ->  employeeNumber = OLD.employeeNumber,
        ->  lastname = OLD.lastname,
        ->  changedon = NOW(); END$
    Query OK, 0 rows affected (0.01 sec)

    mysql>  DELIMITER ;

---

旧答案

尝试这个

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

missing ; after END，我猜，

编辑：修复格式

Edit1：重新答复。