mysql - 创建 Sql 触发器查询语法问题

Question

我创建了一个触发器，如果​​我手动创建它，它的工作，当我导出并在另一个数据库中运行查询时，在 If 条件附近显示语法错误。谁能帮忙，以下查询中的问题是什么

CREATE TRIGGER advance_audit 
AFTER UPDATE ON employee 
FOR EACH ROW 
BEGIN
IF NEW.firstname <> OLD.firstname THEN 
    INSERT INTO employees_audit (action, column_name, old_name, new_name, operator)
    VALUES ('update', 'firstname', OLD.firstname, NEW.firstname, SYSTEM_USER());
END IF;
IF NEW.lastname <> OLD.lastname THEN 
    INSERT INTO employees_audit (action, column_name, old_name, new_name, operator)
    VALUES ('update', 'lastname', OLD.lastname, NEW.lastname, SYSTEM_USER());
END IF;

END

标签： mysqltriggers

Answer 1

将触发器转换为单语句形式：

CREATE TRIGGER advance_audit 
AFTER UPDATE ON employee 
FOR EACH ROW 
INSERT INTO employees_audit (action, column_name, old_name, new_name, operator)
    SELECT 'update', 'firstname', NEW.firstname, OLD.firstname, SYSTEM_USER() 
    WHERE NEW.firstname <> OLD.firstname
  UNION ALL
    SELECT 'update', 'lastname', NEW.lastname, OLD.lastname, SYSTEM_USER() 
    WHERE NEW.lastname <> OLD.lastname;