将CONCAT与MySQL触发器配合使用（错误，） [英] Using CONCAT with MySQL Triggers (Error at ,)

问题描述

我正试图创建一个触发器来编辑x表中的行时写日志。

I am trying to create a trigger to write logs when rows in x table have been edited.

这是当前查询。

CREATE TRIGGER users_update_trigger
AFTER UPDATE ON users FOR EACH ROW
BEGIN
    INSERT INTO users_backlog
    (user_id, description, datetime) VALUES (user_id,
    CONCAT('modified from ', OLD.value, ' to ', NEW.value), CURTIMESTAMP());
END

控制台返回以下错误：

您的SQL语法有错误；检查手册
对应于您的MySQL服务器版本，以获取正确的语法以在第6行的''附近使用

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 6

有人可以告诉我我在做什么错吗？

Can anyone tell me what I'm doing wrong here?

编辑：
相关表的架构
用户：

Schema for relevant tables Users:

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `hourly` decimal(10,2) DEFAULT NULL,
  `date_paid` date DEFAULT NULL
;

Users_backlog：

Users_backlog:

CREATE TABLE IF NOT EXISTS `users_backlog` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `description` varchar(50) NOT NULL,
  `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

推荐答案

已更新：

1. 您似乎并没有更改 DELIMITER 。


2. 您最有可能eant CURRENT_TIMESTAMP 而不是不存在的 CURTIMESTAMP（）

1. It looks like you didn't changed DELIMITER.
2. You most likely meant CURRENT_TIMESTAMP instead of nonexistent CURTIMESTAMP()

这就是说，触发器的语法正确版本可能是

That being said a syntactically correct version of your trigger might look like

DELIMITER $$
CREATE TRIGGER users_update_trigger
AFTER UPDATE ON users 
FOR EACH ROW
BEGIN
  INSERT INTO users_backlog (user_id, description, datetime) VALUES 
  (NEW.user_id, CONCAT('modified from ', OLD.hourly, ' to ', NEW.hourly), CURRENT_TIMESTAMP);
END$$
DELIMITER ;

或（因为触发器中只有一个语句，所以可以省略 BEGIN ... END 块和 DELIMITER ）只是

or (because you have the only one statement in your trigger you can omit BEGIN ... END block and DELIMITER) simply

CREATE TRIGGER users_update_trigger
AFTER UPDATE ON users 
FOR EACH ROW
  INSERT INTO users_backlog (user_id, description, datetime) VALUES 
  (NEW.user_id, CONCAT('modified from ', OLD.hourly, ' to ', NEW.hourly), NOW());

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于将CONCAT与MySQL触发器配合使用（错误，）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！