php - 创建 MySQL 触发器时出错

Question

我正在尝试创建此触发器，但我得到了

[Err] 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 '; CREATE TRIGGER ca_passwd_trigger BEFORE UPDATE ON users FOR EACH ROW BEGIN ' at line 1

delimiter $$
DROP TRIGGER IF EXISTS ca_passwd_trigger ;
$$
CREATE TRIGGER ca_passwd_trigger BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    IF ((NEW.passwd <=> OLD.passwd) = 0) THEN
        SET NEW.passwd_modified_at = NOW();
    END IF;
END;$$
delimiter ;

Answer 1

在您的查询中，您在两个地方添加了查询终止符 ; 和定界符 $$。以下查询具有正确的查询终止符和分隔符。

DELIMITER $$
DROP TRIGGER IF EXISTS ca_passwd_trigger; -- removed the delimiter $$ after the terminator ;
CREATE TRIGGER ca_passwd_trigger BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    IF ((NEW.passwd <=> OLD.passwd) = 0) THEN
        SET NEW.passwd_modified_at = NOW();
    END IF;
END$$  -- removed the terminator ; before the delimiter $$
DELIMITER ;