mysql trigger, text between OLD and NEW

Question

Hello I have a problem with mysql trigger:

DELIMITER $$
CREATE TRIGGER update_forum_admin 
    after UPDATE ON sb_admins
    FOR EACH ROW BEGIN

    INSERT INTO `mysql_db`.`trigger_log` (`dato`, `trigger`, `status`) VALUES (NOW(), 'update_forum_admin', OLD.user+"="+NEW.user);
END$$
DELIMITER ;

The problem is about:

OLD.user+"="+NEW.user

The result of this is just 0, but I wanna have it to show the Old user name plus "=" plus the new user name.

But what statement do I need for that?

Answer 1

You're using + as a string-concatenation operator, but this is non-standard syntax used by Microsoft not by MySQL nor in the ANSI SQL standard.

MySQL uses a function CONCAT(). For example:

INSERT INTO `mysql_db`.`trigger_log` (`dato`, `trigger`, `status`) 
  VALUES (NOW(), 'update_forum_admin', CONCAT(OLD.user, '=', NEW.user));

ANSI SQL (and most other SQL databases) uses the double-pipe as a string concatentation operator:

INSERT INTO `mysql_db`.`trigger_log` (`dato`, `trigger`, `status`) 
  VALUES (NOW(), 'update_forum_admin', OLD.user || '=' || NEW.user);

But don't try this in MySQL unless you set the SQL mode to ANSI or PIPES_AS_CONCAT. By default, || in MySQL is a synonym for a logical OR.

PS: You should use single-quotes for string literals.

Answer 2

You want to concatenate those values into a string:

CONCAT(OLD.user, "=", NEW.user)