Using CONCAT with MySQL Triggers (Error at ,)

Question

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

This is the current query..

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

The console returns the following error:

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?

Edit: 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:

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

1st, Where is OLD.value and NEW.value came from? I can see that you haven't declare those. 2nd is the CURTIMESTAMP() will return a TIMESTAMP not a DATETIME. — Mark
– Mark, Commented Dec 5, 2013 at 0:11
@ChristianMark Ah I havent declared this, the 'value' column are both decimal(10, 2) — InvalidSyntax
– InvalidSyntax, Commented Dec 5, 2013 at 0:15
So what is the actual table name payments or users? auto_increment user_id column for payments table doesn't make much sense — peterm
– peterm, Commented Dec 5, 2013 at 0:27

peterm · Accepted Answer · 2013-12-05 00:40:32Z

4

UPDATED:

It looks like you didn't changed DELIMITER.
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 ;

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());

Here is SQLFiddle demo

edited Dec 5, 2013 at 0:40

answered Dec 5, 2013 at 0:24

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rsanchez · Accepted Answer · 2013-12-05 00:23:47Z

1

You didn't set the DELIMITER to something different than ; before running your definition.

answered Dec 5, 2013 at 0:23

rsanchez

14.7k1 gold badge40 silver badges54 bronze badges

Collectives™ on Stack Overflow

Using CONCAT with MySQL Triggers (Error at ,)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related