mysql - 使用两个 IF block 触发

Question

您好，我的触发器有问题，当我单独使用所有东西时，它可以工作，但一起使用时不行。如何修复语法错误？

CREATE TRIGGER `C_edit` AFTER UPDATE ON `client`
 FOR EACH ROW 
 IF OLD.name != NEW.name THEN
INSERT INTO `change`(`id_user`, `table`, `column`, `id`, `status`, `before`, `now`) VALUES (OLD.id_user_edit, 'client', 'name', OLD.id_client, '4', OLD.name, NEW.name);
END IF;
IF OLD.name_sql != NEW.name_sql THEN
INSERT INTO `change`(`id_user`, `table`, `column`, `id`, `status`, `before`, `now`) VALUES (OLD.id_user_edit, 'client', 'name_sql', OLD.id_client, '4', OLD.name_sql, NEW.name_sql);
END IF;
error: #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 '' at line 4 

Answer 1

由于您在触发器主体中使用多个语句，因此需要使用 MySQL 的 begin ... end compound statement syntax ，以及用于更改分隔符的 delimiter 语句:

delimiter //
CREATE TRIGGER `C_edit` AFTER UPDATE ON `client`
 FOR EACH ROW
    BEGIN 
        IF OLD.name != NEW.name THEN
            INSERT INTO `change`(`id_user`, `table`, `column`, `id`, `status`, `before`, `now`) VALUES (OLD.id_user_edit, 'client', 'name', OLD.id_client, '4', OLD.name, NEW.name);
        END IF;
        IF OLD.name_sql != NEW.name_sql THEN
            INSERT INTO `change`(`id_user`, `table`, `column`, `id`, `status`, `before`, `now`) VALUES (OLD.id_user_edit, 'client', 'name_sql', OLD.id_client, '4', OLD.name_sql, NEW.name_sql);
        END IF;
    END//
delimiter ;