CODEWITHSUNDEEP
mysqlsqltriggersconcatenation
InvalidSyntax
InvalidSyntax

Reputation: 9507

Using CONCAT with MySQL Triggers (Error at ,)

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

Upvotes: 1

Views: 2203

Answers (2)

peterm
peterm

Reputation: 92805

UPDATED:

  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 ;

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

Upvotes: 4

rsanchez
rsanchez

Reputation: 14657

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

Upvotes: 1

Related Questions