创建 MySQL 触发器时出错 [英] Error creating MySQL Trigger

问题描述

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

I'm trying to create this trigger but i'm getting

[Err] 1064 - 您的 SQL 语法有错误；检查手册对应于您的 MySQL 服务器版本的正确语法使用附近 ';在用户更新之前创建触发器 ca_passwd_triggerFOR EACH ROW BEGIN ' 在第 1 行

[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 ;

推荐答案

在您的查询中，您在两个位置添加了查询终止符 ; 和分隔符 $$.以下查询具有正确的查询终止符和分隔符.

In your query you added the query terminator ; with the delimiter $$ in two places. The below query is having proper query terminators and delimiters.

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 ;

这篇关于创建 MySQL 触发器时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！